SQL Server Performance

SQL-2000 linked server to SQL-2005

Discussion in 'SQL Server 2005 General DBA Questions' started by arka, Sep 22, 2009.

  1. arka New Member

    I'm having troubles creating a linked server. In SQL-2000 environment I need to create a linked server to SQL-2005 Named Instance. I’m able to see some system tables and views on the linked server, but that's all, no user objects. Does anybody know if this link between two different versions of SQL is going to work at all? And if it is, what's correct syntax (script) to create it?
    Here is a script I used to create the linked server:
    declare @ServerName varchar(50)
    Set @ServerName = 'SQL2005_alias_name' -- this alias points to 'IPSQL2005_Inst_name'
    exec sp_addlinkedserver @server=@ServerName, @srvproduct='', @provider='SQLOLEDB',@datasrc='IPSQL2005_Inst_name', @provstr='Integrated Security=SSPI;'
    -- Set options
    exec sp_serveroption @ServerName, 'collation compatible', 'true'
    exec sp_serveroption @ServerName, 'data access', 'true'
    exec sp_serveroption @ServerName, 'rpc', 'false'
    exec sp_serveroption @ServerName, 'rpc out', 'true'
    exec sp_serveroption @ServerName, 'use remote collation', 'false'
  2. arunyadav New Member

    Try adding @catalog='database_name'..
  3. arka New Member

    Thank you. I works now.

Share This Page