How to display error message generated from raiserror() with SQL Server

Question

I am interacting with SQL Server 2005 from a jsp via JDBC (this is an assignment not a real project) and I have created a trigger in the database I am using. If a certain condition is not met when this trigger executes I raise an error via raiserror(). I would like this error to be displayed on the actual page that calls the SQL Server query via JDBC, but currently I am just getting the following default message when I print out the result of SQLException.getMessage():

The transaction ended in the trigger. The batch has been aborted.

Does anyone know how to extract the text I actually passed to raiserror in my trigger code? I have already tried:

• SQLException.getState()
• SQLException.getNextException()
• SQLException.getCause()
• SQLException.toString()

Cid

Answer 1

I figured it out. The problem was related to the severity I was choosing when calling raiserror(). I was passing in 10 for this argument as I had seen an example that put 10 there, but in order for the error text to get passed to the SQLException the severity must be 11 or higher. After changing the severity it all works fine. If anyone else has a similar problem I would recommend trying this. Also google JDBC error handling for useful documentation (I would submit a link, but new users aren't allowed).

Cid

Answer 2

You need to iterate all exceptions returned with getNextException()

Yaws

Thanks for the response. I tried that, but unfortunately it appears that there are no other exceptions as when I call getNextException() it returns null. – Cid

Do you have any BEGIN TRY/BEGIN CATCH in your SQL or in your procedures? – Yaws

I have a try / catch block in my jsp where I catch an SQLException. I don't have a BEGIN TRY or BEGIN CATCH or anything like that in my SQL. – Cid