Tuesday, March 13, 2012

SSRS: Duplicating the "Dataset1" Error Message

This is a fairly simple blog post (requested by Microsoft) showing how to create the "Dataset1" error message when you are using a report which contains no dataset called "Dataset1". Just as a quick recap the error message is along the lines of;

Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)

Now to make this as simple as possible I'm going to create a dataset using the (Oracle) SQL;

SELECT SYSDATE
FROM DUAL
WHERE TO_NUMBER(:TEST) = 1

This just returns the current date/time when you pass in the value 1 in the "Test" parameter.

If you pass in a non-numeric value then you'll get an error (ORA-01722: invalid number) when Oracle tries to do the conversion to a number.

Save the dataset as a Shared Dataset then create a new report and add the dataset to it (Using the Table Wizard).

Running the report with parameter "q" gives;
DataSet1 Error Message

As you can see the lines in the error message are;

ORA-01722: invalid number
----------------------------
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
----------------------------
The execution failed for the shared data set 'SSRSTestShowingDataset1error'. (rsDataSetExecutionError)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)

I've highlighted the interesting line above. This line is clearly identifying the dataset which caused the error. Something I missed when writing up my previous example (see here) because, amusingly, this time the error coming back from Oracle/ SSRS is "simple" and appears on a single line - my previous error appeared on two lines and thus caused that important identifying line to slip off the bottom of the expanded error dialog (and I never thought to use the scroll bar!).

So while "DataSet1" is still - incorrectly - identified as the source of the error additional details are available.




Post a Comment