MS-SQL 2012 LINKED SERVER TO MS-SQL 2000

Having trouble creating a linked server from MS-SQL 2012 to MS-SQL 2000?  Need to access data on your older SQL 2000 database system?  Here is a handy script to use as a template for creating the link.  But first go to the control panel and create a 64 bit data source called “MYDSN” using the older provider (SQL Client 10).  Then when that is completed, run the following database script on the MS-SQL 2012 machine to create the link.  But first edit the script as follows:

  • replace ‘MyLink’ with any name you want to use for your linked server.
  • replace ‘mylocaluser’ with the database user name on the SQL 2012 machine.
  • replace ‘myremoteuser’ with the database user name on the SQL 2000 machine.
  • replace ‘myremotepassword’ with the database password on the SQL 2000 machine.
  • Note:  the first line of the script deletes the link, which will fail if the link doesn’t exist yet.

EXEC master.dbo.sp_dropserver @server=N’MyLink’, @droplogins=‘droplogins’
EXEC master.dbo.sp_addlinkedserver @server = N’MyLink’,
    @srvproduct=N’MSDASQL’, @provider=N’MSDASQL’, @datasrc=N’MYDSN’, @location=N’System’
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N’MyLink’,
    @useself=N’True’, @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N’MyLink’,
    @useself=N’False’, @locallogin=N’mylocaluser’,
    @rmtuser=N’myremoteuser’, @rmtpassword=‘myremotepassword’
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N’MyLink’, @useself=N’False’,
    @locallogin=N’sa’, @rmtuser=N’myremoteuser’, @rmtpassword=‘myremotepassword’
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N’MyLink’, @useself=N’False’,
    @locallogin=N’NT Service\SQLSERVERAGENT’,
    @rmtuser=N’myremoteuser’, @rmtpassword=‘myremotepassword’
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’collation compatible’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MyLink’, @optname=N’data access’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’MyLink’, @optname=N’dist’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MyLink’, @optname=N’pub’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MyLink’, @optname=N’rpc’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MyLink’, @optname=N’rpc out’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’sub’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’connect timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’collation name’, @optvalue=null
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’lazy schema validation’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’query timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’use remote collation’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption
    @server=N’MyLink’, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
 

Leave a comment