?
Solved

SP exceptional flow control

Posted on 2012-04-10
2
Medium Priority
?
246 Views
Last Modified: 2012-04-12
Dear all DB expertist,

right now we have a system workflow monitoring tools called control-M, all our housekeeping job is running in a store procedure and we can see the the exeception flow control is not very good.

How can we improve the execeptional flow control the return as much information as possible when the SP failed to the monitoring system ? the Control-M.

All solution is welcome and I will try it one by one.

DBA100.
0
Comment
Question by:marrowyung
[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
2 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 1000 total points
ID: 37830096
I'm a big fan of TRY/CATCH blocks in newer versions of SQL Server. You can nest them if you want, and they provide excellent control over the flow of your T-SQL code when there's the possibility of an error.

Once you can control the flow sufficiently, you can use the RAISERROR keyword (note the mis-spelling - it's on purpose) to throw information of interest back to the control process that's calling the SP in the first place. Those details and the associated messages or codes can be logged and reported on at the application level.

Check out the link above for some examples of both RAISERROR and TRY/CATCH blocks to capture errors and report them.
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 1000 total points
ID: 37830227
please tell more about about this control-m and the execeptional flow control  that you have now:
check:

SQL Server 2008 error handling best practice
http://blogs.msdn.com/b/anthonybloesch/archive/2009/03/10/sql-server-error-handling-best-practice.aspx

http://www.sqlteam.com/article/handling-sql-server-errors
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

777 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