March 26, 2020 at 11:37 am
The catch section hides the error message. If you'll add throw to it, it will send you the full error message that includes the place that the error occurred. Check the small demo code and play with it.
CREATE OR ALTER PROC MyInnerDemoProc (@i int, @j int) as
BEGIN TRY;
SELECT @i / @j;
END TRY
BEGIN CATCH;
THROW;
END CATCH;
GO
CREATE OR ALTER PROCEDURE MyOuterDemoProc (@i INT, @j INT)
AS
BEGIN TRY;
EXEC MyInnerDemoProc @i, @j
END TRY
BEGIN CATCH;
SELECT 'oh boy, something went wrong';
--remove the remark so the throw statement will work
--THROW;
END CATCH;
GO
exec MyOuterDemoProc 1,0
March 26, 2020 at 1:06 pm
Thanks, I have tried using Throw as you suggested buts its still showing the same error comment without mentioning the exact stored procedure which is giving the error
March 26, 2020 at 1:55 pm
You need to write throw also in the inner procedures. If they have a catch block without throw in it, you won't see the error. Also I don't know how you run the procedure. If you run it in an application, then depending the error handling in the application you'll might get the technical information (in a log or in the GUI) and might not, but if you'll run it in SSMS then you should see the location of the error.
March 26, 2020 at 4:11 pm
Another way, probably easier, is to set up an extended events session collecting the error_reported event, filtered to that error number, and add the tsql frame or tsql stack actions.
Either of those will give you the sql handle, line, start offset, and end offset for the innermost call (with tsql stack giving you all the parent calls as well).
Cheers!
March 31, 2020 at 8:58 am
Run the stored procs individually ?
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply