Share via

How to create a Linked Server to SQL 2000 from SQL 2012 or later

  • Article

So SQL 2000 has been out of support for a while now.  But I know some of you still run it.  As you upgrade your environments you might find you are upgrading a SQL Server 2000, 2005, 2008 or 2008 R2 server that has linked server connections to a SQL 2000 server.  If so, it can be tricky, since the newer OleDB drivers in SQL Server no longer support connecting to SQL 2000.

Anyway, here's how to do it.  You can't use SQLNCLI11, and shouldn't use MSDASQL.  If you try to use MSDASQL SQL Server will silently use SQLNCLI11 instead, so that won't work either.  So first you need to install an older version of the SQL Native Client OleDB provider, SQLNCLI10.  It can be installed side-by-side with SQLNCLI11, and is still supported.

So first download the newest SQL Native Client OleDB provider that still supports connecting to SQL 2000, SQLNCLI10 from here:

Microsoft® SQL Server® 2008 R2 SP2 Feature Pack

 

Then set up the linked server like this, after adjusting the server name in sp_addlinkedserver and the login mapping in sp_addlinkedsrvlogin for your environment.

 
 EXEC sp_MSset_oledb_prop N'SQLNCLI10', N'AllowInProcess', 1
 GO
 EXEC sp_addlinkedserver @server = N'SQL2000', @srvproduct=N'', @provider=N'SQLNCLI10', @datasrc=N'MySql2000Server'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'collation compatible', @optvalue=N'false'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'data access', @optvalue=N'true'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'dist', @optvalue=N'false'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'pub', @optvalue=N'false'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'rpc', @optvalue=N'true'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'rpc out', @optvalue=N'true'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'sub', @optvalue=N'false'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'connect timeout', @optvalue=N'0'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'collation name', @optvalue=null
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'lazy schema validation', @optvalue=N'false'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'query timeout', @optvalue=N'0'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'use remote collation', @optvalue=N'true'
 GO
 EXEC sp_serveroption @server=N'SQL2000', @optname=N'remote proc transaction promotion', @optvalue=N'true'
 GO
 EXEC sp_addlinkedsrvlogin @rmtsrvname = N'SQL2000', @locallogin = NULL , @useself = N'False', @rmtuser = N'dw', @rmtpassword = N'P@ssword'
 GO

Then you can test with a query like

  select * from openquery (sql2000, 'select @@servername a' )

David

Comments

  • Anonymous
    September 14, 2015
    what is procedure for creating the datasource "MYSQL2000Server"?