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 Enterprise Manager 2000

Hi,
After installing XP SP2 security patches on my box, I can not open
Enterprise Manager. It crashes when I click on icon to bring the analyzer up.
I get two error in event log,
========================
1. Event Type:Error
Event Source:Application Error
Event Category:None
Event ID:1000
Date:1/18/2005
Time:3:30:09 PM
User:N/A
Computer:WXPP-LT_AMINH
Description:
Faulting application mmc.exe, version 5.1.2600.0, faulting module mmc.exe,
version 5.1.2600.0, fault address 0x0000bb5b.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 41 70 70 6c 69 63 61 74 Applicat
0008: 69 6f 6e 20 46 61 69 6c ion Fail
0010: 75 72 65 20 20 6d 6d 63 ure mmc
0018: 2e 65 78 65 20 35 2e 31 .exe 5.1
0020: 2e 32 36 30 30 2e 30 20 .2600.0
0028: 69 6e 20 6d 6d 63 2e 65 in mmc.e
0030: 78 65 20 35 2e 31 2e 32 xe 5.1.2
0038: 36 30 30 2e 30 20 61 74 600.0 at
0040: 20 6f 66 66 73 65 74 20 offset
0048: 30 30 30 30 62 62 35 62 0000bb5b
0050: 0d 0a ..
2.
Event Type:Error
Event Source:Application Error
Event Category:None
Event ID:1001
Date:1/18/2005
Time:3:30:22 PM
User:N/A
Computer:WXPP-LT_AMINH
Description:
Fault bucket 02874011.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 42 75 63 6b 65 74 3a 20 Bucket:
0008: 30 32 38 37 34 30 31 31 02874011
0010: 0d 0a ..
=======================
I removed the patches using reollback option of XP, but after removal even
explorer crashes. I re-installed SQL client tools, but result is same...
Any one has idea how to resolve this?
Thanks,
Hiten
Maybe the snap-in has become corrupt or damaged.
Try renaming the existing SQL Server Enterprise Manager.MSC
file.
Then copy the SQL Server Enterprise Manager.MSC file from
the your SQL Server CD to the same location.
-Sue
On Tue, 18 Jan 2005 12:39:11 -0800, "Hiten"
<Hiten@.discussions.microsoft.com> wrote:

>Hi,
>After installing XP SP2 security patches on my box, I can not open
>Enterprise Manager. It crashes when I click on icon to bring the analyzer up.
>I get two error in event log,
>========================
>1. Event Type:Error
>Event Source:Application Error
>Event Category:None
>Event ID:1000
>Date:1/18/2005
>Time:3:30:09 PM
>User:N/A
>Computer:WXPP-LT_AMINH
>Description:
>Faulting application mmc.exe, version 5.1.2600.0, faulting module mmc.exe,
>version 5.1.2600.0, fault address 0x0000bb5b.
>For more information, see Help and Support Center at
>http://go.microsoft.com/fwlink/events.asp.
>Data:
>0000: 41 70 70 6c 69 63 61 74 Applicat
>0008: 69 6f 6e 20 46 61 69 6c ion Fail
>0010: 75 72 65 20 20 6d 6d 63 ure mmc
>0018: 2e 65 78 65 20 35 2e 31 .exe 5.1
>0020: 2e 32 36 30 30 2e 30 20 .2600.0
>0028: 69 6e 20 6d 6d 63 2e 65 in mmc.e
>0030: 78 65 20 35 2e 31 2e 32 xe 5.1.2
>0038: 36 30 30 2e 30 20 61 74 600.0 at
>0040: 20 6f 66 66 73 65 74 20 offset
>0048: 30 30 30 30 62 62 35 62 0000bb5b
>0050: 0d 0a ..
>2.
>Event Type:Error
>Event Source:Application Error
>Event Category:None
>Event ID:1001
>Date:1/18/2005
>Time:3:30:22 PM
>User:N/A
>Computer:WXPP-LT_AMINH
>Description:
>Fault bucket 02874011.
>For more information, see Help and Support Center at
>http://go.microsoft.com/fwlink/events.asp.
>Data:
>0000: 42 75 63 6b 65 74 3a 20 Bucket:
>0008: 30 32 38 37 34 30 31 31 02874011
>0010: 0d 0a ..
>=======================
>I removed the patches using reollback option of XP, but after removal even
>explorer crashes. I re-installed SQL client tools, but result is same...
>Any one has idea how to resolve this?
>Thanks,
>Hiten
|||Someone with the same problem before SP2 found this to be a workaround:
http://groups-beta.google.com/group/...e66a3e81c65f5e
[I opened] mmc.exe in the Windows/system32 folder and add[ed]
the Enterprise Manager snap-in. Now I can just run enterprise
manager that way! It's not ideal, but it works :-)
Steve Kass
Drew University
Hiten wrote:

>Hi,
>After installing XP SP2 security patches on my box, I can not open
>Enterprise Manager. It crashes when I click on icon to bring the analyzer up.
>I get two error in event log,
>========================
>1. Event Type:Error
>Event Source:Application Error
>Event Category:None
>Event ID:1000
>Date:1/18/2005
>Time:3:30:09 PM
>User:N/A
>Computer:WXPP-LT_AMINH
>Description:
>Faulting application mmc.exe, version 5.1.2600.0, faulting module mmc.exe,
>version 5.1.2600.0, fault address 0x0000bb5b.
>For more information, see Help and Support Center at
>http://go.microsoft.com/fwlink/events.asp.
>Data:
>0000: 41 70 70 6c 69 63 61 74 Applicat
>0008: 69 6f 6e 20 46 61 69 6c ion Fail
>0010: 75 72 65 20 20 6d 6d 63 ure mmc
>0018: 2e 65 78 65 20 35 2e 31 .exe 5.1
>0020: 2e 32 36 30 30 2e 30 20 .2600.0
>0028: 69 6e 20 6d 6d 63 2e 65 in mmc.e
>0030: 78 65 20 35 2e 31 2e 32 xe 5.1.2
>0038: 36 30 30 2e 30 20 61 74 600.0 at
>0040: 20 6f 66 66 73 65 74 20 offset
>0048: 30 30 30 30 62 62 35 62 0000bb5b
>0050: 0d 0a ..
>2.
>Event Type:Error
>Event Source:Application Error
>Event Category:None
>Event ID:1001
>Date:1/18/2005
>Time:3:30:22 PM
>User:N/A
>Computer:WXPP-LT_AMINH
>Description:
>Fault bucket 02874011.
>For more information, see Help and Support Center at
>http://go.microsoft.com/fwlink/events.asp.
>Data:
>0000: 42 75 63 6b 65 74 3a 20 Bucket:
>0008: 30 32 38 37 34 30 31 31 02874011
>0010: 0d 0a ..
>=======================
>I removed the patches using reollback option of XP, but after removal even
>explorer crashes. I re-installed SQL client tools, but result is same...
>Any one has idea how to resolve this?
>Thanks,
>Hiten
>

Problem with DTS schedule

Hi,

I have a little DTS problem here.
This package is programmed in an SQL Server v7.0 box and it executes OK when I run it manually.

The problem starts when I want to schedule the automatic execution of the DTS and in the Job History I got this error: "Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed."

That means that the user owner of the DTS must be a SysAdmin? The current role for the user is "Process Administrator" but not "System Administrator".

As I cannot give the user superpower in the server, do I have to recompile de DTS under other user that is System Administrator?

Regards,

GOne of the archived post refers
Expand Management, right-click SQL Server Agent, and then click Properties. Click the Job System tab.

Under Non-SysAdmin job step proxy account, clear the Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps check box, and click Reset Proxy Account.

Type the user name, password, and domain of the user account to be used by SQL Server Agent when running jobs owned by users who are not system administrators.

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