hopefully, a simple question for the SQL experts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

hopefully, a simple question for the SQL experts

Author

Topic

cckelly
Starting Member

9 Posts

Posted - 2011-09-09 : 11:18:02

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 plan to use a "union" query to pull all the data and eliminate any duplicate records. I've been trying to come up with the correct syntax for fully-qualified object names. What makes sense to me is

SELECT {all the columns I want} FROM FAYRMS1.VSI_DATA.dbo.table     
     INNER JOIN other tables WHERE {conditions}
union
SELECT {all the columns I want} FROM FAYOSSIRMS.rmsconv.dbo.table 
     INNER JOIN other tables WHERE {conditions}
union 
SELECT {all the columns I want} FROM FAYOSSIRMS.rms.dbo.table     
     INNER JOIN other tables WHERE {conditions}

"Server Box1" : server FAYRMS1 (SQL Server 9.0.3042)
stores Database VSI_DATA

"Server Box2" : server FAYOSSIRMS (SQL Server 9.0.1399)
stores Database rmsconv and
Database rms

I've put brackets [] around the individual parts of the fully-qualified object name, but I continue to get error messages telling me that I have "invalid object name."

I can "connect" to all three databases individually (Windows Authentication).
When connected to a 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've googled and done a lot of reading, but I don't know the best way to query data from all three databases.

On one of my "google quests" I came across a suggestion to NOT use linked servers, but to use OPEN-dataset/rowset to pull my data from multiple servers/databases "if" the query isn't run very often. I don't know about performance issues. What database should I be connected to when I run the query? I want to have the least impact on our production environment (database rms on server FAYOSSIRMS).

Currently, there are no linked servers on either "box." I would really appreciate advice and guidance !!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-09 : 11:27:47

You need to create the linked servers before you can access one from the other.

cckelly
Starting Member

9 Posts

Posted - 2011-09-09 : 11:37:09

Using linked servers is one option. But, what about using open-dataset/rowset? The article I read said to use that INSTEAD of using linked servers, particularly if the query isn't run very often. (The syntax appeared to embed all the "location" info.)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-09 : 12:03:15

Whichever way you want. I prefer linked servers.

The syntax you posted is for linked servers, and it will fail with "Could not find server FAYRMS1 in sys.servers." if you haven't created the linked servers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources