servers Dialog Error

I freshly installed SQL Server 2005 Developers Edition (with SP1) on Windows XP (with SP2) but every time I right click on “Linked Servers” and choose “New Linked Server…” I get the following error:

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot find table 0. (System.Data)

BUTTONS:

OK

Details:

===================================

Cannot show requested dialog.

===================================

Cannot find table 0. (System.Data)

Program Location:

at System.Data.DataTableCollection.get_Item(Int32 index)

at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.PopulateProvidersCombo()

at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.Microsoft.SqlServer.Management.SqlMgmt.IPanelForm.OnInitialization()

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SetView(Int32 index, TreeNode node)

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SelectCurrentNode()

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.InitializeUI(ViewSwitcherTreeView treeView, ISqlControlCollection viewsHolder, Panel rightPane)

at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)

at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)

at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)

at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()

I tried reinstalling SQL Server 2005 and also tried Service Pack 2 (CTP) but I still get the same error.

PS I know I can use “sp_addlinkedserver” but I still need to know why the “Linked servers Dialog Box” is not showing up.

Here’s my @.@.VERSION

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Thank you,

We've recently released SQL Server 2005 Service Pack 2 (RTM). If you could install that, then let us know if you're still having this issue. I know we had worked on the Linked Server dialog in SP2, but I cannot recall the scope of the work.

SQL Server 2005 Service Pack 2:
http://www.microsoft.com/sql/sp2.mspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

It works now. I had a feeling it had something to do with “Host Integration Server 2006” so I uninstalled it, rebooted my PC and now “Linked Servers” dialog box in “SQL Server 2005 Developer Edition” works. I have no idea why it does not work with “Host Integration Server 2006” installed. Hopefully you guys have time to look into it and fix it. Also I’m very curious why this happens.

Thank you,

|||

I have this error too.

My @.@.version is

Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1) (i.e I have SP2)

I'm having trouble setting uo a linked server (SQL 2000) to my brand new SQL 2005. When I try to access the Linked Server (SQL2000) from the 2005 Server I get the following error message:

OLE DB provider "SQLNCLI" for linked server "zerver" returned message "Unspecified error".

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

I used sp_addlinkedserver och sp_addlinkedsrvlogin to set up the connection between the servers.

SQL 2000 version is Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Thank you,

Johan Nilsson

|||

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/en-us

SYMPTOMS
Consider the following scenario. You define a linked 32-bit Microsoft SQL Server 2000 server or a linked SQL Server 7.0 server by using the sp_addlinkedserver stored procedure. Then, you try to run distributed queries from a 64-bit SQL Server 2005 client to the linked server. In this scenario, you may experience one of the following symptoms: ? If the 32-bit SQL Server 2000 server has not been upgraded to SQL Server 2000 Service Pack 3 (SP3) or SQL Server 2000 Service Pack 4 (SP4), you receive the following error message:
The ODBC catalog stored procedures installed on server <LinkedServerName> are version <OldVersionNumber>; version <NewVersionNumber> or later is required to ensure proper operation. Please contact your system administrator.
? You receive an error message if the following conditions are true: ? SQL Server 2000 SP3 or SQL Server 2000 SP4 is installed on the 32-bit SQL Server 2000 server, or you use the linked SQL Server 7.0 server.
? The versions of the system stored procedures on the 32-bit SQL Server 2000 server or on the SQL Server 7.0 server are different from the service pack version that is installed on the server.
The error message is similar to the following:
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 "<LinkedServerName>". The provider supports the interface, but returns a failure code when it is used.
Note If you use the linked SQL Server 2000 server and the server has not been upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4, you must install SQL Server 2000 SP3 or SQL Server 2000 SP4 first.

sql

servers Dialog Error

I freshly installed SQL Server 2005 Developers Edition (with SP1) on Windows XP (with SP2) but every time I right click on “Linked Servers” and choose “New Linked Server…” I get the following error:

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot find table 0. (System.Data)

BUTTONS:

OK

Details:

===================================

Cannot show requested dialog.

===================================

Cannot find table 0. (System.Data)

Program Location:

at System.Data.DataTableCollection.get_Item(Int32 index)

at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.PopulateProvidersCombo()

at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.Microsoft.SqlServer.Management.SqlMgmt.IPanelForm.OnInitialization()

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SetView(Int32 index, TreeNode node)

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SelectCurrentNode()

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.InitializeUI(ViewSwitcherTreeView treeView, ISqlControlCollection viewsHolder, Panel rightPane)

at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)

at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)

at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)

at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()

I tried reinstalling SQL Server 2005 and also tried Service Pack 2 (CTP) but I still get the same error.

PS I know I can use “sp_addlinkedserver” but I still need to know why the “Linked servers Dialog Box” is not showing up.

Here’s my @.@.VERSION

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Thank you,

We've recently released SQL Server 2005 Service Pack 2 (RTM). If you could install that, then let us know if you're still having this issue. I know we had worked on the Linked Server dialog in SP2, but I cannot recall the scope of the work.

SQL Server 2005 Service Pack 2:
http://www.microsoft.com/sql/sp2.mspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

It works now. I had a feeling it had something to do with “Host Integration Server 2006” so I uninstalled it, rebooted my PC and now “Linked Servers” dialog box in “SQL Server 2005 Developer Edition” works. I have no idea why it does not work with “Host Integration Server 2006” installed. Hopefully you guys have time to look into it and fix it. Also I’m very curious why this happens.

Thank you,

|||

I have this error too.

My @.@.version is

Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1) (i.e I have SP2)

I'm having trouble setting uo a linked server (SQL 2000) to my brand new SQL 2005. When I try to access the Linked Server (SQL2000) from the 2005 Server I get the following error message:

OLE DB provider "SQLNCLI" for linked server "zerver" returned message "Unspecified error".

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

I used sp_addlinkedserver och sp_addlinkedsrvlogin to set up the connection between the servers.

SQL 2000 version is Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Thank you,

Johan Nilsson

|||

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/en-us

SYMPTOMS
Consider the following scenario. You define a linked 32-bit Microsoft SQL Server 2000 server or a linked SQL Server 7.0 server by using the sp_addlinkedserver stored procedure. Then, you try to run distributed queries from a 64-bit SQL Server 2005 client to the linked server. In this scenario, you may experience one of the following symptoms: ? If the 32-bit SQL Server 2000 server has not been upgraded to SQL Server 2000 Service Pack 3 (SP3) or SQL Server 2000 Service Pack 4 (SP4), you receive the following error message:
The ODBC catalog stored procedures installed on server <LinkedServerName> are version <OldVersionNumber>; version <NewVersionNumber> or later is required to ensure proper operation. Please contact your system administrator.
? You receive an error message if the following conditions are true: ? SQL Server 2000 SP3 or SQL Server 2000 SP4 is installed on the 32-bit SQL Server 2000 server, or you use the linked SQL Server 7.0 server.
? The versions of the system stored procedures on the 32-bit SQL Server 2000 server or on the SQL Server 7.0 server are different from the service pack version that is installed on the server.
The error message is similar to the following:
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 "<LinkedServerName>". The provider supports the interface, but returns a failure code when it is used.
Note If you use the linked SQL Server 2000 server and the server has not been upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4, you must install SQL Server 2000 SP3 or SQL Server 2000 SP4 first.

servers and MDAC 2.8

I am currently configuring and testing a new SQL server:
SQL 2000 Enterprise Edition SP3a
Windows 2003 Enterprise Edition
MDAC 2.8
I am experiencing some strange behaviour with linked SQL
servers from the new server.
This 1st SQL stmt below works OK from the new server
select contact_id from ls.lat_prod_ed.dbo.contact
This 2nd SQL stmt fails on the new server with general
network error after approx 25 seconds.
select * from ls.lat_prod_ed.dbo.contact
Server: Msg 11, Level 16, State 1, Line 5
General network error. Check your network documentation.
A SQL profiler trace shows the following error being
raised:
OLE/DB Provider 'Unknown' ::ReleaseSchemaLock returned
0x80040e96: OLE DB provider Unknown supported the Schema
Lock interface, but returned0x80040e96 for
ReleaseSchemaLock .
The target linked server (ls) is running SQL 2000 sp3a
Standard Edition on Windows 2000 sp3. It was on MDAC 2.7
and I've now upgraded it to MDAC 2.8 but get the same
error.
I've run component checker on both servers and it reports
everything OK.
The SQL statement that fails, works OK when run from
another SQL server (SQL 2000 Enterprise Edition sp3a,
Windows 2000 Advanced server sp3 and MDAC 2.7 sp1 refresh)
and pointing to the same target linked server (ls).
Thanks in advance
Greg DaviesDoes openquery work? Does this happen only for '*' or for other columns of
the table as well? How was this linked server created? Through enterprise
manager?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

servers and MDAC 2.8

I am currently configuring and testing a new SQL server:
SQL 2000 Enterprise Edition SP3a
Windows 2003 Enterprise Edition
MDAC 2.8
I am experiencing some strange behaviour with linked SQL
servers from the new server.
This 1st SQL stmt below works OK from the new server
select contact_id from ls.lat_prod_ed.dbo.contact
This 2nd SQL stmt fails on the new server with general
network error after approx 25 seconds.
select * from ls.lat_prod_ed.dbo.contact
Server: Msg 11, Level 16, State 1, Line 5
General network error. Check your network documentation.
A SQL profiler trace shows the following error being
raised:
OLE/DB Provider 'Unknown' ::ReleaseSchemaLock returned
0x80040e96: OLE DB provider Unknown supported the Schema
Lock interface, but returned0x80040e96 for
ReleaseSchemaLock .
The target linked server (ls) is running SQL 2000 sp3a
Standard Edition on Windows 2000 sp3. It was on MDAC 2.7
and I've now upgraded it to MDAC 2.8 but get the same
error.
I've run component checker on both servers and it reports
everything OK.
The SQL statement that fails, works OK when run from
another SQL server (SQL 2000 Enterprise Edition sp3a,
Windows 2000 Advanced server sp3 and MDAC 2.7 sp1 refresh)
and pointing to the same target linked server (ls).
Thanks in advance
Greg Davies
Does openquery work? Does this happen only for '*' or for other columns of
the table as well? How was this linked server created? Through enterprise
manager?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
sql

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.

servers

I have to link servers using sql server 2000 standard edition...
for anyone who has done this.... upon linking the servers using the other options radio button choice... is it suppose to show all the databases for that server under the drop down...
its only showing tables and views... which i get an error 7339 or error 17:

can anyone assist...or lead me in the right direction....
thanks,
jonathanhttp://support.microsoft.com/kb/814398
http://support.microsoft.com/default.aspx/kb/241267
If you google on error 7399 you would get relevant forum posts too.|||I resolved the issue|||How ?|||permissions on the standy server did not match or map to the prod box.....
under the security tab i changed the option being made using this security context to begin made using the login's current security context|||ahhhh...yes.

The ole' security issue...