Linked Server from 2008R2 to Sql Server 7?
-
Hello Everyone,
I have done quite a bit of searching and have found several people asking the question but, I haven't been able to find a answer to the question.
Can you create a linked server from Sql Server 2008R2 to Sql Server 7, and if so how do you do this?
We have a legacy application that cannot be upgraded or migrated to a newer database platform and are getting ready to update one of the databases that currently links to the Sql 7 db from 2005 to 2008. I have been struglstrugglingother 2008 server to get the linked servers working with the Sql 7 Db.
If I cannot make the connection my thought was to use SSIS and populate some tables within the 2008 Database, the other problem that comes into play is the system is so old we don't know all the different pieces that are being accessed.
Any ideas on how to link these servers would be greatly appreciated.
Thanks,
Matt
-
I would like to help, but I have no SQL 7 environtment to run try the linked Server. What kind of error do you get when you try to create the linked Server? Could you add some screenshots?
IMHO I think that it should be possible, but as I already said I don't have an environment to test it.
-
The error I am getting is:
I am able to successfully create a linked server connection from the current 2005 env and that does work just fine. I am currently trying to figure out how to get Sql Server 2008 to use a DSN connection to maked the linked server and hopefully that will help. I have a DSN connection within my windows 7 env setup that can successfully make a test connection and SSIs is able to successfully pull data from the sql 7 DB so I don't believe there are any communication issues.
Thanks for any help you can give.
TITLE: Microsoft SQL Server Management Studio
------------------------------
The linked server has been created but failed a connection test. Do you want to keep the linked server?
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "CRM2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "CRM2" 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.". (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
------------------------------
BUTTONS:
&Yes
&No
------------------------------
-
mmady (10/12/2011)
The error I am getting is:I am able to successfully create a linked server connection from the current 2005 env and that does work just fine. I am currently trying to figure out how to get Sql Server 2008 to use a DSN connection to maked the linked server and hopefully that will help. I have a DSN connection within my windows 7 env setup that can successfully make a test connection and SSIs is able to successfully pull data from the sql 7 DB so I don't believe there are any communication issues.
Thanks for any help you can give.
TITLE: Microsoft SQL Server Management Studio
------------------------------
The linked server has been created but failed a connection test. Do you want to keep the linked server?
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "CRM2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "CRM2" 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.". (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
------------------------------
BUTTONS:
&Yes
&No
------------------------------
I think you can use the information provided in the links I mentioned on my last reply. Check this link http://www.sqlservercentral.com/Forums/Topic902721-391-1.aspx#bm903258 They mention in that link that you should not use SQLNCLI10, but SQLNCLI. You may find more info about it in this link http://blogs.msdn.com/b/spike/archive/2008/12/08/the-importance-of-selecting-the-correct-sql-provider-for-the-job-sqlncli-vs-sqlncli10.aspx. I hope it help you.
-
I deleted my Linked Server and created one using the following:
sp_addlinkedserver @server = N'CRMCENTRAL',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'crmcentral',
@catalog = N'siebeldb'
And I get the same error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
The test connection to the linked server failed.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.
OLE DB provider "SQLNCLI10" for linked server "CRMCENTRAL" returned message "Client unable to establish connection due to prelogin failure".
OLE DB provider "SQLNCLI10" for linked server "CRMCENTRAL" returned message "Client unable to establish connection". (Microsoft SQL Server, Error: 10054)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
-
mmady (10/12/2011)
I deleted my Linked Server and created one using the following:sp_addlinkedserver @server = N'CRMCENTRAL',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'crmcentral',
@catalog = N'siebeldb'
And I get the same error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
The test connection to the linked server failed.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.
OLE DB provider "SQLNCLI10" for linked server "CRMCENTRAL" returned message "Client unable to establish connection due to prelogin failure".
OLE DB provider "SQLNCLI10" for linked server "CRMCENTRAL" returned message "Client unable to establish connection". (Microsoft SQL Server, Error: 10054)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
In the error you sent me you are still using the SQLNCLI10 provider. See if you can use another provider. You may try this link http://www.microsoft.com/download/en/details.aspx?id=16177 for the providers.
-
I have exactly the same problem, I've got the same error messages
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply