?
Solved

Getting SSRS to display just the specified error message when the using raiserror function

Posted on 2014-08-06
9
Medium Priority
?
1,279 Views
Last Modified: 2015-01-14
I'm using the fuction raiserror in a stored procedure to produce an error in a reporting services report when certain criteria are met. My problem is that reporting services does not just display the error text that I specify. It adds in the following text before my error message

An error occurred during local processing. An error has occurred during report processing. Query exclution failed for the dataset 'Dataset1'.

When this report is run via the client application it's even worse and I get the following;

An error has occurred during report processing. -->
Microsoft. ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for the dataset 'Dataset1'. -->
System.Data.sqlClient.SqlException

We think the end users won't even read the proper error message that we specify because there is so much other meaningless texp before it. How do I get RS to just display my error text?
0
Comment
Question by:Ed_Snowden
  • 5
  • 4
9 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40243919
>My problem is that reporting services does not just display the error text that I specify
I don't think this is possible, as SSRS like SSIS requires a 'contract' between it and it's data source, and it isn't going to handle switching between the expected data set and an error message.

>We think the end users won't even read the proper error message
Correct.  A vastly better idea would be to create a 'log' table to store error messages, and in your SP's if it throws an error have it do an INSERT into that table.  The report will still crash, but at least you'll have a record of what the error is.
0
 

Author Comment

by:Ed_Snowden
ID: 40243967
Thanks Jim,
In the client application, when the report runs then it is rendered as a pdf document and is saved onto a sharepoint directory.
It’s important that the report does not run when the conditions are not correct, because we don’t want reports to run and be stored when the information in them is invalid.
It’s also very important at that time for the user to know that the report has failed and the reasons that it failed. Having a record in a log table is not going to work for that purpose.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40244036
>In the client application, when the report runs
Define 'client application', as that's where you'd have to handle not sending the report.  SSRS and SQL won't be able to handle this by itslef.

>It’s also very important at that time for the user to know that the report has failed and the reasons that it failed. Having a record in a log table is not going to work for that purpose.
Okay, I'll bite.  What would work?  Smoke signals, morse code, interpretive dance...
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

Author Comment

by:Ed_Snowden
ID: 40244060
Hi,

The Client application is a bespoke finance system that uses a sql database.
What would work is if we can get the error message that SSRS puts out to be a bit more user friendly and to just include the raiserror text that we have specified.
Thanks
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40244074
>The Client application is a bespoke finance system that uses a sql database.
Okay, then that will have to handle the 'Don't send any report that throws an error' requirement.  Never heard of it, so I can't help you there.

>What would work is if
The only thing that comes to mind is to log the error to another table, then..

1.

Handle this in the SP so that it returns an empty set instead of the error.

2.

In the SP INSERT into the log table do all the cosmetic formatting.  Realize that there will be limitations, as Microsoft isn't known for abundantly user-friendly error messages.

3.

Create a new dataset that runs SQL against the log table, such that if there was an error it displays it, and if there is not an error it displays nothing.
Kind of a house of cards, but I'm not seeing any other solution that will display anything graceful.
0
 

Author Comment

by:Ed_Snowden
ID: 40244098
Ok Thanks Jim,
Some good work-around's  there I think. We'll check if anyone else has some ideas on a simpler solution and then I'll accept your solution :)
0
 

Author Comment

by:Ed_Snowden
ID: 40543156
I've requested that this question be closed as follows:

Accepted answer: 0 points for Ed_Snowden's comment #a40244098

for the following reason:

It worked
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40543157
Object to the close request, as 'it worked' is not an answer.  
Please explain in detail how you resolved this problem.
0
 

Author Comment

by:Ed_Snowden
ID: 40543162
Sorry Jim, i had to put something in to close the problem and accept your solution. I've had so many things going on since that I've forgotten what the detail of the fix was.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question