Rollback Transaction To Previous Stored Procedure?
May 6, 2007
Hi all, I have a program that needs to delete records, then re-insert new records to a table. But I need to rollback the transaction IF the insert is not success (error occured). The delete and insert are in 2 difference stored procedure (which have rollback transaction) that calling from 1 stored procedure. My problem is that if Insert is not successful, but the records already deleted previously. How can we rollback the delete transaction when insert is not successful?
Note: if possible, I don't want to delete the records AFTER the insert is successful, or create a temp table to stored the deleted records
=======================================
create stored procedure combine_sp
as
begin
call delete_sp -- have rollback transaction in the delete_sp
-- what to do if following has error occured, but we already deleted the records above?
call insert_sp -- have rollback transaction in the insert_sp
end
go
=======================================
Thanks a lot.
View 3 Replies
ADVERTISEMENT
Nov 24, 2014
create proc proc1 (@param1 int)
as
begin try
declare @param2 int
begin transaction
exec proc2 @param2
commit transaction
end try
begin catch
if @@trancount > 0
rollback transaction
end catch
i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.
View 3 Replies
View Related
Aug 6, 2006
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
View 5 Replies
View Related
Mar 31, 2008
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
View 1 Replies
View Related
Sep 27, 2007
Hi,
I am using sql2k5. I just wanted to throw an error from stored procedure with some message to C# to rollback my transaction.
Here is how i wnated to do ( in sequence )
C#
=====
Open a connection
Begin the transaction
Execute the command
In the Stored Proc
===========
do multiple operations one by one
if error
stop processing further
Throw the error
C#
========
if exception
rollback the transaction
else
commit the transaction
I have tried using raise error in stored proc but never thrown exception
Can any one let me know how to achieve this scenario??
~Mohan Babu
View 5 Replies
View Related
Mar 5, 2008
Hi everyone,
I don't specify the following statements in SP.
begin transaction
commit transaction
rollback
In sp, there are a lot of insert and update. If i execute SP and stop it in half way. will it rollback all update and insert statement? also, will it rollback if it fails?
cheers
View 1 Replies
View Related
Apr 30, 2015
In general as understand if we have a stored procedure that does operations like inserts or updates, it makes perfect sense to use a rollback operation within a transaction.
So, if something goes wrong and the transaction does not complete, all changes will be reverted and an error description will be thrown for example.
Nevertheless, does using a rollback within a try catch statement, make sense in a read only stored procedure, that practically executes some dynamic sql just to select data from some tables?
I have around 100 Stored procedures, all of them read only. Today a colleague suggested adding try-catch blocks with rollback to all of them. But since they are just selecting data, I don't see a clear benefit of doing so, compared to the hassle of changing such a big number of SP's.
View 9 Replies
View Related
Nov 14, 2006
I'm receiving the below error when trying to implement Execute SQL Task.
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'
I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.
Anyone know of the reason?
View 1 Replies
View Related
Dec 9, 2003
Hi!
I just made a transaction which writes and reads some data from a database as one unit. Now, I would like to know, what happens, if this transaction does not commit, but instead performs a rollback? Should I use a boolean loop which keeps on trying the transaction until it commits or is there an alternative? I would appreciate any suggestions or help!
Thanks!
Timothy
View 1 Replies
View Related
Feb 26, 2007
How do I find out the transaction name to rollback. Thanks
View 1 Replies
View Related
Apr 10, 2008
Hi all
I received the following message in the application log files while the QA team is tesing the application.
06:40:31 [ERROR ] com.inet.tds.ao: The connection is closed: there was an error on the database could not rollback the transaction
I checked at the SQL server logs and found any thing odd.
what does the above error indicates..
Thanks.
View 1 Replies
View Related
Jul 31, 2007
Hi all,
How to work rollback transaction in sql server.
Thanks&Regards
msrs
View 2 Replies
View Related
Jul 20, 2005
If someone could help me with this I will be in debt to you!I am a .net developer who is working on a system that has a sql server2000 backend. On the weekends, I work from home and I have MSDE on mylaptop. This morning I went to update the main SQL server as I addedsome new views .. however I managed to import the data too!!Therefore I have overwritten my local data to the main sql serverdatabase, which has ressulted in loss of loads of work!Can I rollback to the version before I exported my data from MSDE to themain SQL server. I did the export by using enterprise manager and rightclicking on the table on my local msde and then sending it to the realsql server!Please please please help??*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jun 15, 2007
i have a froeach loop,in which i have data flow task,which i need to rollback if any other task fails..
here i need to accept multiple files and i have to give a one second pause for between each file for this i have used a dummy for loop,as i am using this for loop i cant give the for each loop property Transaction Option as required...but i need to rollback if any task fails...i have a screen shot of the for each loop
please give me some ideas on this.
Thanks in advance
View 1 Replies
View Related
Jul 10, 2006
Hi everyone,
In the following T-SQL code snippet,when I attempt to insert more than one record I encounter an error because of the following trigger but I have some doubts about the performation of ROLLBACK TRANSACTION in here. So there is only one transaction occured in the specified table that contain both trigger and the specified table however I supposed that when we call ROLLBACK TRANSACTION, the transaction is aborted. But the code run the Raiserror statement which is located in after the ROLLBACK TRANSACTION statement. So why do this code( Raiserror) is run ? Should not it be terminated because of the ROLLBACK TRANSACTION statement ??
ALTER TRIGGER kimlikNo_Degistir
ON Bilgi FOR INSERT
AS
IF(SELECT COUNT(*) FROM Inserted) > 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('You are not allowed to enter more than one record',12,1)
END
ELSE
BEGIN
UPDATE M SET M.KimlikNo = B.Kimlk + M.KimlikNo FROM Muhasebe as M INNER JOIN Inserted AS B ON M.Ad = B.Ad
END
Thanks
View 8 Replies
View Related
Dec 14, 2007
I made a mistake and ran a delete query that I should not have. There have been no other transactions/modifcations to the database since my delete query.
Can I roll this back from the log file?
Please help as soon as you can!
THANKS!!
View 7 Replies
View Related
Jul 20, 2005
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
View 1 Replies
View Related
Apr 6, 2006
I am moving some code that was created in visual studio 2002 into Visual Web Developer 2005 express edition and am getting a warning on some of my code before compilation.
The warning is "Variable lSqlTransaction is used before it has been assigned a value. A null reference exception could result at runtime". I experienced no problems in vs2002.
The offending line is the rollback command within the exception. If I move it out of the exception it no longer flags the warning, if I move it after the throw statement it now longer flags the warning. Any help appreciated.
Heres the code
Public Shared Sub ExecuteNonQuery(ByVal lSQLCommand As SqlCommand)
Dim lSQLConnection As New SqlConnection(SQLConnString(enmSQLDB.enmSQLDB_TPSDB))
Dim lSQLTransaction As SqlTransaction
Try
'-----------------------------------------------------
'Try and open the database
'-----------------------------------------------------
lSQLConnection.Open()
'-----------------------------------------------------
'Create a transaction
'-----------------------------------------------------
lSQLTransaction = lSQLConnection.BeginTransaction
'-----------------------------------------------------
'Assign the connection and transaction to our object
'-----------------------------------------------------
lSQLCommand.Connection = lSQLConnection
lSQLCommand.Transaction = lSQLTransaction
'-----------------------------------------------------
'Execute the stored proc
'-----------------------------------------------------
lSQLCommand.ExecuteNonQuery()
'-----------------------------------------------------
'Use the transaction to commit the changes
'-----------------------------------------------------
lSQLTransaction.Commit()
Catch ex As Exception
'-----------------------------------------------------
'If any errors have been thrown then roll back without
'commiting....
'-----------------------------------------------------
lSQLTransaction.Rollback()
Throw New Exception("An error has occured whilst trying to update the database. " & _
"Your changes have not been saved.", ex)
Finally
If lSQLConnection.State = ConnectionState.Open Then
lSQLConnection.Close()
End If
End Try
End Sub
View 1 Replies
View Related
Nov 20, 1998
DECLARE @A INT, @B INT,@C INT
SELECT @A=2,@B=3
SELECT @C=@A*@B
SELECT @C
BEGIN TRANSACTION A
SELECT @A
SELECT @B
SELECT @C
END TRANSACTION A
THIS CODE PROCDUE the following errors
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'END'.
Can anyone help me telling me what I am doing wrong ?
thanks
Ali
View 1 Replies
View Related
Jul 23, 2005
In VB 6, using ADO, SQL Server 2000In my VB program I use the connection object (abc)to begin a transaction.abc.BeginTransI then, in my VB App, using ADO, execute multiple stored proceduresinserting records into temp tables ##Meetings,reading them, and then finnaly executing (from my VB appusing ADO) as stored procedure that inserts recordsinto a non-temp table.I then have a abc.CommitTransWhat if my VB program crashes before thecommittrans is executed, I assume that the transactionis rolled back, even though no explicit rollbackis issued, because my VB program has crashed.Thank You for your help,Laurence NuttallProgrammer Analyst IIIUCLA - Division of Continuing Education
View 1 Replies
View Related
Aug 14, 2006
Hi
We are loading data from one extract file in to 4 different database tables. When we are loading if the first table gets loaded without any issues and the second table loading gives any issues, we need to rollback the data loaded in to the first table also.
In the package for achieving this, we are using Sequence container. In the sequency container we are having 4 different tasks for loading data to 4 different tables from the same extract file. We also tried setting the different transaction options given in the properties box of the packages, container and tasks.
Also we tried the properties by setting as mentioned in the Test# 7 and 8 in the following URL
http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp
Could you please explain what properties we need to give.
Thanks
Kumaran
View 8 Replies
View Related
May 7, 2007
Hello ,
I have a Dataflow where I insert data from SQL to Oracle and SQL Connection needs to have RetainSameConnection = TRUE. I have Oracle Services for Microsoft Transaction Server installed and I can rollback the transaction only if SQL Connection has RetainSameConnection = FALSE and TransactionOption = Required. Is there any possibility to rollback the Oracle transaction with having SQL RetainSameConnection = TRUE and TransactionOption = Supported?
Thanks.
View 4 Replies
View Related
Jun 22, 2006
I have a stored procedure that registers a user. It also checks whether an email or username exists before registering them.Does this need to be in a transaction to prevent duplication of email/username? The data is highly sensitive and should not be duplicated at all.I tried writing methods in .net like CheckEmail() and CheckUsername() but that probably wont protect against duplicates if 2 people submit at the same time on a busy server. So the next best thing is a stored procedure but should it be in a transaction or not is what I'm wondering.Thanks again,you guys are great!
View 2 Replies
View Related
Nov 10, 2006
I have a function in which I use a stored procedure and a transaction.I use the following code, but I get the exception e (last try-catch): This SqlTransaction has completed; it is no longer usable.(btw, the SendEmail function currently returns False, so the transaction should ALWAYS roll back) Public Shared Function SaveGBEntry(ByVal myGBEntry As GBEntry, ByVal Language As String) As Boolean Dim bSuccess As Boolean = False Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("spNewGBEntry", MyConnection) Dim fBeginTransCalled As Boolean = False Dim myTrans As SqlTransaction Try MyConnection.Open() myTrans = MyConnection.BeginTransaction() cmd.CommandType = Data.CommandType.StoredProcedure cmd.Transaction = myTrans fBeginTransCalled = True Dim UserCodeOwner As Integer = UserFunctions.GetUserCode(myGBEntry.UserNameOwner) Dim OwnerEmail As String = UserFunctions.GetUserEmail(myGBEntry.UserNameOwner) cmd.Parameters.Add(New SqlParameter("@UserCodeSender", myGBEntry.UserCodeSender)) cmd.Parameters.Add(New SqlParameter("@GBText", myGBEntry.Text)) cmd.Parameters.Add(New SqlParameter("@GBUpdateDate", myGBEntry.UpdateDate)) cmd.Parameters.Add(New SqlParameter("@UserCode", UserCodeOwner)) cmd.ExecuteNonQuery() myTrans.Commit()
If MailFunctions.SendEmail(myGBEntry.UserNameOwner, OwnerEmail, ConfigurationManager.AppSettings("emailINFOname"), ConfigurationManager.AppSettings("emailINFOAddress"), True, "NewGBEntry", Language) Then bSuccess = True fBeginTransCalled = False End If Catch ex As Exception GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", ex.Message) Finally If fBeginTransCalled Then Try myTrans.Rollback() Catch e As System.Exception GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", e.Message) End Try End If MyConnection.Close() End Try Return bSuccess End Function
View 1 Replies
View Related
Oct 17, 2007
hello,
i'm executing a select statement inside the BEGIN TRANSACTION block on a Stored Procedure. my question is, does it lock the table? actually, i'm getting the maximum value of a field and i don't want other users to read until the transaction is done. do i still need to use tablockx to manually lock the table or does the transaction handles the locking?
please help
thanks!
View 2 Replies
View Related
Jul 20, 2005
i have to update two tables from ASP pages with same data but i wantthat both of them should be updated at one time. If either of them isnot updated then my transaction should roll back.I want this thing tobe in a stored procedure. so that i have to write an execute statementonly on the ASP page and pass the parameters.Looking forward for ur replyDEEPAK
View 2 Replies
View Related
Aug 19, 2004
Hi,
i would like to know how to determine a transaction is already rollback so that we dont have to reroll back again and get an exception like below:
This SqlTransaction has completed;
it is no longer usable. at System.Data.SqlClient.SqlTransaction.Rollback()
Please help because when i tried to do this: objTrans.rollback() and i got the above exception.
Regards,
View 3 Replies
View Related
Feb 17, 2006
When I write code for a multiple statements transaction do I need to check 'if @@ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the 'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @ERROR > 0 ?
View 2 Replies
View Related
Jul 10, 2007
Hi everyone,
I am reading about the Rollback transaction but I'm not sure if it's the feature I need.
My application is going to update a few tables, but my programmer said that it will be done using more than 1 transaction.
For example, if I want to create a new employer with all the detailed informations, tables will be filled when the user complete the insertion of a part of data. If he decides to abort the operation, i'd like to delete all the values inserted before with the other queries. Is it possible to create a "savepoint" and roll back all transactions processed from this savepoint?
thank you!
View 6 Replies
View Related
Feb 8, 2006
Hi,I have a problem. When my computer loses connection toserver during transaction it is rolled back automaticlyby server after 2-5 minutes. During the time a can resetmy computer, run my program again and I can seedata from not commited transaction becouse I have toset isolation level to read uncommited.How to force the server immediately rollback transaction?TIAclint
View 1 Replies
View Related
May 28, 2008
Hello,
I have a stored procedure that updates my table with values entered in a datatable in my windows app.
An error occurs 1/2 way through the update process. I assumed that by implementing the rollback transaction command that the inserted lines would not be saved to my db. This is not the case.
I will elaborate a little more on what I need. From my windows app I have a datatable with 100 new rows that need to be written to my db. This I can do. However, a problem crops up should there be an error during the transfer. Let's say I have 100 rows in my datatable in my windows app, and row 57 causes an error, what is happening is that rows 1-56 are committed and 57-100 are not. What I need is for 1-100 to NOT be committed to my DB should there be a snag along the way. I need a code sample as I'm having way too much difficulty with this as is.
The basic code that copies to my db(sans rollback):
BEGIN
SET NOCOUNT ON
INSERT INTO userprofile (uid, uname, ustatus)
VALUES @userid, @username, @userstatus;
END
Thank You.
View 3 Replies
View Related
Oct 15, 2007
We have a locked table and all I need is a way to show all open trans, so I can roll teh offender back
View 1 Replies
View Related
Aug 21, 2006
I have a stored procedure I want to use in a transaction...I have no idea where the problem is, since I dont receive an error...but tblMessages and tblUsersAndMessages are not updated..(hence something is wrong ;))...it might be the sp or just my code...anyway..here's all the data:STORED PROCEDUREset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
ALTER PROCEDURE [dbo].[spNewMessage]@MessageID int,@UserIDSender uniqueidentifier,@MessageTitle nvarchar(50),@MessageContent text,@MessageType int,@UserID uniqueidentifier
ASBegin Set NoCount on DECLARE @WhateverID INT
INSERT INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType)VALUES (@UserIDSender,@MessageTitle,@MessageContent,@MessageType)
SET @WhateverID=SCOPE_IDENTITY()
INSERT INTO tblUsersAndMessages(MessageID,UserID)VALUES (@WhateverID,@UserID)
End
TABLE DEFINITIONtblUsersAndMessages allow nullsMessageID int falseUserID uniqueidentifier falseNew bit false *default set to ((1))
tblMessages allow nullsMessageID int false *PKUserIDSender uniqueidentifier falseMessageTitle nvarchar(50) trueMessageContent text trueSentDateTime datetime false * default set to (getdate())MessageType int false
THE CODE Public Shared Function SendMessage(ByVal SenderName As String, ByVal To As String), ByVal MessageTitle As String, ByVal MessageContent As String, ByVal MessageType As String) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("spNewMessage", MyConnection) Dim fBeginTransCalled As Boolean = False Dim myTrans As SqlTransaction Try MyConnection.Open() myTrans = MyConnection.BeginTransaction() cmd.CommandType = Data.CommandType.StoredProcedure cmd.Transaction = myTrans fBeginTransCalled = True
Dim UserIDSender As Guid = UserFunctions.GetUserID(SenderName) Dim UserIDReceiver As Guid = UserFunctions.GetUserID(To)
'create message cmd.Parameters.Add(New SqlParameter("@UserIDSender", UserIDSender)) cmd.Parameters.Add(New SqlParameter("@MessageTitle", MessageTitle)) cmd.Parameters.Add(New SqlParameter("@MessageContent", MessageContent)) cmd.Parameters.Add(New SqlParameter("@MessageType", CInt(MessageType))) 'userid receiver cmd.Parameters.Add(New SqlParameter("@UserID", UserIDReceiver))
cmd.ExecuteNonQuery() myTrans.Commit() bSuccess = True fBeginTransCalled = False Catch ex As Exception Finally If fBeginTransCalled Then Try myTrans.Rollback() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
View 3 Replies
View Related