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

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?
Ed_SnowdenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
Ed_SnowdenAuthor Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Ed_SnowdenAuthor Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ed_SnowdenAuthor Commented:
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
Ed_SnowdenAuthor Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Object to the close request, as 'it worked' is not an answer.  
Please explain in detail how you resolved this problem.
0
Ed_SnowdenAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.