Post reply

sql2005 and sql2000 connectivity error

  • 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

  • September 15, 2006 at 1:10 am

    #660598

    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?

  • September 15, 2006 at 1:38 pm

    #660814

    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

  • September 16, 2006 at 12:33 am

    #660871

    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

     

     

  • September 17, 2006 at 7:55 am

    #660916

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

  • September 17, 2006 at 7:58 am

    #660917

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

    Did your problem get solved?

  • September 17, 2006 at 8:07 am

    #660918

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

  • August 2, 2007 at 12:01 pm

    #724282

    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.

  • October 5, 2007 at 2:00 pm

    #739707

    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?

  • October 5, 2007 at 2:10 pm

    #739712

    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

  • October 5, 2007 at 3:30 pm

    #739743

    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

  • June 19, 2008 at 6:53 am

    #831056

    Ellen your solution was amazing, and really quickly.

    Thanks!!

    😀

  • July 3, 2008 at 3:47 pm

    #838159

    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!

  • January 8, 2009 at 11:06 am

    #923570

    Ellen,

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

    Lee

  • July 28, 2009 at 7:30 am

    #1031002

    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.

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

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