I need options to query multiple SQL servers

Question

In recent months, we have converted to a new software application. The migration team (vendor) left the old database intact. They converted some of the old data to an "archive" database using the new-vendor's database structure. (That way the new software can be used to access the archived data--thereby eliminating the need for old-software licenses.) Since the conversion, our company has entered data into the new database.

I need to pull data from all THREE databases (on two SQLserver2005 servers). I would like to use a "union" query to pull all the data and automatically eliminate any duplicate records.

I've gone on "google quests" and done some reading. One option is linked servers. Another option I came across is OPENDATASOURCE.

My three databases are located on two SQLservers:

• "Server Box1" : houses server FAYRMS1 (SQL Server 9.0.3042),one database of old legacy data, named VSI_DATA
• "Server Box2" : houses server FAYOSSIRMS (SQL Server 9.0.1399), one database of archive data, named rmsconv AND one database of "live" data, named rms

I can "connect" to all three databases individually (Windows Authentication). When connected to each specific database, I can execute a query that returns the data I need. I have trouble when I am connected to one database and I try to pull data from another database. (I tried creating fully-qualified names in my FROM clause--I got errors. I queried sys.servers and discovered that the databases aren't linked.)

I've googled and done a lot of reading, but I haven't found an answer. What is the syntax to do a UNION query that will pull data from 3 databases?

Answer 1

I would create linked servers to do this if you're just doing simple selects. OPENDATASOURCE is not necessary. Your query would be something like this:

SELECT
    Field1, Field2, Field3, Field4
FROM [Server1].[Database1].[MySchema].[Table1]
UNION
SELECT
    Field1, Field2, Field3, Field4
FROM [Server2].[Database2].[MySchema].[Table2]
UNION
SELECT
    Field1, Field2, Field3, Field4
FROM [Server3].[Database3].[MySchema].[Table3]

Assuming you have 3 linked servers named Server1, Server2, and Server3

For your environment, you don't really say which instance you're querying from so I'm going to assume you're working from the instance that has the database of live data.

Create a linked server on FAYOSSIRMS that points to FAYRMS1, name it as such - FAYRMS1.

Then your query looks like this:

SELECT
    [SomeData]
FROM [rms].[dbo].[Table1]
UNION
SELECT
    [SomeData]
FROM [rmsconv].[dbo].[Table2]
UNION
SELECT
    [SomeData]
FROM
    [FAYRMS1].[VSI_DATA].[dbo].[Table3]

(assuming your schema is dbo)

EDIT
If you're stuck with OPENDATASOURCE then give this a shot:

SELECT
    [SomeData]
FROM [rms].[dbo].[Table1]
UNION
SELECT
    [SomeData]
FROM [rmsconv].[dbo].[Table2]
UNION
SELECT
    [SomeData]
FROM OPENDATASOURCE('SQLNCLI','Data Source=FAYRMS1;Integrated Security=SSPI')
    .VSI_DATA.dbo.Table3

That will open a connection to FAYRMS1 using the SQL Native Client and Windows Authentication