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)