Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

Stored procedure not working in Report

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
allelopath
Asked:
allelopath
  • 5
  • 4
  • 2
2 Solutions
 
jonnidipCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
allelopathAuthor Commented:
@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
 
jonnidipCommented:
"<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
 
allelopathAuthor Commented:
@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
 
allelopathAuthor Commented:
@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
 
jonnidipCommented:
The .data file is what I mean.
You can try to delete or rename it and test your report again.
0
 
allelopathAuthor Commented:
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
 
jonnidipCommented:
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
 
allelopathAuthor Commented:
YES, the .data file was indeed the problem. Thanks.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now