June 25, 2009 at 1:50 pm
Is there anyway to get Report Designer to not parse the SQL of a stored procedure?
I have a stored procedure that runs like this
CREATE TABLE #suppevt (incidents int, sl_agn_seq int NOT NULL,sl_log_grp int NOT NULL)
ALTER TABLE #suppevt ADD CONSTRAINT PK_suppevt PRIMARY KEY (sl_log_grp,sl_agn_seq)
And it SSRD bombs out when I tell it to run the stored proc with "#suppevt does not exist"
June 26, 2009 at 9:06 am
Is this a limitation of RDL? I come from a crystal background. Crystal ran the procedure and produced the fields that it could report on based on the output, not actually parsing the sprocs.
June 26, 2009 at 9:33 am
you can create a table, and then insert into it directly, but if you alter a table, a GO statement is expected.
simply create your table with the constraint in a single statement to resolve your issue:
CREATE TABLE #suppevt (
incidents int,
sl_agn_seq int NOT NULL,
sl_log_grp int NOT NULL,
CONSTRAINT PK_suppevt PRIMARY KEY (sl_log_grp,sl_agn_seq) )
Lowell
June 26, 2009 at 9:48 am
Thanks on that regard.
I'm still having trouble with this sproc though. It's evaluating my SQL in some shape when I tell it to use EXEC usp_rpt_get_supportqueue_piechart as the query for an RDL it comes back with #suppevt does not exist.
The proc runs fine if I execute it from SSMS, and also from Crystal Reports. But when I try to develop an RDL with it I get
There is an error in the query. Invalid object name '#suppevt'.
Instead of being able to go to the next step of creating the report.
/**********************************************************************
Procedure: usp_rpt_get_supportqueue_piechart
Author: Mtassin/Cjarvis
Purpose: Return Aggregated Support Queue Counts based on criteria passed
Creation Date: 06-01-2009
Parameters:
@datefromdatetimeEarliest datetime to select data from. Defaults
to 2 months prior to current date at midnight
@datetodatetimeLatest datetime to select data from. Defaults
to present date at midnight
@regionvarchar(30)Region to select data on. Supports null values
@territoryvarchar(35) Territory to select data on. Supports null values
@routevarchar(10) Route to select data on. Supports null values
Modifications:
06-10-2009 - Mtassin Added @region, @territory,@route to parameters list
06-16-2009 - Mtassin Commented out the Record count query changed calls column
to CallCount.
06-18-2009 - Mtassin changed joins to supp_evt to joins to supp_log
**********************************************************************/
ALTER PROCEDURE [dbo].[usp_rpt_get_supportqueue_piechart]
@datefrom datetime=null,
@dateto datetime=null,
@region varchar(30)=null,
@territory varchar(35)=null,
@route varchar(10)=null
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
/********* Testing Variables **********/
--declare @datefrom datetime
--declare @dateto datetime
--declare @region varchar(30)
--declare @territory varchar(35)
--declare @route varchar(10)
--set @datefrom = '04/01/2008'
--set @dateto = '04/30/2008'
/*********************************/
IF @datefrom is null SET @datefrom = dateadd(mm,-2,dateadd(dd,datediff(dd,0,getdate()),0))
IF @dateto is null SET @dateto = dateadd(dd,datediff(dd,0,getdate()),0)
set @dateto = dateadd(dd,datediff(dd,0,@dateto)+1,0)
CREATE TABLE #suppevt (
incidents int, sl_agn_seq int NOT NULL,
sl_log_grp int NOT NULL,
CONSTRAINT PK_suppevt PRIMARY KEY (sl_log_grp,sl_agn_seq) )
INSERT INTO #suppevt
SELECT
COUNT(*),
sl_agn_seq,
sl_log_grp
FROM support..supp_log
WHERE
(sl_log_dat >= @datefrom AND sl_log_dat < @dateto)
GROUP BY
sl_log_grp,
sl_agn_seq
SELECT
GroupTerm = gt_type,
CallCount = sum(incidents)
FROM
#suppevt
LEFT JOIN pubdata..agn_info ON ai_agn_seq = sl_agn_seq
LEFT JOIN pubdata..agn_prod ON ap_agn_seq = ai_agn_seq
LEFT JOIN pubdata..agn_policies ON pol_code = ap_type AND pol_company = ap_companycode
LEFT JOIN pubdata..agn_TerritoryRoute pubdata ON art_seq_num = ai_route_seq
LEFT JOIN pubdata..agn_territory ON atr_seq_num = art_territory_seq
LEFT JOIN pubdata..agn_regionTerritory ON art_territory_seq = art_territory
LEFT JOIN pubdata..agn_Regions ON arg_id = art_region
LEFT JOIN support..Groups ON sl_log_grp = gr_grp_seq
LEFT JOIN support..grp_type ON gt_typ_seq = gr_typ_seq
WHERE ap_main_type = 1 AND
(@region is null OR arg_region = @region) AND
(@territory is null or atr_territory_name = @territory) AND
(@route is null or art_route_name = @route)
GROUP BY
gt_type
DROP TABLE #suppevt
June 26, 2009 at 1:21 pm
To fix this, put SET FORMAT_ONLY OFF; at the beginning of your procedure. Be aware that doing this causes SQL Server to actually run the procedure, and if it takes a long time to process will affect how long it takes to get the structure to return.
You could always modify the procedure so that you have a default select statement that matches the actual output but doesn't return any data. Un-comment the section while working on the report, then comment it out when you want to use the actual procedure. This does require additional maintenance but can avoid a long wait.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 26, 2009 at 1:31 pm
Umm when I do that I get the following error:
Msg 195, Level 15, State 5, Line 1
'FORMAT_ONLY' is not a recognized SET option.
June 26, 2009 at 1:37 pm
mtassin (6/26/2009)
Umm when I do that I get the following error:Msg 195, Level 15, State 5, Line 1
'FORMAT_ONLY' is not a recognized SET option.
Got it... SET FMTONLY OFF is the proper command.
Thanks!
The query runs in less than a sec... so it's not a big deal 🙂
Learning the nuances of RDL is though... heh... a part of SQL I've never tapped in my 12 years playing with it.
June 26, 2009 at 2:28 pm
Sorry about that - I didn't take the time to look it up and I get it wrong all the time. 😀
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply