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’