<div>
Five bucks says ...<br />
<br />
(1) &nbsp;You don't have SET ANSI_WARNINGS OFF, such that it's thinking (1 rows returned) is the return recordset.<br />
(2) &nbsp;You have a troubleshooting-like SELECT statement before the final return recordset, and that's what SSIS is interpreting. &nbsp;Even if it's in an IF block, SSIS will interpret the first SELECT. <br />
(3) &nbsp;Something else that gets me out of paying five bucks. <br />
<br />
&gt;Is there something special about calling an sproc that returns only 1 row?<br />
Nope.
</div>


<div>
(4) &nbsp;It is returning a row, but in the table / matrix that populates it the detail section was deleted, so no rows are displayed. <br />
(5) &nbsp;It is returning a row, but the detail section's Hidden property is set to True.
</div>


<div>
@jonnidip:<br />
The only rdl file I see is in Solution Explorer -&gt; Reports. This certainly has to exist, so I'm not sure where you are saying to look.
</div>


<div>
@jimhorn:<br />
ANSI_WARNINGS is not in the sproc. Fwiw, there is:<br />

<pre><code class="code-10 nolinks" id="code-20-39155587-1"><span>USE [MyDatabase]</span>
<span>GO</span>
<span>SET ANSI_NULLS ON</span>
<span>GO</span>
<span>SET QUOTED_IDENTIFIER ON</span>
<span>GO</span>
<span></span>
<span>ALTER PROCEDURE [reports].[uspCoverSheet]</span>
<span>@MyNumber varchar(10) = '0000000000'</span>
<span>AS</span>
<span>BEGIN</span>
<span>	SET NOCOUNT ON;</span>
<span></span>
<span>select top 1 [MyId]</span>
<span>...</span>
</code></pre>
</div>


<div>
@jonnidip:<br />
Do you mean:<br />
<i>C:\Word\Visual Studio 2008\Projects\MyProject\My<wbr />Project\My<wbr />Project.rd<wbr />l</i><br />
This folder also has the .rsd, .rds, .data, .rptproj, and .user files
</div>


<div>
The <i>.data</i> file is what I mean.<br />
You can try to delete or rename it and test your report again.
</div>


<div>
I deleted the .data file, then went to Preview tab, and this shows:<br />
<i>An error occurred during local report processing<br />
An error occurred during report processing<br />
An attempt was made to set a data set parameter '@MyNumber' that is not defined in this data set.</i>
</div>


<div>
Your dataset (from stored result) may not match your actual report schema.<br />
Would you please check if your stored returns <i>MyNumber</i> field?<br />
<br />
You may also check if the profiler does now show your stored execution.<br />
<br />
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.<br />
<br />
<br />
Regards.
</div>


<div>
YES, the .data file was indeed the problem. Thanks.
</div>


<div>
<div class="content wysiwyg-content">
I'm looking for ideas as to why executing a certain stored procedure as a dataset does not work in a SSRS Report. <br />
<br />
By does not work, I mean no data is returned. <br />
<br />
In BIDS by going into the Shared Dataset Properties -&gt; Query -&gt; &nbsp;Query Designer -&gt; ! (which runs the query). The column headers appear and data appear.<br />
<br />
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.<br />
<br />
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?
</div>
</div>


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?

Stored procedure not working in Report

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.

SSRS

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.