Linked servers 2000/2005

Linked servers 2000/2005

• RayinSpain

  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

  
  

• Manish 3177

  I'd also like to say thank you for this wrapper. I was experiencing the same problem and it worked like a charm.

  
  

• JSensei

  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
  
  

• JBond007

  I'll try to apply SP4
  
  Thank you
  
  

• PeteJM01

  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
  
  

• mtfck

  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

  
  

• Nimrand

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

• RoxanaT

  You ROck! very nice fix, we were not very pleased with the idea of running service pack scripts against a production db, but THIS is a very nice work around.

  Kudos to you!

  
  

• Rob O

  I came across this KB article which explains the issue.
  
  http://support.microsoft.com/default.aspx scid=kb;en-us;906954
  
  Cheers,
  Priyanga
  

• Hibri Marzook

  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
  

• LisLis

  Just wanted to say thank you also. I used the wrapper and it worked perfect.

  It was a lot quicker to add than to run the Instcat.sql script indicated in KB906954.

  
  

• ChrisKinsman

  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.

  
  
  

• Arindam Sen

  No. We didn't find a solution.
  Using the x64 version of SQL Server was not a requirement so we used x86 version instead.
  
  
• Linked servers 2000/2005