Linked Server Remote Server

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

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!