sql2005 and sql2000 connectivity error

Question

Post reply

sql2005 and sql2000 connectivity error

Ahamed Aslam

SSC Rookie

Points: 43
More actions
September 14, 2006 at 11:39 am

#170150

I tried to connect sqlserver 2000 sp4 installed on windows 2000 prof sp4 version from sql2005 windows 2003 64 bit cluster-aware version through linked server. i got the following error.
Microsoft OLE DB Provider for SQL Server error '80040e14'
Named Pipes Provider: Could not open a connection to SQL Server [1326].

After i search all the forums to solve it i could not solve the problems

I check the MSADCS.dll version on win2003 server where sql2005 installed it was 2.82.1830.0 while sql2000 sp4 version is 2.81.1117.0. i don't how to degrade or upgrade the MSADCS.dlls?
Pls any one help me. Thanx in advance

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Hans Lindgren SSChampion Points: 10496 More actions · Answer 1

As far as I know there should be no problems linking a 64Bit 2k5 with a 2k.

Have you checked if the named pipes protocol has been disabled on the 2k machine?

Jim Dillon-291542 Hall of Fame Points: 3344 More actions · Answer 2

There's a something that must be run on the 2000 SP4 instance in order to have the server as a linked server on the 2005 SQL instance.

"You must run the instcat.sql script on the SQL 2000 machine. This script is part of the SQL 2000 SP4 installation (even though it doesn't get executed as part of the installation)."

A.J.

DBA with an attitude

Also try this article: KB ref at http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

Good Luck!

jim

Ahamed Aslam SSC Rookie Points: 43 More actions · Answer 3

Thankx Hanslindgren and Jim for ur quick response.

b4 I run inst.sql i got the following error

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "<LinkedServerName>".

Also i disable named pipe protocol. no.

I can access sql2k5 data from sql2000 through linked server. but the reverse is the pbm generating this error: Named Pipes Provider: Could not open a connection to SQL Server [1326].

i stuck on this pbm almost a week. Any help would be appreciable

Hans Lindgren SSChampion Points: 10496 More actions · Answer 4

Thanks, I didn´t know about this potential pitfall when using SQL Server 2005 64bit and linked servers..

Hans Lindgren SSChampion Points: 10496 More actions · Answer 5

It would seem that the KB that Jim posted describes exactly this error message you now are seeing.

Did your problem get solved?

Ahamed Aslam SSC Rookie Points: 43 More actions · Answer 6

My problem solved after i remove the sql2005 express edition on win2k sqlsk m/c. Thanx for support in the future

Jeff Lofgren SSC Journeyman Points: 75 More actions · Answer 7

Though it's nearly a year since you posted this, I want to let everyone know that this fixed my (recent) problem perfectly. Thanks, Jim.

Ellen-477471 SSCrazy Points: 2238 More actions · Answer 8

We are having the same issue. I am wary to run it on my SQL Server 2000 server that has been in production use for over a year. We just now need to have a server link from a SQL Server 2005 server from a 64bit server so it is the first time we have run up against the error.

Do you know when it is safe to run the indicated script?

Jim Dillon-291542 Hall of Fame Points: 3344 More actions · Answer 9

From my experience you have to run instcat.sql script on the SQL 2000 machine if you want to create a linked server for it from a 64-bit SQL Server 2005. Of course this is after you have applied SQL Server 200 SP4.

I'd make sure that SP 4 would work in my environment before I got too far. I'd hope that you were already running SP4 on your SQL 2000 instances. If so, just wait for a little down time and run the script. It's been a year or so since I did this, but I don't remember it taking more than 15 minutes or so.

Good luck,

jim

Ellen-477471 SSCrazy Points: 2238 More actions · Answer 10

Jim,

We have been running with sp4 applied. Luckily I found a work around on another forum. I added the sp below to the master db on my 2k server and it works great. But now that I know it is safe to run the Instcat.sql script I may go ahead and do that on the weekend.

Thanks again,

Ellen

FYI in case you would like the wrapper another DBA posted:

**************** FROM THE FORUM **********************

When running 4 part reference query like this:

select * from sql2000.mybase.dbo.mytable

SQL Server 2005 x64 runs the following query on remote SQL2000 server:

exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL

Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:

create procedure sp_tables_info_rowset_64

@table_name sysname,

@table_schema sysname = null,

@table_type nvarchar(255) = null

as

declare @Result int set @Result = 0

exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

Raffaele Garofalo Newbie Points: 9 More actions · Answer 11

Ellen your solution was amazing, and really quickly.

Thanks!!

😀

Eric H Grasshopper Points: 19 More actions · Answer 12

I applied the script and it did exactly what it was supposed to do. I didn't experimented any other problems since I ran the script so I will recommend it. Good luck!

Lee Linares SSCrazy Points: 2727 More actions · Answer 13

Ellen,

Thank you, thank you, thank you!! This was driving me nuts. What forum did you find this on?

Lee

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 14

You might find

MS article id:906954 "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 or to a linked SQL Server 7.0 server"

http://support.microsoft.com/kb/906954

helpful.