MS SQL Server – Two Servers, Two Databases, One Query – How To

You need to use the sp_addlinkedserver and sp_addlinkedsrvlogin procedures.

Here is what currently works for me albiet slowly: (Fill in your own values where relevant.)

EXEC    sp_addlinkedserver    @server='GM', @srvproduct='',
                                @provider='SQLOLEDB', @datasrc='Server Name',
				@catalog ='myDatabase'
EXEC sp_addlinkedsrvlogin 'GM', 'false', NULL, 'username', 'password'

--The Query, Using the Server Database I'm already in and my new linked server: GM--
SELECT
NUMBER,
DT_CREATED AS DATE_CREATED,
PRIORITY,
(SELECT COMPANY FROM GM.myDatabase.dbo.myTable WHERE ACCOUNTNO IN (SELECT TOP 1 ACCOUNTNO FROM GM.myDatabase.dbo.myTable WHERE Field1=(SELECT CUSTOMER_NUMBER FROM CUSTOMERS WHERE ID=ID_CUSTOMER))) AS COMPANY
FROM
MAIN_TABLE

I was inspired to this answer here.

The Transact-SQL Reference presumably installed with your Query Analyzer also provides lots of info if you look up those two key procedures: sp_addlinkedserver and sp_addlinkedsrvlogin

My Search Terms:

  1. two servers in one query
  2. sqlserver + (query two servers) OR (query across two servers)
  3. (query two servers) OR (query across two servers)

Posted by Greg Pinero (Primary Searcher) on Jun 8th, 2006 and is filed under Other, win32.

Both comments and pings are currently closed.

Add this post to Del.icio.us - Digg

Comments are closed.