Linked server error 7202 could not find server in sysservers
Question
Thursday, August 16, 2018 8:41 PM
we are working to upgrade our old sql 2000 servers to 2008 initially then on to current. what I have is a sql 2000 server (server1) using a linked server to call another sql 2000 server (server2) and execute a stored procedure. I scripted off the linked server to server2 from server1 and replaced the servr2 name with my 2008 instance server3\instance1 name where I had to use the ip address not the name of the server due to other requirements (replication). So the new linked server is named something like '12.123.12.133\instance1'. the linked server was set up to use a specific sql login. I can see the linked server under server objects but when I click on catalogs to expand the databases, it gets an error "failed to retrieve data... dbnetlib connection open sql server does not exist or access denied". When I try to run the procedure using exec [12.123.12.133\instance1].databasename.dbo.storedprocname it returns a 7202 error could not find 12.123.12.133\instance1 in sysservers. a select from sysservers does return 12.123.12.133\instance1. I believe it has to do with the scripting used to create and not adding additional parms in the sp_addlinkedserver call, all it puts in is @server and @srvproduct. Appreciate any suggestions as to what needs to be done to resolve this.
All replies (5)
Friday, August 17, 2018 7:16 PM âś…Answered
Never heard of sp_setnetname. :-)
I always use what I showed above:
EXEC sp_addlinkedserver 'LINKEDSERVER', '', 'SQLNCLI', 'SERVER\INSTANCE'
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Thursday, August 16, 2018 9:33 PM
You obviously need to spell better. If it says that 1.2.3.4\instancename is not in sys.servers, this means that 1.2.3.4\instancename is not in sys.servers. So you need to double- and triple-check what you wrote.
Or do something like:
EXEC sp_addlinkedserver 'BETTERNAME', '', 'SQLNCLI', '1.2.3.4\instancename'
Now to the bad news. If you are aiming to upgrade to SQL 2017, you will find it difficult to connect to servers running SQL 2000. SQLNCLI11, which is the default on this version does not support connections to SQL 2000. You might be able to force SQLNCLI10 which you can get from SQL 2008, but I have a vague memory that this does not work very well.
What people usually end up doing is to use MSDASQL + ODBC, but Microsoft does not support this for distriuted queries. (The again, they don't support SQL 2000 either.)
Thursday, August 16, 2018 9:47 PM
Thanks for the reply, trying to get off these 2000 serves is what we are doing for sure. this test setup I have, I am using the 1.2.3.4\instancename as I needed to use the IP address to create the linked server for application use under a different security context than the linked server created by the replication set up for replication which was created with servername\instance. I do see I have the wrong name /number now in the IP address (thanks for that). When I try a right click script as drop to remove the linked server 1.2.3.4\instancename I get error 20583, cannot drop server it is used as a subscriber in replication. I suppose I can drop it all and start over but I would think I could remove the 1.2.3.4\instancename linked server, but it seems to now associate it with the servername\instance which was created for replication?
Friday, August 17, 2018 7:35 AM
I find that difficult to believe. I have not worked with linked servers for replication, but a linked server is really only an alias. That is, you can set up multiple linked servers for the same target. (Which can be practical if you need different server options, different login mapping etc.) So if you get this error message, I would guess it is because you have used this name as a subscriber.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Friday, August 17, 2018 2:28 PM
I completely removed the replication set up here and recreated it. This created the linked server on the publisher to the servername\instance name. I then created another linked server with the ip\instancename with the different login mapping. All seems to be working fine, when I try to delete the ip\instancename linked server, it fails with the 20583 error consistently even though it is not the linked server set up for replication but it is the server that is a subscriber from the server it was created on. I have found a few other posts that have the same situation such as here so it appears the resolution is to remove replication and then remove the linked server which is not much of a resolution to drop and recreate.
Do you use sp_setnetname to change the name of linked servers when you need multiple linked servers for the same target or another method? What I would like to do is use a more generic name for the linked server rather than the ip\instancename.