January 14, 2008 at 9:42 am
Currently one of our reports are deployed for each database on the report server. They need to be deployed for each database that we create. It is very cumbersome and takes up time.
Is it possible to set up a parameter which changes the database that the report is executed on via a dynamic query or a dynamic connection?
Thanks!
January 23, 2008 at 10:02 am
Does this article help?
http://qa.sqlservercentral.com/columnists/bKhawaja/2945.asp
Regards
Jonathan
January 23, 2008 at 10:13 am
Thanks, but this only works in SQL 2005 and not 2000. If all else fails, we'll be upgrading the reporting services to 2005.
January 24, 2008 at 8:13 am
We do this quite frequently in our SSRS 2005 reports. It is done by including a USE statement in the SQL code. Like this:
"USE " & Parameters!DatabaseParameter.value & " SELECT ...... "
Ed
January 24, 2008 at 8:49 am
The dynamic connection in the example does work to connect to 2000 as well as 2005 servers, but often the syntax of the query you run on a 2000 server differs from a 2005 server in these cases I handle the code in the dataset like this example to pull back a list of the databases on the server :-
IF SUBSTRING(@@version,23,4)='2000'
BEGIN
SELECT [name] AS Database_Name
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('AdventureWorks', 'Pubs', 'Northwind')
END
ELSE
BEGIN
SELECT [name] AS Database_Name
FROM master.sys.sysdatabases
WHERE [name] NOT IN ('AdventureWorks', 'Pubs', 'Northwind')
END
The difference is that you unfortunately cannot see the results of a SQL Server 2000 query in the reports part of SSMS, you can view via the Reports web server however.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply