Troubleshooting SQL Stored Procedure
I am seeing a weird issue while executing an SQL stored procedure. Below is the format of the stored proc:
CREATE PROC abc
(
@status int=0 output
)
AS
BEGIN TRY
--Does some transactions
END TRY
BEGIN CATCH
SET @status=-1
END CATCH
return @status
I understand I should have added/logged more details like Error Number, Error Message etc in my catch block but I didnt realize such an issue would occur. Currently, the stored procedure in production sometimes returns a -1 status. The strange part is all the transactions in the try block have been successfully committed. I dont understand then why is the procedure getting into the catch block. I tried to manually execute the procedure with same data it completes successfully and returns status=0. I added extra logging in test environment and tested more than 300 times, but was not able to reproduce the issue. In production I am not allowed to make any changes. Hence, I asked our DBA's to enable trace logging, but that didnt help us much in getting any exception details.
I would really appreciate if there is any way I can troubleshoot this issue. Also are there any options for the DBA's to monitor this procedure and capture the exception
链接地址: http://www.djcxy.com/p/71584.html下一篇: 疑难解答SQL存储过程