Linked Server Error 17

[MsChelle]

I have two servers. One is replacing the other, eventually. I have *almost* identical databases on both. Because they are not completely identical, I have to manually copy some of the table data from the old to the new.

I am able to successfully setup an ODBC connection to the old database. I am able to successfully set up a SQL Server instance to the old database. However, when I try to setup a linked server to the old database, I get "Error 17: SQL Server does not exist or access is denied."

I have tried several different logins, I have checked the ODBC setup, and am stuck.

Any ideas on what could be causing this or what else I can do/check?

 

[ptheriault]

When you set up the ODBC are you doing so from the new server to the old server? Can the new server ping the old server? Which server are you trying to create the link from?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[MsChelle]

Yes, the ODBC setup is from New to Old, and works fine. I can ping the old server from the new. I need to create the link from the new to the old (link the old onto the new).

 

[ptheriault]

Have you tried to add your link server like this?

USE master;
GO
EXEC sp_addlinkedserver 
   'server_name',
   N'SQL Server'
GO

Then run sp_addlinkedsrvlogin to add the login account information.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[MsChelle]

I've tried both that way and through the Security portion of EM. It appears to setup fine, but any attempts to use it (or view the tables via EM) give me that error 17.

 

[ptheriault]

The problem then is with the account you are using to try to access it. Have you confirmed that you can login to the old server with that account?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[MsChelle]

That's what I thought, too. I checked the accounts, and have tried several, including the sa, Administrator, and user accounts. They all work on the ODBC connection, Server instance (separate registration), and the old server. None make the linked server work.

 

[ptheriault]

hmmm.
Have you tried using the DTS wizard as a work around to import the tables you need?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[MsChelle]

That's actually why I'm trying to do it this way. DTS copies were failing or only giving me partial data. Even when I created new tables on the old server with subsets of the larger tables and tried copying brand new tables to the new server database, I got fails. It would create the tables but no data came over. I figured if I can do a linked server, I can create the subset tables (select * into newtable where date_val < datemarker) directly into the new server databases.

 

[MsChelle]

OK I got it. I had to create an alias (there was a hyphen in the server name), then rebuild the registration, then create the linked server as an SQL Server, as opposed to an OLE.

Thanks for your help!