Solved

Stored procedure not working in Report

Posted on 2013-05-10
11
618 Views
Last Modified: 2016-02-11
I'm looking for ideas as to why executing a certain stored procedure as a dataset does not work in a SSRS Report.

By does not work, I mean no data is returned.

In BIDS by going into the Shared Dataset Properties -> Query ->  Query Designer -> ! (which runs the query). The column headers appear and data appear.

However, in the report itself, no data is returned. This stored procedure should return one row of data, no more no less. The dataset 'knows' the columns returned by the sproc in that the fields in the dataset properties are shown correctly. It has ` parameter, and I've tried hard-coding it, but makes no difference. When I look in SQL Server profiler on the database/server, indeed nothing happens when running the report.

I have another dataset in the same report, running another sproc and it runs correctly. This is sproc returns many rows. Is there something special about calling an sproc that returns only 1 row?
0
Comment
Question by:allelopath
  • 5
  • 4
  • 2
11 Comments
 
LVL 13

Accepted Solution

by:
jonnidip earned 500 total points
ID: 39155389
Please make sure you don't have a local .rdl.data file, that stores a cached version of your previous request.
That file is found in your VS project folder and is safe to delete.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39155404
Five bucks says ...

(1)  You don't have SET ANSI_WARNINGS OFF, such that it's thinking (1 rows returned) is the return recordset.
(2)  You have a troubleshooting-like SELECT statement before the final return recordset, and that's what SSIS is interpreting.  Even if it's in an IF block, SSIS will interpret the first SELECT.
(3)  Something else that gets me out of paying five bucks.

>Is there something special about calling an sproc that returns only 1 row?
Nope.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39155453
(4)  It is returning a row, but in the table / matrix that populates it the detail section was deleted, so no rows are displayed.
(5)  It is returning a row, but the detail section's Hidden property is set to True.
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 1

Author Comment

by:allelopath
ID: 39155560
@jonnidip:
The only rdl file I see is in Solution Explorer -> Reports. This certainly has to exist, so I'm not sure where you are saying to look.
0
 
LVL 13

Assisted Solution

by:jonnidip
jonnidip earned 500 total points
ID: 39155567
"<yourRDL>.rdl.data" is a file that is created when you test the report in VS.
You have to look for this file in the FOLDER, not in the project.
0
 
LVL 1

Author Comment

by:allelopath
ID: 39155587
@jimhorn:
ANSI_WARNINGS is not in the sproc. Fwiw, there is:
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [reports].[uspCoverSheet]
@MyNumber varchar(10) = '0000000000'
AS
BEGIN
	SET NOCOUNT ON;

select top 1 [MyId]
...

Open in new window

0
 
LVL 1

Author Comment

by:allelopath
ID: 39155601
@jonnidip:
Do you mean:
C:\Word\Visual Studio 2008\Projects\MyProject\MyProject\MyProject.rdl
This folder also has the .rsd, .rds, .data, .rptproj, and .user files
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 39155676
The .data file is what I mean.
You can try to delete or rename it and test your report again.
0
 
LVL 1

Author Comment

by:allelopath
ID: 39155719
I deleted the .data file, then went to Preview tab, and this shows:
An error occurred during local report processing
An error occurred during report processing
An attempt was made to set a data set parameter '@MyNumber' that is not defined in this data set.
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 39155751
Your dataset (from stored result) may not match your actual report schema.
Would you please check if your stored returns MyNumber field?

You may also check if the profiler does now show your stored execution.

Please note that the .rdl.data file is created when you test your project and the file itself is missing. If you want to have fresh data at a test execution you will need to delete the file again.


Regards.
0
 
LVL 1

Author Comment

by:allelopath
ID: 39155899
YES, the .data file was indeed the problem. Thanks.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS Problems 9 101
Alternative to CDC 1 43
SSRS Highlight Specific Column 3 76
SQL Server query - can a #temp table be improved with indexing? 6 59
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question