SSRS 2008 stored proc across multiple dbs

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

Analysis Server and Reporting Services (2008)

SSRS 2008 stored proc across multiple dbs

Author

Topic

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2013-03-06 : 13:46:19

Hello All:

We are running SSRS 2008. We are reporting on multiple databases. In the past, I have used a dynamic sql query to select the data from multiple databases into a temp table. This has worked ok, but the performance is not great.

Is there any way to include parameters in a dynamic sql query in SSRS? I have not been able to do this using the above method, but am wondering if anyone has had any success using a stored proc that can pass the parameter to SSRS to limit the result set.

We have over 40 databases and the result set is large and cumbersome. I added filters within SSRS to limit the result set in the report, but I would like to limit the amount of data returned in the sql query using a parameter to improve performance.

Any help is appreciated.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 23:38:53

you can add parameters within dynamic query. use sp_executesql to execute dynamic sql queries

see

http://msdn.microsoft.com/en-in/library/ms175170(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2013-03-07 : 13:46:27

thank you for your response. The code is posted below. I am using dynamic sql to run a query across multiple databases on a sql instance. The databases can be added or dropped frequently, so the code is designed to have no hard coding for the database names.

For this report, the dataset is too large and I need a parameter before the result set is pulled into SSRS. The code below is not recognizing the parameter for @Period. When the report generates, it looks like the parameter as NULL.

Does SSRS have the capability of using a parameter in a dynamic sql query? I have used parameters in multiple other reports, but have had no success in using parameters in dynamic sql. Is there something incorrect in the syntax?

Thanks.. my code is below:

Use master

Create Table #t
(Worker Char(10),
SalesPerson Char(10),
Period char (6),
SPTotal float,
JobTitle Char (30),
Firstname Char(15),
LastName Char (20),
SvcChangeReason char(30),
SiteCompanyID char(10),
CompanyID char (10),
Status char(10),
DatabaseName char (30),
Contact char (20)
)
---- Cursor for returing all db names for reporting purposes -----

DECLARE @name Varchar(80) -- database Name

---- Exclude DBs and demo dbs --------------------------
DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE NAME NOT IN ('MASTER', 'TEMPDB', 'MODEL', 'MSDB', 'MDBADB')
AND (NAME NOT LIKE '%REPORTSERVER%')
AND (NAME NOT LIKE '%TEST%')
AND (NAME NOT LIKE '%DEMO%')
AND (NAME NOT LIKE '%BU')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

------ BEGIN QUERY RESULTS -----------------------
declare @PERIOD char (6)
declare @SQL nvarchar(max)
set @SQL =

'USE ['+@name+'] INSERT INTO #t
( worker ,
salesperson,
period,
SPTotal,
JobTitle,
FirstName,
LastName,
SvcChangeReason,
SiteCompanyID,
CompanyID,
Status,
DatabaseName,
Contact)

-----------------------------------------------Period 1 Begin --------------------------------------------------------------------------------------
SELECT

worker ,
salesperson,
period,
SPTotal,
JobTitle,
FirstName,
LastName,
SvcChangeReason,
SiteCompanyID,
CompanyID,
Status,
DatabaseName,
Contact)

FROM SOMETABLES

WHERE Period = @PERIOD

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 15:03:42

yep...but for that best way is to use sp_executesql to execute them. see earlier posted link

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2013-03-07 : 15:50:51

I do use sp_executesql.. I just didn't post that part of my query sorry..

execute sp_executesql @SQL

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor
DEALLOCATE db_cursor

Select #t.*

The issue is that the query won't return the results based on the @Period. It is ignoring it. Can I not use a parameterized query with dynamic sql using sp_executesq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 00:19:19

you can, where's the declaration part though? check that link. it has an example on how to use sp_executesql. you need to declare parameters used inside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2013-03-08 : 09:00:07

I'm declaring the variable right after the commented line --- Begin Query Results

Should I be declaring it somewhere else in my code? I've reposted my code (the whole thing this time).. Do you mind taking a look at where I'm declaring @Period to see if I should move it somewhere else?

Thanks so much for your help so far!

Use master

Create Table #t
(Worker Char(10),
SalesPerson Char(10),
Period char (6),
SPTotal float,
JobTitle Char (30),
Firstname Char(15),
LastName Char (20),
SvcChangeReason char(30),
SiteCompanyID char(10),
CompanyID char (10),
Status char(10),
DatabaseName char (30),
Contact char (20)
)
---- Cursor for returing all db names for reporting purposes -----

DECLARE @name Varchar(80) -- database Name

---- Exclude DBs and demo dbs --------------------------
DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE NAME NOT IN ('MASTER', 'TEMPDB', 'MODEL', 'MSDB', 'MDBADB')
AND (NAME NOT LIKE '%REPORTSERVER%')
AND (NAME NOT LIKE '%TEST%')
AND (NAME NOT LIKE '%DEMO%')
AND (NAME NOT LIKE '%BU')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

------ BEGIN QUERY RESULTS -----------------------
declare @PERIOD char (6)
declare @SQL nvarchar(max)
set @SQL =

'USE ['+@name+'] INSERT INTO #t
( worker ,
salesperson,
period,
SPTotal,
JobTitle,
FirstName,
LastName,
SvcChangeReason,
SiteCompanyID,
CompanyID,
Status,
DatabaseName,
Contact)

-----------------------------------------------Period 1 Begin --------------------------------------------------------------------------------------
SELECT
Fields above
FROM SOMETABLES

WHERE Period = @PERIOD

execute sp_executesql @SQL

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor
DEALLOCATE db_cursor

Select #t.*

Drop table #t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 09:20:03

I was sugggesting to declare it within sp_executesql

something like


declare @PERIOD char (6)
declare @SQL nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(500); 
set @SQL = 

'USE ['+@name+'] INSERT INTO #t
( worker ,
salesperson,
period,
SPTotal,
JobTitle,
FirstName,
LastName,
SvcChangeReason,
SiteCompanyID,
CompanyID,
Status,
DatabaseName,
Contact)
SELECT
Fields above
FROM SOMETABLES


WHERE Period = @PERIOD'

SET @ParmDefinition = N'@PERIOD char (6),@name Varchar(80)';

execute sp_executesql @SQL,@ParmDefinition,@PERIOD=@PERIOD,@name=@name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources