Migration of linked server

Question

Hiall,

I have migrated the llinked server(copied the script and executed on new server 2012) but the issue is when I try to retrive data this error has been thrown

OLE DB provider "SQLNCLI11" for linked server  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.

please let me know If there is any other possible way to transfer the linked servers.

Answer 1

Hello,

If you using the linked server to connect to a SQL Server 2000 instance, install SQL Server native Client that belongs to SQL Server 2008 and use it on the linked server.

Hope this helps.

Regards,
Alberto Morillo
SQLCoffee.com

Answer 2

hi alberto ,

where do you want me to install (sql server native client that belongs to 2008 ) on sql server 2012 or sql 2000 .

What I have done is'' scripted linked server from old server which has 2005 sql server on it  and used that script and installed the linked server on the new 2012 server but dont know why this error appeared when i tried to retrive data from a table.

the linked server is connecting a server which is 'sql 2000'

we are doing our migration and its last day please help me alberto.

Regards,

Answer 3

Hello,

Please just download and install “Microsoft SQL Server 2008 Native Client” from the following link:

http://www.microsoft.com/en-us/download/details.aspx?id=16177

Once you install it, open “Data Sources (ODBC)” (Administrative Tools) and create a DSN using Native Client 10.0. Finally used the second example I provided on my first post, and change “datasrc” with the name of the data source you just created.

For more information, please see the following thread:

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/2e02c603-e28d-49eb-b073-548c59732b5d/#b490f6ff-940e-45a2-93b6-c25860947c1e

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 4

hi alberto 

the download link is failed ,could you please give a link which is working I am searching for it in microsoft page aswell,

thanks for your reply reallu helpful.

regards,

keerthi.

Answer 5

Hello,

Download the SQL Server 2008 R2 Native Client that you will find on the following link:

http://www.microsoft.com/en-us/download/details.aspx?id=16978

This native client is compatible with SQL Server 2000.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 6

Hi alberto !

Do you want me to uninstall native client 11.0 which is installed  in with sql server 2012 ,because it will say that there is already a higher version of native client available  uninstall it .. 

Will that effect other linked servers I have installed from other servers.

Thanks for you help !!

Regards,

Keerthi C madapati

Answer 7

Hello,

Thank you for your kindness.

If you cannot have both keep the SQL Server 2008 R2 version of native client. You may have to recreate any other linked server only, but I am not sure.

The SQL Server 2008 R2 Native Client wil allow you to connect to SQL Server 2012 also.

Hope this helps.

Regards,
Alberto Morillo
SQLCoffee.com

Answer 8

Hi Alberto ,

I have checked it in both the ways it worked with MSDASQL provider but there was an issue while running a stored procedure.I could retrive data from a table like

select * from [maindb].[xyz].[dbo].x_Y_Z_detail -----this returns data

when ran a stored procedure

it returns'' null values''....

when tried it with SQLNCLI10 it returns message 

''The OLE DB provider "SQLNCLI10" has not been registered''

Please help!

Answer 9

Hi Alberto,

The error was 

OLE DB provider "SQLNCLI10" for linked server "mainDb" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "mainDb" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53]. 

this is the error i get when do it with native client 10.0

and its half way ok If I do with MSDASQL.

Regards,

Keerthi.

Answer 10

Hello,

Please download and install the Native Client for this version.

http://www.microsoft.com/en-us/download/details.aspx?id=27596  

Hope this helps.

Regards,
Alberto Morillo
SQLCoffee.com