CODEWITHSUNDEEP
c#sqlvb.netreporting-servicesreport
Bernard Walters
Bernard Walters

Reputation: 391

Get SSRS report to show SQLexception

I have a problem where i want to show specific Exception Errors inside my reports(making life easier for the guy who has to fix things if they are borken).

I want my SQLexception

Not allowed to embed images(SQL) enter image description here

and my SSRS exception

Not allowed to embed images(SSRS) enter image description here

to both show the SQL exception message.

Does anybody have a clue how i can fix this?

so far I have only hit a wall

Upvotes: 0

Views: 305

Answers (1)

StevenWhite
StevenWhite

Reputation: 6034

First, you need to wrap the SQL in a try ... catch block (see here). This gives you control over what happens when the SQL throws an error and allows you to see the Error Message (see here).

In order for you to be able to display the error in SSRS, you have to still return a SELECT statement with the same columns you would have normally. This is because SSRS needs to know the column names ahead of time. So add a column to your original dataset like so:

SELECT Col1, Col2, ... , NULL as ERROR_MESSAGE

And then add this in your catch block:

SELECT NULL AS Col1, NULL as Col2, ... , ERROR_MESSAGE() as ERROR_MESSAGE

Then, in the report, you can check the Max(Fields!.ERROR_MESSAGE.Value) to determine if you should display the error message in that column or regular data.

Upvotes: 1

Related Questions