May 5, 2009 at 12:11 pm
This will eventually be done in SSRS, but I need the sql script first. I have a server with multiple db's. I want to be able to select from mulitple db's (or all) and pass the value into the sql script to report on those db's. The script is exactly the same for all db's with the difference of the db names only. Is this possible?
May 5, 2009 at 12:18 pm
Yes it is possible, but you will have to either use Dynamic SQL or use a temporary table to hold the results form the query against each database with IF statements like this:
If @list like '%db_name%'
Begin
Insert into #data
Select ....
End
Are you planning on using a stored procedure for the dataset or ad hoc sql in the report?
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
May 5, 2009 at 12:26 pm
Jack Corbett (5/5/2009)
Yes it is possible, but you will have to either use Dynamic SQL or use a temporary table to hold the results form the query against each database with IF statements like this:
If @list like '%db_name%'Begin
Insert into #data
Select ....
End
Are you planning on using a stored procedure for the dataset or ad hoc sql in the report?
I dont know exactly the most efficient route to go about at this moment, but it will be querying against a maximum of 24 dbs. So in your example code, will it run the INSERT for each db selected?
May 5, 2009 at 12:41 pm
Yes once for each database selected.
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
May 5, 2009 at 1:14 pm
excellent!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply