This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(12.8, 72%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHC%3C/text%3E%3C/svg%3E)
I have a SQLServer Stored Procedure,,that is called by a client application (Java/Hibernate/JDBC)
I catch errors with TRY/CATCH or @@error variable. That´s fine
But, what does the Engine do in the case of an error ?
It seems that in some way, every error is reported to client application
even though you have caught it
Then, the client application triggers an Exception
because of errors that have already been treated -- in SQL Server
and shouldn´t have been sent to the client
Does Engine always report to client all errors?
It depends on the driver?
Is it possible to stop Engine to send caught/treated messages ?
I didn´t found any explanation on internet....
I´ll appreciate your help
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Hi @Hernan C ,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Best regards,
Seeya
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
try/catch/@@error cannot catch all errors. Most likely your error is a severity >10, which terminates at the point of the error and does not continue the command.
maybe I don't understand your answer. I do catch the errors. And that's fine from the "business perspective". The problem is not that I can't catch some type of error. The question is aimed to know what is happening behind the scenes. Despite I'm catching the errors, the error messages are sent to the client. I don't know why. Maybe it is normal for the driver or the engine. I really don't know. It seems that Java triggers an exception because the stack of errors is not empty. Sure,
it is related to Java, but why messages are sent back?. That would be the question
Are you sure your catch block is running?
Please post the code from your catch block. What kind of error are you seeing?
Yes, Tom. When i run the SP from the SSMS, i get no errors. Next week I will be able to gather some information from the Java developer, and then i will post it
If SQL does not generate an error, then the problem is on the Java side. My bet is you are getting something like "stored proc not found". Which is not "catchable" in SQL Server.
but, if there were something not catchable, I'd get an error (in the results tab) when I execute it from the SSMS. I'll add more information soon
I am saying Java is passing something invalid. The error never gets to your proc.
For example, if Java is trying to run "EXEC xxxxxx". Java will generate an error, because proc "xxxxxx" does not exist.
Without the Java error message, it is impossible to guess the problem.
First of all, if there is an unexpected error in your SQL code, it should be trapped by TRY-CATCH, and then it should be re-raised by the CATCH block, so it should go to the client.
Occasionally, you may perform an action, fully aware of that it can go wrong and for that case you have a plan B. In this case you do not want the error to go the client, but that is not a very common thing.
You should be aware of that error handling in SQL Server is a very messy topic, and there are errors you cannot catch, or you cannot catch in the scope they occur.
But if you don't see any exception in SSMS, you should not see any in the application. So we really need to have a more concrete example of what you are talking about.
Yes, I agree with the last paragraph. That's the point. I'll add details next monday.
Meanwhile, I can tell you the following:
I don't know what happens behind the scenes (in genreal) when the Engine find an error. I wonder if the Engine reports it directly to the driver, at the same time it gives the control back to the "corresponding next line", that is, (1) the first line in case of the CATCH block, or (2) the next line after the failing statement (where you usually ask for @@ERROR)
I'm speculating that the driver is aware of each error, and depending on the driver, the error is stored or not in some buffer or place, and maybe it reaches the client. I'm not reported of any error when I running the SP from SSMS tab --that is, a connection that use a native driver or similar-- but an exception is triggered when I execute it from Java (JDBC driver)
That's what I referred to when I said "behind the scenes". Could my speculation be possible?
No, your speculation is out in the blue. The engine communicates with the driver over the TDS protocol, that's the sole channel. So if an error is caught and not re-raised (as it should be), the client does not know about it.
Or, well, if the client looks at the error code, it will know that something went wrong:
CREATE PROCEDURE error_sp AS
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
END CATCH
go
DECLARE @ret int
EXEC @ret = error_sp
SELECT @ret
go
DROP PROCEDURE error_sp
The return value is -6, and any non-zero value indicates that an error occurred.
But all this speculation, because I have no idea of what your code looks like, not in the client, and not in the database. To be honest, I don't even know what your real problem is.
Since the same proc works from SSMS, it may be the client code is using a server API cursor (which SSMS does not do) and the options are incompatible with the proc. That error cannot be caught on the server side. Without debugging the client code, you could run a server-side trace to troubleshoot.
Hi @Hernan C ,
I suggest you read this article.
https://www.mssqltips.com/sqlservertip/6478/error-handling-in-sql-server-with-try-catch/
In addition, please post your try catch code, it will be very helpful to solve the problem.
Best regards,
Seeya
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.