Search code examples
c#sqlconnection-stringlinked-server

Specifying linked sql server in web.config

How do you set a linked server in MVC3 Web.Config?

We have a linked server that has a stored procedure SP_PROC that we need to run. However, both the main SQL server TESTSERVER1 and the linked server is set up in a test environment TESTSERVER2, so the name/ip will change once the application goes live. Because of this I don't want to specify anything more than the SP name in the code.

I've tried specifying the linked server in the initial catalog field of the connection string, but that's throwing errors - so I assume I'm barking up the wrong tree..

Example connection string:

<add name="connectionName" connectionString="data source=TESTSERVER1;Initial Catalog=TESTSERVER2;user id.../>
Solution

  • The Linked Server is configured on the server, not the connection. You connect to the main server and your query will reference the linked server by name in the query:

    SELECT * FROM TESTSERVER2.database.dbo.tablename

    I would recommend not using the actual server name for the linked server name, but abstracting it so your queries can port from one sever to the next.