Linked Server SQL Server 2014 to SQL Server Version 8

Question

I have recently installed SQL Server 2014 Express and need to create a linked server. I have tried this in SQL Server Management Studio (from the object explorer - server objects - linked servers - add linked server).

When connecting to the server directly through Server type "SQL server" or by specifying the connection attributes in "Other data source" then Microsoft OLE DB Provider for SQL Server and filling in the additional details I receive the following error.

The linked server has been created but failed a connection test. SQL Server native client 11.0 does not support connections to SQL Server 2000 or earlier versions.

I need to be able to create a joined query between two databases on different servers, what is the best way of achieving this ? The database I need to connect to is Version 8 (SQL Server 2000), very old. I have read it might be possible to achieve through transact SQL but not sure what steps to take.

Answer 1

It is possible to create a linked server but it cannot be done through the GUI. As a workaround you can create a DSN to use in transact SQL to link the servers.

For full instructions visit http://sqlwithmanoj.com/2012/12/10/sql-server-2012-does-not-support-linked-server-to-sql-server-2000-workaround/

=> WORKAROUND / FIX:

Now as a workaround to make this Linked Server work we have an option to use the ODBC Data Source which will connect to our remote server. There are 2 approaches: 1. Either we create an ODBC Data Source (DSN) and use it in our Linked Server 2. Or, use the Data Source (DSN) connection string directly in the Linker Server Provider

–> Using appraoch #1:

Create an ODBC Data Source: – Open Control Panel, go to Administrative Tools, then “Data Sources (ODBC)”. – On “ODBC Data Source Administrator” window go to “System DSN” Tab. – Here click on Add to create a new DSN. – Choose “SQL Server” and click Finish. – On the new window, give a proper name for the Source DSN (like: NorthWind2000DSN), we will use this name while creating our Linked Server. Provide the Server name which is on SQL Server 2000, here “NorthWind”. Click Next. – Choose the Authentication Type, either Windows or SQL Server auth. Click Next. – Change the default database, not necessary. Click Next. – Click Finish. You will see a new DSN created under System DSN tab.

Now, create Linked Server and provide this DSN in the @datasrc param and provide the @provider param “MSDASQL”. You can use the below query to create the same:

USE master
GO
-- Drop Existing LinkedServer [NorthWind2000]:
EXEC sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO

-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC sp_addlinkedserver @server = N'NorthWind2000', 
                    @srvproduct=N'MSDASQL', 
                    @provider=N'MSDASQL', 
                    @datasrc = N'NorthWind2000DSN', 
                    @location=N'System';

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000', 
                      @useself=N'True', 
                      @locallogin=NULL, 
                      @rmtuser=NULL, 
                      @rmtpassword=NULL
GO

–> Using appraoch #2:

We can also directly put the DSN connection String in the Provider String @provstr param. Let’s check it below:

USE master
GO
-- Drop Existing LinkedServer [NorthWind2000]:
EXEC sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO
-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC sp_addlinkedserver @server = N'NorthWind2000', 
                    @srvproduct=N'', 
                    @provider=N'MSDASQL', 
                    @provstr=N'DRIVER={SQLServer};SERVER=NorthWind;Trusted_Connection=yes;'

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000', 
                      @useself=N'True', 
                      @locallogin=NULL, 
                      @rmtuser=NULL, 
                      @rmtpassword=NULL
GO