SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SSRS 2008 stored proc across multiple dbs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SunnyDee
Yak Posting Veteran

76 Posts

Posted - 03/06/2013 :  13:46:19  Show Profile  Reply with Quote
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
52325 Posts

Posted - 03/06/2013 :  23:38:53  Show Profile  Reply with Quote
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/

Go to Top of Page

SunnyDee
Yak Posting Veteran

76 Posts

Posted - 03/07/2013 :  13:46:27  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/07/2013 :  15:03:42  Show Profile  Reply with Quote
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/

Go to Top of Page

SunnyDee
Yak Posting Veteran

76 Posts

Posted - 03/07/2013 :  15:50:51  Show Profile  Reply with Quote
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





Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/08/2013 :  00:19:19  Show Profile  Reply with Quote
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/

Go to Top of Page

SunnyDee
Yak Posting Veteran

76 Posts

Posted - 03/08/2013 :  09:00:07  Show Profile  Reply with Quote

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




Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/08/2013 :  09:20:03  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000