473,840 Members | 1,611 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Stored procedure rollback

Hey everyone,

I have a set of stored procedures that call each other to perform a
set of tasks. I have decided to do this for database performance
reasons rather than doing it from a ColdFusion page (and to prevent
timeouts, etc.)

These stored procedures run every night and handle scheduled tasks in
a complex system.

An example of this would be:
sp_ExecuteSched uledTasks @ForDate
(calls) sp_UpdateEmploy eeSalary @EmployeeID, @NewSalary,
@EffectiveDate
(then calls) sp_UpdateEmploy eeCoverage @EmployeeID,
@PlanEnrollment Year
(calls) sp_UpdateEmploy eeBenefitCovera geandCosts @EmployeeID,
@PlanEnrollment Year

This is a quickly drafted example of the complexity of this stored
proc routine.

The problem I have is that each time an error occurs in this stored
proc routine, I would like to save it to a table in the database for
future analysis and rectification the following day. The reason this
does not happen is because of the nested transactions. If the
transaction is going to fail, it also rolls back transactions within
itself (it's children transactions).

I've put together a very basic set of SQL statements below in order to
test and try to find a solution to my problem but have not yet
succeeded.

/*************** ***********/
/* SQL Example Start */
/*************** ***********/

BEGIN TRAN tran_with_error

INSERT INTO tblThisCausesAn Error(error) VALUES('This causes an error')

BEGIN TRAN save_error_mess age
INSERT INTO tblErrorLog(mes sage) VALUES ('This reports an error')

COMMIT TRAN save_error_mess age
/* SAVE TRAN save_error_mess age */

/* At this point an error has occured, rollback the transaction */
ROLLBACK TRAN tran_with_error

/*************** ***********/
/* SQL Example End */
/*************** ***********/

I have tried committing/saving the transaction, I have tried
disallowing delete access to the tblErrorLog table to public and the
SQL user that executes the process, I have even tried a delete trigger
that disallows any deletion but I realize that transactions are
handled differently and that a rollback is not considered a deletion.

Any ideas?

Thanks.

Brent
Jul 20 '05 #1
0 6936

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
11356
by: C Kirby | last post by:
In SQL Server 2000, I've got a rather lengthy stored procedure, which creates a lot of temporary tables as it processes down through a few sets of data. When testing it through Query Analyzer, it runs fine (a bit slow though). But when I try to run it through the ade, it doesn't do anything. It runs through the procedure in milliseconds but doesn't seem to ever actually start it. If I change the calling code in the ade VBA to refer to...
1
2277
by: Ryan | last post by:
We have a suppliers application that runs a stored procedure (one of many). This stored procedure then calls various other ones etc... and the final number of stored procedures run is 11. Now, some of our users have been having problems and deciding for themselves to CTRL+ALT+DEL the application. This is the first thing we will stop. However, we have had some error messages indicating that the previous transaction is blocking them...
2
5130
by: xAvailx | last post by:
I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data store. I've done a fair amount of research on concurrency handling in newsgroups and other resources. Below is what I've come up as a standard for handling concurrency thru stored procedures. I am sharing with everyone so I can get some comments...
9
10303
by: dtwilliams | last post by:
OK, i'm trying to do some error checking on stored procedures and am following the advise in Erland Sommarskog's 'Implementing Error Handling with Stored Procedures' document. Can anybody help with my stored procedures and why it keeps erroring at the '-- Create new Address Detail stage'? The errorCode value that is being return in my web app is 0, so i'm not even sure why it's even raising the error!! Rather than executing the INSERT...
3
8731
by: Mark | last post by:
If a java applicaiton using the type 4 driver calls a DB2 stored procedure, does the stored procedure need to do its own commit when updates are completed? If the stored procedure does a commit or rollback, does that affect the UOW for any SQL that was directly issued by the java program before calling the stored procedure?
1
1509
by: Belee | last post by:
I am developing a c# program VS 2003 and I have created my own stored procedure to insert data into 3 tables The following is the stored procedure, the code and the error message from visual studio ide The following the stored procedure CREATE PROCEDURE dbo.NewControlAndNormalAccoun @AccountNo numeric(18) @AccountName nvarchar(50) @AccountType char(16)
7
9724
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason for this being to stop the user thinking the application has frozen when in fact it is just waiting for a long SP to complete. Another reason for doing it like this is that I also have had a problem in the past where the SP takes longer than the...
9
2473
by: Frawls | last post by:
Hi I Am am having problems with a stored Procedure that i wrote. Basically whats happening is that the Stored procedure Runs fine when i EXECUTE it in SQL Query analyzer. But when i debug through the application in Visual Studio .NET 2003 the application an exception when it executes the query.
3
2548
by: stockton | last post by:
I have written the following Stored Procedure in an attempt to update two tables in the same database reliably but unfortunately it is not too successful. I ocassionaly end up with only the BundlesIssued tables updated and nothing in TicketsIssued. Please make suggestions on how I could make this stored procedure update both tables reliably. ALTER PROCEDURE spIssueScannedTickets @iEventID int, @MemberNum nvarchar(12), ...
1
1193
by: stockton | last post by:
I have written the following Stored Procedure and need input as to what could be wrong with my coding or is there a possibility that not getting the correct results is not in the procedure at all? ALTER PROCEDURE spIssueScannedTickets @iEventID int, @MemberNum nvarchar(12), @BatchSize int, @FirstNumber nvarchar(12), @LastNumber nvarchar(12), @SlotsBalance int, @TableBalance int,
0
9864
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9703
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10609
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10302
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9445
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7024
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5691
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4081
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3140
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.