[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SP exceptional flow control

Posted on 2012-04-10
2
Medium Priority
?
247 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

650 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