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.

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.

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.

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.

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.

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.

server with SQLNCLI and a default database.

I have a linked server from one 2005 server (server A) to another 2005 server (server B). The linked server is created like this:

EXEC sp_addlinkedserver

@.server = N'TEST',

@.srvproduct=N'',

@.provider=N'SQLNCLI',

@.datasrc=N'twx-webdev',

@.catalog=N'Hornet'

This query works fine:

SELECT * FROM TEST.Hornet.dbo.NotificationTrigger

But, I need to be able to make a query from server A to server B without specifying the database name. I need the default database of the linked server to be used when making the following query:

SELECT * FROM TEST...NotificationTrigger

This query does not work. The query returns the following error:

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "TEST".

I also used the following syntax when creating the linked server hoping the Initial Catalog in the provider string would work.

EXEC sp_addlinkedserver

@.server = N'TEST',

@.srvproduct=N'',

@.provider=N'SQLNCLI',

@.datasrc=N'twx-webdev',

@.provstr=N'Initial Catalog=Hornet'

The query against this linked server returned this error:

Msg 7314, Level 16, State 1, Line 2

The OLE DB provider "SQLNCLI" for linked server "TEST" does not contain the table ""dbo"."NotificationTrigger"". The table either does not exist or the current user does not have permissions on that table.

Also, this query produces the same results:

SELECT * FROM TEST..dbo.NotificationTrigger

How can I force the linked server to actually use the default database and not have to specify the database in queries? The 2005 documentation for linked servers alludes to this being possible.

Any help you can provide is very appreciated.

Thank you,

Chris

http://msdn2.microsoft.com/en-us/ms188718.aspx

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

http://sqlserver2000.databases.aspfaq.com/how-do-i-prevent-linked-server-errors.html - a good one as a whole to resolve the LS errors.

|||

The following quote is from your first link. Is this telling me that I cannot default the database in a query even if I have the default catalog set on the linked server?

"When you use four-part names, always specify the schema name. Not specifying a schema name in a distributed query prevents OLE DB from finding tables. When referencing local tables, SQL Server uses defaults if an owner name is not specified. The following SELECT statement would generate a 7314 error, even if the linked server login mapped to a dbo user in the AdventureWorks database on the linked server:"

Thanks,
Chris