servers 2000/2005

I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
The error message is :
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "Unspecified error".

OLE DB provider "SQLNCLI" for linked server "serv01" 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 "serv01". The provider supports the interface, but returns a failure code when it is used.
Thank you.

Is your SQL Server 2000 instance running SP4?

If not, in order for Distributed queries in SQL Server 2005 to work against SQL Server 2000, you need to run the instcat.sql script that is supplied as part of SP4 on your SQL Server 2000 instance.

Thanks,
- Balaji|||I'll try to apply SP4

Thank you|||I'll mark Balaji's answer as the correct one. If SP4 doesn't fix the issue, let us know. You or I can unmark the message at that time.

Thanks
Laurentiu|||Emil,

I am also getting the following error when i try to create a linked server from a 64bit SQL 2005 to a SQL Server 200o (SP4) instance. Did you find a solution to your problem?

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

Cheers,
Priyanga
|||I came across this KB article which explains the issue.

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

Cheers,
Priyanga|||No. We didn't find a solution.
Using the x64 version of SQL Server was not a requirement so we used x86 version instead.|||

Hi,

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

And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.

Regards,

Marek Adamczuk

|||I am having the same issue but we ARE already running sp4. Can I assume that instcat.sql was run? How do I tell?|||

I had the same problem, and found a workaround.

You'll probably find that you are able to create a linked server to the 2000 database. This can be referenced though an OPENQUERY statement:

CREATE view [dbo].[vw1_Sql_L_ElogiaSFProd_OrderEntry_AppletAttribute] as
Select * From OPENQuery(ELOGIA_IPG_SFOPROD, 'Select * from IPG_SFOPROD.dbo.applet_attributes')

where:
ELOGIA_IPG_SFOPROD is the name of the linked server, with its "Catalogue" pointed to the 2000 database name.
dbo is the object owner
applet_attributes is the table name

Cheers,

Mark

|||

Marek,

Thanks to your useful wrapper SP. This worked for me since it would be whole lot more pain to modify all selects to openquery methods. Instead this wrapper worked perfect.