Problem with Linked Servers

I am trying to link a sql 2000 server to a sql 2005 Server. I setup the link to the sql 2000 box from the sql 2005 (x64, Enteprrise) box using SSMS. Both boxes have the same Windows domain account that I'm using for auithentication. (I also selected the Impersonate option in SSMS) When I run a simple query on the sql 2005 box referencing the sql 2000 box ( select * from sql2000Box.DBName.dbo.tblName) I get the error message:

OLE DB provider "SQLNCLI" for linked server "sql2000Box" returned message "Invalid authorization specification".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "sql2000Box" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "sql2000Box".

Help!.

TIA,

Barkingdog

I called Microsoft on this one. They recommended that I enable RPC OUT on the server trying to link to the linked server. When I did I got another error message that appears explicilty in this article:

"You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server"

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

I will see if this solution works.

Barkingdog

Problem with Install of MSSQL 2005 Standard x64 on two node cluster

Hi:
I'm trying to install MS SQL 2005 Standard x64 to a two node Windows Server
2003 R2 SP2 cluster.
I've setup the cluster as per MS requirements and tested failover, The
cluster seems to be working well.
When I go through the Install and select failover clustering for the
Database and Analysis services, all seems to go well until it gets to the
end, I get a failure on the second (passive) node.
On the passive node I find that there is a Task Scheduler job set, SQL
Server Remote Setup.job that is reporting this error:
"SQL Server Remote Setup .job" (setup.exe) 12/4/2007 12:40:14 PM ** ERROR
**
Unable to start task.
The specific error is:
0x80070005: Access is denied.
Try using the Task page Browse button to locate the application.
The only MS info, I've found is not to be in RDP on the second node. I've
tried that and even did an install from the console on the first node (No
Remote Desktop sessions at all). Same error.
I've made sure that the account used is part of the local administrators
group on both nodes and has the following rights:
Act as part of operating system
Logon as a batch job
Logon as a Service
I have been going around on this for several days and cannot come to a
solution.
Is it that the second node doesn't have access to the setup files? I tried
installing from the DVD on the primary node and copied the files to a
directory on the primary and shared it. Same error.
Thanks
Usually, the last thing you tried, moving the setup files, fixes this type
of issue.
Have you tried running the cluster install but only for the first node, then
adding the node via add/remove programs? I sometimes find mirroring the
regular cluster setup logic (build a single-node cluster and add on) helps.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Hons" <Paul.Hons@.unt.edu> wrote in message
news:4755520A.B695.00E7.0@.unt.edu...
> Hi:
> I'm trying to install MS SQL 2005 Standard x64 to a two node Windows
> Server
> 2003 R2 SP2 cluster.
> I've setup the cluster as per MS requirements and tested failover, The
> cluster seems to be working well.
> When I go through the Install and select failover clustering for the
> Database and Analysis services, all seems to go well until it gets to the
> end, I get a failure on the second (passive) node.
> On the passive node I find that there is a Task Scheduler job set, SQL
> Server Remote Setup.job that is reporting this error:
> "SQL Server Remote Setup .job" (setup.exe) 12/4/2007 12:40:14 PM ** ERROR
> **
> Unable to start task.
> The specific error is:
> 0x80070005: Access is denied.
> Try using the Task page Browse button to locate the application.
> The only MS info, I've found is not to be in RDP on the second node. I've
> tried that and even did an install from the console on the first node (No
> Remote Desktop sessions at all). Same error.
> I've made sure that the account used is part of the local administrators
> group on both nodes and has the following rights:
> Act as part of operating system
> Logon as a batch job
> Logon as a Service
> I have been going around on this for several days and cannot come to a
> solution.
> Is it that the second node doesn't have access to the setup files? I tried
> installing from the DVD on the primary node and copied the files to a
> directory on the primary and shared it. Same error.
> Thanks
>
>
|||Geoff N. Hiten wrote:
> Usually, the last thing you tried, moving the setup files, fixes this
> type of issue.
> Have you tried running the cluster install but only for the first
> node, then adding the node via add/remove programs? I sometimes find
> mirroring the regular cluster setup logic (build a single-node cluster
> and add on) helps.
>
One other thing to try - which I had to do to get around this issue.
Have you logged into both systems with the service account that you will
be using to run SQL Server? If not, make sure you log into both
machines with that account to create the user profile. For whatever
reason - without the user profile the task scheduler jobs cannot be started.
At least that is what worked for me.
BTW - before you restart your install make sure you uninstall the setup
files or you will get another error.
Jeff

Problem with FTS

I am trying to setup Full Text Search Service on my SQL 2000 Server Database
The problem I am having is that the "NEXT" button is not setting to enable when I select all IMAGE column to be populate
Why is it behaving like this ? am I missing something? I attach the Table structure & FTS Image link
Please help me
This is a sample image here


Why is theNEXT button disabled
http://80.42.24.114/you/callbackajax/1.gif
This is also my Table structure
http://80.42.24.114/you/callbackajax/2.gif

The text below is from the BOL(books online) about the extra column requirement to use Image column in Full Text index.
Run a search for Full Text image column in the BOL (bboks online) for more info. Hope this helps.
(Thus, when you set up full-text indexing on an image column in a table, you must create a separate column to hold information about the document. This type column can be of any character-based data type and contains the document file extension, such as doc for a Microsoft Word document. If the type column is NULL, the full-text service will assume the document is a text file.

In the Full-Text Indexing Wizard, if you select an image column for indexing, you must also specify a Binding column to hold the document type.

The sp_fulltext_column stored procedure also accepts an argument for the column to contain the document types.

The sp_help_fulltext_columns stored procedure also returns column name and column id of the document type column. )

Problem with enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But we can't find any element in the queue.when we used command

(select*fromsys.transmission_queue )

sys.transmission_queue, in column 'transmission_status' we are getting

'The session keys for this conversation could not be created or accessed. The database master key is required for this operation.'

For solving this we have used command

USE master ;

GO

ALTERDATABASE database name SET ENABLE_BROKER ;

but unable to solve it.

When we dequeue an element which already exist in the Queue we are able to dequeue it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

Add an WITH ENCRYPTION = OFF to your BEGIN DIALOG statements to avoid session keys altogether.

Alternatively you could ensure that the databaseaster key exists and is encrypted with service master key:

create master key encryption by password = 'MyPassword';

alter master key add ADD ENCRYPTION BY SERVICE MASTER KEY;

problem with distributed trans 2000 to 2005

I have a test environment setup consisiting of one SQL Server 2000 32 bit instance on one box and one SQL Server 2005 64 bit edtion on another box. Nearly everything works fine but have found this one snag...

Linked Server name: SQL2005

From the SQL Server 2000 instance in Query Analyzer...

-- this works fine
Delete from [SQL2005].myDB.dbo.myTbl

-- this just hangs
Begin Tran
Delete from [SQL2005].myDB.dbo.myTbl

It doesn't make any difference what provider I use on the Linked Server.

I have tried it on a SQL Server 2000 to SQL Server 2000 configuration and, interestingly, it would only work if I used set XACT_ABORT ON. However, in the SQL Server 2000 to 2005 configuration, this doesn't help.

We have a number of DTS packages that do deletes across the linked server that are failing due to this issue so we're being stopped from continuing with the migration to SQL Server 2005.

Any suggestions?

CilvePerhaps your DTC service is not running.

I have these 2 related links:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1
http://support.microsoft.com/kb/899191

By the way, if you intend to migrate for an old SQL2000 DB to a new SQL2005 DB, why not to backup the old one and restore it on your SQL2005 server?|||The DTC service is runnnig on both servers.

Actually, the problem isn't limited to Deletes within a distributed transaction... The problem is the distributed transaction itself. I repeated the test doing an insert across the servers. The insert works on its own but within a distributed transaction, it hangs.

I have restored the database onto the SQL Server 2005 box to migrate them. I was refering to application DTS packages not working on the 2000 server because of the distributed transaction problem.

Thanks,
Clive