32-64 bit linked server problems

Had an interesting issue recently with a linked server going from a 64 bit SQL2008R2 host to a 32 bit  SQL2000 host.

The error message when running the distributed query was :-

OLE DB provider “SQLNCLI10” for linked server “sql02source” returned message “Unspecified error”.
OLE DB provider “SQLNCLI10” for linked server “sql02source” 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 2
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10” for linked server “sql02source”. The provider supports the interface, but returns a failure code when it is used.

According to Microsoft, this problem occurs because the system stored procedures are not upgraded as part of the service pack installation. You must manually upgrade the system stored procedures after you install SQL Server 2000 Service Pack (SP3) or SQL Server 2000 Service Pack 4 (SP4) by running instcat.sql.

Official MS response is here :-
http://support.microsoft.com/default.as … -us;906954

Peace to all,
Stevo

And here we go.

We all have dreams.  Some days I think I would like to be a writer. Others a fisherman.  Then a musician.  Cafe/Pizza shop owner, preferably in a quiet little town near some sort of water?  In reality I’m a DBA.

This has been a long time coming, and the simple truth is you have to start somewhere.  Here’s where I start writing, with the added benefit of being able to write about anything I choose.

To that end :-

“It was a dark and stormy night…”