SQL with Manoj

Yes, you read it right, “SQL Server 2012” has stopped connecting to “SQL Server 2000” via linked Servers. As this new version uses a new Native Client version i.e. SQLNCLI11, instead of the old SQLNCLI10. This new client only connects back to 2008R2, 2008 and 2005 only.

– I upgraded my Database Servers from “SQL Server 2008 R2” to “SQL Server 2012”.

– Restored the databases from the backup taken from 2008R2.

– Ran the jobs and found that one of them was failing with following error:
 

Error Message:
OLE DB provider “SQLNCLI11” for linked server “NorthWind2000” returned message “Client unable to establish connection”.
Msg 22, Level 16, State 1, Line 0
SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.
OLE DB provider “SQLNCLI11” for linked server “NorthWind2000” returned message “Invalid connection string attribute”.

 

As I have upgraded from 2008 R2, so I checked Providers under the Linked Server and found that with the new SQLNCLI11, I still have the “SQL Server Native Client 10.0” i.e. “SQLNCLI10”.

So, I tried to create the Linked Server by using “SQLNCLI10”, but it again gave an error, as follows:

Msg 8522, Level 16, State 3, Line 1
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

 

I scripted out the DDL of my existing Linked Server, as below:

USE [master]
GO

-- Existing LinkedServer [NorthWind2000]:
EXEC sp_addlinkedserver	@server = N'NorthWind2000', 
						@srvproduct=N'MSSQL', 
						@provider=N'SQLNCLI', 
						@provstr=N'PROVIDER=SQLOLEDB;SERVER=NorthWind'

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

=> 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:
1. Open Control Panel, go to Administrative Tools, then “Data Sources (ODBC)”.
2. On “ODBC Data Source Administrator” window go to “System DSN” Tab.
3. Here click on Add to create a new DSN.
4. Choose “SQL Server” and click Finish.
5. 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.
6. Choose the Authentication Type, either Windows or SQL Server auth. Click Next.
7. Change the default database, not necessary. Click Next.
8. 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={SQL Server};SERVER=NorthWind;Trusted_Connection=yes;'

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

This way you can query SQL Server 2000 data from SQL Server 2012 via Linked Servers by using ODBC DSN.

This seems seamless but it is an indirect process and a workaround to query SQL Server 2000 database.
To make your queries or ETLs efficient it is advisable to upgrade to a higher version, at-least SQL Server 2005.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.