| Author |
 Topic  |
|
|
SunnyDee
Yak Posting Veteran
71 Posts |
 Posted - 03/06/2013 : 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
India
47023 Posts |
|
|
SunnyDee
Yak Posting Veteran
71 Posts |
Posted - 03/07/2013 : 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
India
47023 Posts |
Posted - 03/07/2013 : 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
71 Posts |
Posted - 03/07/2013 : 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
India
47023 Posts |
Posted - 03/08/2013 : 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
71 Posts |
Posted - 03/08/2013 : 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
India
47023 Posts |
Posted - 03/08/2013 : 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/
|
|
|
| |
Topic |
|
|
|
SSRS 2008 stored proc across multiple dbs
