CODEWITHSUNDEEP
sql-server-2008stored-procedures
kinkajou
kinkajou

Reputation: 3728

Tracking Error In Stored Procedure SQL Server

How to track error in stored procedure in sqlserver? I have stored procedure and it is added to job throush sqlserver agent. I get error in job execution and I get mail but I am not able to track the error down as the error occurs intermittently or once 3-4 days.

Upvotes: 0

Views: 769

Answers (1)

Paul Williams
Paul Williams

Reputation: 17020

If your job encounters an error or raises an error with RAISERROR, the job will be marked as failed in the SQL Server Agent job history. This history is visible by right-clicking a SQL Agent job and choosing View History.

You can add some BEGIN TRY/END TRY and BEGIN CATCH/END CATCH statements around your job's code. This will prevent the job from failing unless you really want it to do so.

Your job can use the PRINT statement to output information as it runs. Some of the output will be visible in the job history. You could use the PRINT to include some trace information as it runs and save error information captured from your TRY/CATCH statements.

We have several SQL Agent jobs that run and PRINT information to the job history. The jobs are careful to TRY/CATCH everything possible. They only RAISERROR if there is a serious problem that the DBA needs to know about.

Our developers occasionally refer to the Agent history to view the trace output. We use this output to help diagnose problems in our software.

Upvotes: 1

Related Questions