I have a report that calls 5 stored procedures, 3 of which are used for the display of data.
The stored procedures work fine if called from Query Analyzer.
However in Preview Mode in Visual Studio 2008 and on the Web Page it would throw an error on the last procedure - GenerateInstallReport. This report
- takes 4 parameters (customer id, from, to and user id),
- calls 1 internal store procedure,
- returns 12 columns (one of which is a large (3072 characters) varchar field),
- takes approximately 1 second to run and
- returns (for the testing time period) 9 rows.
I had the SysAdmin enable remote errors on and the error became a little more specific with:
An error occurred during local report processing.
A error has occurred during report processing.
Query exectuion failed for dataset 'GenerateInstallReport'.
A severe error occurred on the current command. The results, if any, should be discarded.
If I am to delete this procedure from the dataset the report completes fine (the meat of the report takes about 2 mins to complete).
Reducing the number of parameters returned in the procedure seems to point the very last field returned from the procedure which is a datetime. If this is commented out and the corresponding field from preview then it works. There is nothing special about this field apart from they are all null for that time period. However I have other reports that have null datetime fields but they aren't the last field in the report. Moving the field from last doesn't have an effect. Just simply returning the field seems to the cause the failure.
It is running on SQL Server 2008 Sp1 with cumulative update 4 installed. All 5 procedures have their permissions set to public. The called procedure is also set to public.