Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SRS Cannot Read Return Set 1

Status
Not open for further replies.
ousoonerjoe

ousoonerjoe

Programmer
Jun 12, 2007
925
US
I'm having issues with SSRS failing to read the returning recordset. When executed in Query Analyzer, all returns exactly as expected. SSRS Returns nothing. In fact, in my testing it acts as if it will not run the EXECUTE command. The report is completed, just need the query to load at run-time. Any guidance is appreciated.
Code: 
[COLOR=blue]ALTER PROCEDURE[/color] cst_Rpt_Ncr_Summary(
	@StartDate	[COLOR=blue]DATETIME[/color] = [COLOR=red]'7/4/1776'[/color],
	@EndDate	[COLOR=blue]DATETIME[/color] = [COLOR=red]'12/31/2032'[/color],
	@CauseId	[COLOR=blue]INT[/color] = 0,
	@IssueDept	[COLOR=blue]INT[/color] = 0,
	@ActionDept	[COLOR=blue]INT[/color] = 0)
[COLOR=blue]AS[/color]
[COLOR=green]--============================================================================
-- Created By: xxxx - 11/19/2009
-- Purpose:	Move away from using SELECT * statements in any application.
--			Migrate towards a better naming convention.
--============================================================================[/color]
[COLOR=blue]DECLARE[/color] @Sql		[COLOR=blue]VARCHAR[/color](4000);
[COLOR=blue]DECLARE[/color] @ProcName	[COLOR=blue]VARCHAR[/color](50);
[COLOR=blue]SET[/color] @ProcName = [COLOR=red]'cst_Rpt_Ncr_Summary'[/color];
[COLOR=blue]SET[/color] @Sql = [COLOR=red]''[/color];

[COLOR=blue]BEGIN TRY
	BEGIN TRANSACTION[/color];
		[COLOR=blue]INSERT INTO[/color] Audit(ProcName, CmdType, Statement, ExecFrom, RecordIdNum, Arg1, Arg2, Arg3)
				[COLOR=blue]VALUES[/color](@ProcName, [COLOR=red]'REPORT'[/color], [COLOR=red]''[/color], [COLOR=red]''[/color], 0, @StartDate, @EndDate, @CauseId);
	[COLOR=blue]COMMIT TRANSACTION[/color];

	[COLOR=blue]SET[/color] @Sql = ([COLOR=red]'SELECT n.NonConformId, n.JobNum, 
			[IssueDept] = i.DeptName, 
			[Division] = d.DivName, 
			[Cause] = c.Descr, 
			[Status] = s.Descr, 
			[Description] = n.Description, 
			[Resolution] = n.Resolution, 
			[RecommendBy] = dbo.TRIM(pr.premp_first_n + '' '' + pr.premp_last_n), 
			[RecommendDate] = n.RecommendDate, 
			[ApprovedBy] = dbo.TRIM(pa.premp_first_n + '' '' + pa.premp_last_n), 
			[ApproveDate] = n.ApproveDate, 
			[ActionDept] = a.DeptName, 
			[CauseDesc] = n.CauseDesc, 
			[ResponsibleDept] = r.DeptName, 
			[QaApproval] = dbo.TRIM(pq.premp_first_n + '' '' + pq.premp_last_n), 
			[QaDate] = n.QaDate,
			[CreateDate] = n.CreateDate 
		FROM NonConform n 
			LEFT JOIN Departments i ON n.IssuingDept = i.DeptId 
			INNER JOIN Division	d ON n.Division = d.DivisionId 
			INNER JOIN NcrCause c ON c.NcrCauseId = n.Cause 
			INNER JOIN NcrStatus s ON s.NcrStatusId = n.Status 
			LEFT JOIN TmpPrEmp pr ON n.RecommendBy = pr.premp_num 
			LEFT JOIN TmpPrEmp pa ON n.ApprovedBy = pa.premp_num 
			LEFT JOIN Departments a ON n.ActionDept = a.DeptId 
			LEFT JOIN Departments r ON n.ResponsibleDept = r.DeptId 
			LEFT JOIN TmpPrEmp pq ON n.QaApproval = pq.premp_num 
		WHERE n.CreateDate BETWEEN '''[/color] + CONVERT(VARCHAR(12), @StartDate, 101) + [COLOR=red]''' AND '''[/color] + CONVERT(VARCHAR(12), @EndDate, 101) + [COLOR=red]''''[/color]);	

	[COLOR=blue]IF[/color] @CauseId <> 0
		[COLOR=blue]BEGIN 
			SET[/color] @Sql = (@Sql + [COLOR=red]' AND n.Cause = '[/color] + CAST(@CauseId [COLOR=blue]AS VARCHAR[/color](5)));
		[COLOR=blue]END 

	IF[/color] @IssueDept <> 0
		[COLOR=blue]BEGIN
			SET[/color] @Sql = (@Sql + [COLOR=red]' AND n.IssuingDept = '[/color] + CAST(@IssueDept [COLOR=blue]AS VARCHAR[/color](5)));
		[COLOR=blue]END

	IF[/color] @ActionDept <> 0
		[COLOR=blue]BEGIN
			SET[/color] @Sql = (@Sql + [COLOR=red]' AND n.ActionDept = '[/color] + CAST(@ActionDept [COLOR=blue]AS VARCHAR[/color](5)));
		[COLOR=blue]END 

	EXEC[/color] (@Sql);

[COLOR=blue]END TRY
BEGIN CATCH
	INSERT INTO[/color] ErrLog (Number, Severity, ErrState, 
			ErrSource, ErrLine, ErrMsg, Form, AppID)
		[COLOR=blue]SELECT[/color] ISNULL(ERROR_NUMBER(),-1), ISNULL(ERROR_SEVERITY(),0), ISNULL(ERROR_STATE(),0), 
			ISNULL(ERROR_PROCEDURE(),[COLOR=red]'Unknown.'[/color]), ISNULL(ERROR_LINE(),0), 
			ISNULL(ERROR_MESSAGE(),[COLOR=red]'Unknown: Failed to Execute SELECT.'[/color]), @ProcName, 3;
[COLOR=blue]END CATCH[/color]

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------
 
Sort by date Sort by votes
Try it without the dynamic sql. You can change your where clause to something like this:

Code: 
AND (@CauseId = 0 OR n.Cause = CONVERT(VARCHAR(5), @CauseId))
AND (@IssueDept = 0 OR n.IssuingDept = CONVERT(VARCHAR(5), @IssueDept))
AND (@ActionDept = 0 OR n.ActionDept = CONVERT(VARCHAR(5), @ActionDept))
 
Upvote 0 Downvote
River, excellent adjustment! Thank you.

How exactly is this working?

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------
 
Upvote 0 Downvote
No problem. How is what working? The portion of your where clause I posted? It's best practice to avoid dynamic sql unless it's the last resort, so you can get the same functionality by looking at the value of your parameter in your where clause.
 
Upvote 0 Downvote
Looks like i spoke too fast. There is something about the WHERE clause that is killing this in SSRS. I thought for sure that would clear it up. I knew it was having issues with the Dynamic SQL, and your solution removed that issue. All tests ran without the WHERE function correctly. Once the WHERE is added to the Stored Procedure, it returns nothing in SRS, but works great in Query Analyzer. As you can see in the code below, i've stripped out anything that could be 'getting in the way' and it still fails with the WHERE clause.

I still appreciate the tip. That'll be used in many different solutions in the future. I wasn't aware that one could structure the WHERE in such a manor that allows for a conditional usage.
Code: 
ALTER PROCEDURE cst_Rpt_Ncr_Summary(
	@StartDate	DATETIME = '7/4/1776',
	@EndDate	DATETIME = '12/31/2032',
	@CauseId	INT = 0,
	@IssueDept	INT = 0,
	@ActionDept	INT = 0)
AS
--============================================================================
-- Created By: xxxxx - 11/19/2009
-- Purpose:	Move away from using SELECT * statements in any application.
--			Migrate towards a better naming convention.
--============================================================================
SELECT [NonConformId] = n.NonConformId, 
		[JobNum] = n.JobNum, 
		[IssueDept] = i.DeptName, 
		[Division] = d.DivName,  
		[Cause] = c.Descr, 
		[Status] = s.Descr, 
		[Description] = n.Description, 
		[Resolution] = n.Resolution, 
		[RecommendBy] = dbo.TRIM(pr.premp_first_n + ' ' + pr.premp_last_n), 
		[RecommendDate] = n.RecommendDate, 
		[ApprovedBy] = dbo.TRIM(pa.premp_first_n + ' ' + pa.premp_last_n), 
		[ApproveDate] = n.ApproveDate, 
		[ActionDept] = a.DeptName, 
		[CauseDesc] = n.CauseDesc, 
		[ResponsibleDept] = r.DeptName, 
		[QaApproval] = dbo.TRIM(pq.premp_first_n + ' ' + pq.premp_last_n), 
		[QaDate] = n.QaDate,
		[CreateDate] = n.CreateDate 
	FROM NonConform n 
		LEFT JOIN Departments i ON n.IssuingDept = i.DeptId 
		INNER JOIN Division	d ON n.Division = d.DivisionId 
		INNER JOIN NcrCause c ON c.NcrCauseId = n.Cause 
		INNER JOIN NcrStatus s ON s.NcrStatusId = n.Status 
		LEFT JOIN TmpPrEmp pr ON n.RecommendBy = pr.premp_num 
		LEFT JOIN TmpPrEmp pa ON n.ApprovedBy = pa.premp_num 
		LEFT JOIN Departments a ON n.ActionDept = a.DeptId 
		LEFT JOIN Departments r ON n.ResponsibleDept = r.DeptId 
		LEFT JOIN TmpPrEmp pq ON n.QaApproval = pq.premp_num 
	WHERE n.CreateDate BETWEEN  @StartDate AND @EndDate 
		AND(@CauseId = 0 OR n.Cause = @CauseId)
		AND (@IssueDept = 0 OR n.IssuingDept = @IssueDept)
		AND (@ActionDept = 0 OR n.ActionDept = @ActionDept);

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------
 
Upvote 0 Downvote
After some further trial and errors, we were able to locate the issue. Something about SSRS and passing NULLs to stored procedures does not always play well together. By setting the parameters to pass the default values instead of passing NULL and letting the stored procedure fill in the default values, everything works as expected now.

Credit where credit is due: Thanks to RiverGuy for the new way to approach WHERE clauses when you may or may not need it. It will save massive amounts of time and typing. I have 2 other major reports with multiple searchable fields that will be getting this adaptation in the next couple of days.

(By the way, i did figure out how the WHERE was working exactly.)

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Mr Wonderful
  • Locked
  • Question
SSRS Dataset query all Oracle Tables/Columns;Return the maximum length of data values in each column
Replies
0
Views
290
Mr Wonderful
Mr Wonderful
xarzu
  • Locked
  • Question
project settings in SSRS deployment
Replies
0
Views
199
xarzu
xarzu
AndyH1
  • Locked
  • Question
Report Server cannot connect to database
Replies
0
Views
66
AndyH1
AndyH1
Luvsql
  • Locked
  • Question
Cannot see shared datasets in Report Builder
Replies
0
Views
85
Luvsql
Luvsql
Auguy
  • Locked
  • Question
Reporting Services 2000 - &quot;This Program cannot display the webpage&quot;
Replies
0
Views
28
Auguy
Auguy

Part and Inventory Search

Sponsor

Back
Top