Linked Server Explanation

Lawrence Barnes
Lawrence Barnes used Ask the Experts™
Hello EE:
I'm at a new environment where I can connect (via SQL Server Studio Mgr) to 3 servers.  I'm used to connecting to only one.  The three servers are:
1. A server where I can build tables, stored procedures, etc.
2. A production server with some information I need and read only access
3. Another production server with different information and read only acess

The question is around the definition of linked servers...the users there refer to the two production servers as linked servers and when they write queries to extract information from them they encapsulate the SQL code in quotes and push it to the server with an EXEC.  However,don't understand why I can't connect to all three servers via SQL Server Studio Mgr and use standard (non quoted) SQL queries to get the data I need.

If I open connections to all three servers in SQL Server Studio Manager, is there any reason I can't write a query like this?

SELECT ros1.Field1, ros2.Field2
FROM [ReadOnlyServerONE].[Database1].[dbo].[Table1] ros1 With (NoLock)
INNER JOIN  [ReadOnlyServerTWO].[Database2].[dbo].[Table2] ros2 With (NoLock)
INTO [ServerICanWriteTo].[Database3].[dbo].[Table3]

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
You can't. The query is executed at the server you send it to, and your client is the one who has all three connections - no way of fitting that together. The server needs to know how to get to other servers, and which credentials to use. And that is what a linked server definition is for.

But assembling dynamic SQL, just to push it via EXEC to one single server, is - ehm, let's say too complicated. If you have a linked server, you can do as you showed - combine the results easily. That has many advantages, but one major disadvantage - transactions are hold longer, and hence more locking is involved. That might look as it does not apply to your example query, since you used the nolock hint, but rumors say that hints are ignored when applied to linked servers.


Ok...that explains it.  Thank you.


Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial