• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

How to trace procedure errors efficiently in sql2008

I have a stired procedure which have written very long and its also use to execute another procedures within a procedure. now this procedure doesn't seems work fine and I need to trace at which execution the procedure failed to run.
This procedure define to be run on sql jobs, but  nothing were written when I try  to enable the error log in sql jobs.

any best way to find where out the procedure failed within the job ?
0
motioneye
Asked:
motioneye
  • 3
2 Solutions
 
25112Commented:
you should put try/catch in your code to catch the error.. here is an example..
BEGIN TRY
    SELECT 2+'A'
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() as ErrorState,ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,ERROR_MESSAGE() as ErrorMessage;
END CATCH

Open in new window

0
 
25112Commented:
ERROR_MESSAGE() is the key which will tell you what the problem is..

if you try your code in several segments of the proc, it may help you to isolate the area where it is failing.
0
 
25112Commented:
another option is to manually run each segment of the proc by passing the parameters.. this will be manual troubleshooting..
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now