Empty report when using VS2008 report wizard and stored procedure

Question

I'm using VS2008, writing a C# .NET app. I have an SQL Server 2005 database server with a database which contains several stored procedures. I want to show the stored procedure data in a report.

So, as a test I created a brand new VS2008 C# project based on the "Reporting -> Reports Application" project type.

I went through the wizard, connecting to the database and creating the dataset from the stored procedure I wanted to use. I told the report wizard to create a tabular report and didn't bother grouping the columns.

When I ran the application, the report viewer appeared with "Report 1" in the top, but nothing else. There was only one page.

If I create a whole new application, following the exact same method, but this time choose either a View or a Table from my database, I get a multi-page report with data in it, as you'd expect.

So why isn't the reporting system working with stored procedures, but working fine with tables or views? The stored procedure works if I execute it in SQL Management Studio, and it needs no parameters.

Answer 1

I remember having an issue a while back using VS 2003 (Crystal Reports) and SQL Server 2000, where the report designer was unable to determine the fields returned by the stored procedure, and as a result generated a blank report. In this case the reason was that the stored procedure selected its results from a table variable defined in the SP.

Have you checked if this is the case?

I might be over-complicating things already, maybe the reason is much more straight-forward, but this was the scenario that immediately came to mind when I saw your post.

The solution to my problem back then was to create a sort of 'wrapper' SP instead, and perform all calculations in a table-valued function. The SP then selected data from this function (SELECT * FROM dbo.MyFunc(@param)), which enabled the report designer to retrieve the list of fields. I needed the SP since the designer didn't support parameterized SELECT-queries. This workaround was implemented in a small application though, and I'm not sure how or if this approach affects performance on a larger scale.

Answer 2

I fixed this by not using the wizard.

1. Create a new WinForms app
2. Manually add a DataSet by right-clicking the solution in the Solution Explorer
3. Manually add a TableAdapter by double-clicking on the DataSet xsd file in the Solution Explorer, then right-clicking the blank area and choosing "Add-Table Adapter..."
4. Connect to the DB
5. Choose "Use existing stored procedures"
6. Assign the required stored procedure and complete the wizard
7. Manually add a report to the solution
8. Double-click it, then go to "Data->Show Data Sources"
9. Drag the required fields onto the report
10. Put a Microsoft.Reporting.WinForms.ReportViewer onto the main form
11. Use the pop-out smart tasks triangle to associate the required report

You can use the report wizard available under the smart tasks thing on the reportviewer control instead of steps 8 & 9 if you want to create tabular reports, etc.