Linking Servers 2000 / 2005 1

[LeonAtRC]

I've searched everything I can find but without a solution.
I have 2 SQL servers: one with SQL 2000 and one with SQL 2005. Both on the same LAN. The 2000 machine is runnjing Windows server 2000 and the 2005 machine is running Vista pro (64-bit)
The 2 servers are linked to each other and both report ok to the link being established.
When I run this:

SELECT People_ID FROM SQL2005.Customers.dbo.People

on the 2005 machine the query runs fine and returns the correct data.
BUT:
When I run this:

SELECT People_ID FROM SQL2000.Customers.dbo.People

on the 2000 machine I get the following error:

OLE DB provider "SQLNCLI" for linked server "sql2000" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "sql2000" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "sql2000". The provider supports the interface, but returns a failure code when it is used.

Can anyone provide a clue to what might be wrong?

Thank you. Leon

 

[mrdenny]

How did you setup the linked server from the SQL 2005 machine to the SQL 2000 machine?

What service pack is the SQL 2000 machine? The SQL 2005 machine?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LeonAtRC]

In tne Management Studio I selected "SQL Server" for Server type;
under Security I entered a user name/password; Under Server Options all are set to True. (Both machines are set the same way)

SQL 2000 is using SP4

SQL 2005 is version 9.00.3054.00 (No service pack)

 

[mrdenny]

Try deleting the linked server from the SQL 2005 server to the SQL 2000 server. Then recreate it by selecting the OLEDB drive for SQL Server instead of just selecting SQL Server.

The top two of the 5 boxes should be the remote servers name.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LeonAtRC]

Thanks Denny, but I still get the same error messages

 

[mrdenny]

You need to run the instcat.sql script that is supplied as part of SP4 on your SQL Server 2000 instance.


Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LeonAtRC]

It Works!!

Thanks Denny. That was one I never would have found!

Leon

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com