May 9, 2004 at 11:50 am
Hello All,
I'm a basic level VB programmer and SQL Server 2000 junior-level admin.
I want to run a similar query to 90 different SQL servers to ultimately dump into a text or excel file for further reporting.
Let me explain the scenario, I monitor 90 retail stores running SQL Server MSDE 2000 sp3. I need to run queries on all servers at once. So far I have maintained all the store databases as linked servers on the host SQL server at home office. The queries I have written so far have been of the cut and paste approach. Example:
select '100' as Store, *
FROM ST100USA.DB_RETAIL.dbo.RECLINE A INNER JOIN ST100USA.DB_RETAIL.dbo.RECMAST B ON A.RECEIPT_NO = B.RECEIPT_NO
WHERE (B.SALE_DATE = CONVERT(CHAR, GETDATE()-1, 101))
ORDER BY B.SALE_DATE, A.RECEIPT_NO
UNION ALL
(The next would be a copy of the first the replace 100 with next store number)
select '105' as Store, *
FROM ST105USA.DB_RETAIL.dbo.RECLINE A INNER JOIN ST105USA.DB_RETAIL.dbo.RECMAST B ON A.RECEIPT_NO = B.RECEIPT_NO
WHERE (B.SALE_DATE = CONVERT(CHAR, GETDATE()-1, 101))
ORDER BY B.SALE_DATE, A.RECEIPT_NO
(again and again for 90 stores)
For now I have been running these in Query Analyzer, sending output to a text file, and then distributing to end users for use in excel.
So here's where I get to the point:
I need a way to do all this with automation and I can't figure out how to make the server name a looping variable. I have a seperate table with all the server names on the host server. Most of my store numbers are from 100 to 200, so maybe I could count that way.
I don't want to do this in VB because I need flexibility to run whatever queries I want without compiling. I basically need help writing this outer shell so the I can put whatever query I need inside.
Thanks in advance for anyone who may point me in the right direction!
May 9, 2004 at 12:08 pm
Have you considered using a view for each table to link them all for reuse?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply