Linked SQL Server 2014 with SQL Server version 8
I recently installed SQL Server 2014 Express and need to create a linked server. I tried this in SQL Server Management Studio (from object explorer - server objects - linked servers - add linked server
).
When connecting to the server directly through the server type "SQL Server" or by specifying the connection attributes in "Other Data Source", Microsoft OLE DB Provider for SQL Server and filling in additional data, I get the following error.
The linked server was created, but the connection test failed. SQL Server Client 11.0 does not support connections to SQL Server 2000 or earlier.
I need to be able to create a combined query between two databases on different servers, what is the best way to achieve this? The database I have to connect to is version 8 (SQL Server 2000), very old. I have read that it could be achieved via transactional SQL but am not sure what steps to take.
source to share
It is possible to create a linked server, but it cannot be done through a graphical interface. As a workaround, you can create a DSN to use in an SQL transaction to connect 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 for this Linked Server to work, we can use an ODBC datasource that will connect to our remote server. There are two approaches: 1. Either we create an ODBC data source (DSN) and use it on our Linked Server 2. Or use the data source connection string (DSN) directly in the Linker Server provider
→ Using grade # 1:
Create an ODBC data source: - Open Control Panel, open "Administrative Tools", then "Data Sources (ODBC)". - In the ODBC Data Source Administrator window, click the System DSN tab. - Click "Add" here to create a new DSN. - Select "SQL Server" and click "Finish". - In the new window, give the correct name for the original DSN (eg: NorthWind2000DSN), we will use this name when creating our Linked Server. Specify the server name that is on SQL Server 2000, here "NorthWind". Click Next. - Select the type of authentication, either Windows Authorization or SQL Server. Click Next. - Change the default database, optional. Click "Next. - Click" Finish. "You will see a new DSN,created in the System DSN tab.
Now create a Linked Server and specify this DSN in the @datasrc parameter and specify the @provider parameter "MSDASQL". You can use the following 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 grade # 2:
We can also directly bind the DSN string to the string in the provider's @provstr param string. Let's check it out 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
source to share