Add a linked Server in SSMS

Add linked Server in SSMSI was having issue with running a linked server query and getting the following error

Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because every code path results in an error; see previous errors for some of these.

I played with various functions to fixed the problem, namely sp_serveroption, sp_addlinkedsrvlogin but nothing fixed the error. Eventually I found that we can Linked Server in SQL Server Management Studio using a GUI. I tried that and it fixed the error for me. This is how to add linked server in SSMS

1. In SQL Server Management Studio go to “Server Object” on the root level on left hand side in “Objects Explorer”.
2. Expand “Linked Server” + sign to any linked server if any.
3. Right click on Linked Server and select “Add New Linked Server”
4. Select SQL Server Radio box
5. In the Security option below general, select “Be made using this security context”. Provide your remote db login name and password. Usually login name will be “sa”.
6. Select OK

Now run query against your server using the following sample code

select * from
openquery("168.255.0.10", 'select * from Students')

You can try adding linked server from command line as well. Make sure to delete you linked Server by right clicking on it and delete so no one can use it!

Advertisements
This entry was posted in SQL Server 2008, Tutorials/Tips and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s