Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Trying to create a linked server from server A (2005) to B (2008 R2). Everything worked when created. The login on server B was mapped to two databases: DataBase1 and Database2. Database1 was the default.

Database2 was taken off-line. When opening the User Mapping, there is a message, "One or more databases are inaccessible and will not be displayed in list." I assume Database2 is the culprit. I brought Database2 online and unchecked it.

When testing the connection on the Linked Server, it fails because it can't access Database2. Again, Database1 is the default.

I rebuilt the login using the exact same script with no mention of Database2, still get the error.

Because I need a different Linked Server name that does not match the actual SQL Server name, let's say I want to link to server "A" but call the link "X"

EXEC master.dbo.sp_addlinkedserver @server = N'X'
, @srvproduct=N'Microsoft OLE DB Provider for ODBC'
, @provider=N'MSDASQL', @datasrc=N'X'
, @provstr=N'DRIVER={SQL Server};SERVER=B;UID=test;PWD=*******;'

I can go into SSMS and create a linked server through the GUI. Picking SQL Server, which won't allow me to use a name for the linked server except the same name as the server and it works. This does not use the OLE DB Provider for ODBC.

EXEC master.dbo.sp_addlinkedserver @server = N'B', @srvproduct=N'SQL Server' 

Can I script this so I can indicate the link name as X and the server or datasrc?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

I would also change the parameters as follows:

  • @srvproduct = 'sql_server'

  • @provider = 'SQLNCLI' if you're coming from 2005 (and 'SQLNCLI10' from 2008/8R2, and 'SQLNCLI11' from 2012, etc.)

  • @provstr can be removed entirely; it's not necessary with this product and provider; your linked server logins will handle that.

Whatever SQL Server login is being used by the linked server probably has an offline/removed default database. Change the default database for that login to one that exists and that the user has permissions to and try again.

This is a guess, as you didn't post the CREATE USER script that generated the particular username logging in.

share|improve this answer
    
Changing the svrproduct and provider did it. Thanks. –  JeffO Feb 7 at 16:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.