Solved

How to trace procedure errors efficiently in sql2008

Posted on 2011-09-19
3
238 Views
Last Modified: 2012-05-12
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
Comment
Question by:motioneye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 5

Expert Comment

by:25112
ID: 36561120
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
 
LVL 5

Accepted Solution

by:
25112 earned 500 total points
ID: 36561131
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
 
LVL 5

Assisted Solution

by:25112
25112 earned 500 total points
ID: 36561137
another option is to manually run each segment of the proc by passing the parameters.. this will be manual troubleshooting..
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question