3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Emma
  • 577
  • 1
  • 13
  • 27
  • 1
    As the error **clearly says**: SQL Server 2000 (v8) is **no longer supported** - you **cannot** restore a backup from SQL Server 2000, and you cannot create a linked server to a SQL Server 2000 instance anymore. – marc_s Jul 23 '15 at 09:08
  • The error comes from the client, not the server. Install an older SQL Server client on your machine and use that to connect. You can have multiple SQL Server client versions installed at the same time. I expect 2008 or 2008 R2 will support 2000. – Panagiotis Kanavos Jul 23 '15 at 09:13
  • Or restore the database to a newer version that still supports 2000, then backup and restore it to 2014 – Panagiotis Kanavos Jul 23 '15 at 09:15
  • 1
    Hi, I have managed to link the table using a work around here http://sqlwithmanoj.com/2012/12/10/sql-server-2012-does-not-support-linked-server-to-sql-server-2000-workaround/ - How now to get the data from this link into a view of the other database ? – Emma Jul 23 '15 at 09:19

1 Answers1

4

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
Emma
  • 577
  • 1
  • 13
  • 27