Query Multiple SQL Server DB's

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 2000 Forums

Transact-SQL (2000)

Query Multiple SQL Server DB's

Author

Topic

weazel
Starting Member

2 Posts

Posted - 2005-02-03 : 15:10:51

I want to be able to run a single script on multiple servers with the same tables, and return data in one report.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 15:52:42

Alright.... I want a million dollars :)

What is your question specifically?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-03 : 16:09:30

Let's see if the crystal ball is working.

select a,b,c from server1.dbase.dbo.t
union all
select a,b,c from server2.dbase.dbo.t

lookup linked servers in BOL.

rockmoose

weazel
Starting Member

2 Posts

Posted - 2005-02-03 : 17:13:59

Man from some of the posts i have seen i thought you guys were mind readers!
My appologies, i was in a conference call at the time and hit send before adding my details. So here goes:

I have 3 servers A,B,C on those 3 servers i have SQL2k, and all 3 have the same database Hotel. I use sql reports server (great tool) for providing reports to the execs, and often have to create 3 seperate reports to provide the data they request.

I want to be able to run a query select * from System_data (simple query used for ease of reading) from Server A but also include the data from Servers B & C in one single report. Can this be done?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-03 : 17:47:58

Did You read my post ?

If you are allowed to set up linked servers in your environment you could do the following:
1. On server A link the servers B and C
2. Create a view like such:

CREATE VIEW vDataFrom3Servers AS
SELECT a,b,c FROM Hotel.dbo.System_Data -- data from server A
UNION ALL
SELECT a,b,c FROM ServerB.Hotel.dbo.System_Data -- data from server B, notice the 4 part naming
UNION ALL
SELECT a,b,c FROM ServerC.Hotel.dbo.System_Data -- data from server C

3. for your report do:
SELECT a,b,c FROM vDataFrom3Servers !

rockmoose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-03 : 17:51:17

You can use osql.exe (Query Analyzer like tool that is run from the command line). Instead of using the o switch, just output the first result with >C:\FileName.txt, then all the others append with >>C:\FileName.txt.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources