August 21, 2007 at 12:26 pm
I need to create a report in SQL Server Reporting services that displays data from several databases.
Should I create one stored procedure that contains multiple select statements and UNION ALL them together, create db specific stored procedures and combine the data in SSRS or is there another recommended approach to this type of problem?
TIA
Dean
August 21, 2007 at 1:27 pm
I had to do something similar to monitor a variety of field SQL Server nodes that I have very limited access to.
I created an SSIS routine on one of my department servers. In the SSIS package I created connection strings for each destination node. Then I created control flow, running the same query against each node. The results are stored on the same local department servers, which I can run reports against. this approach means i don't have to manage usp's across many nodes. All the logic is stored on my department server.
August 22, 2007 at 6:54 am
If you have this option it is the way to go. Are the db's all on the same server?
I would write db specific sp's and then have a master sp in one of the db's that executes the other sps like
Create procedure sp_data
AS
Create table #table ()
Insert Into #table
Select
From
tables
Insert into #table
Exec db1.dbo.sp_data
Insert into #table
Exec db2.dbo.sp_data
...
Select * from #table
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply