I have written a store procedure which inserts data into two tables. What I want do is to rollback transaction if the second insert fails. Below is a code.
If I start a transaction using the following approach ... using (SqlTransaction trans = destConn.BeginTransaction()) { ...do some transfers using SqlBulkCopy }...will an automatic rollback occur in case of unhandled errors inside the scope of the using statement?
Recently I created a DDL Server-scope trigger using the following:
create trigger stop_alter_database on all server for ALTER_DATABASE as rollback; print 'database change stopped by stop_alter_database'; go
Then I ran the following script:
alter database [test] modify file (name=test', maxsize=2028mb);
The result was:
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
database change stopped by stop_alter_database
The problem is that when I checked the max size of the data file it had changed. So, the statement was never rolled back. Is there something I'm missing because I can't find any documentation or articles that state the inability to rollback alter database statements. Whats going on?
Hello: I am implimenting the creation of sequence numbers .I use an insert proc on a table that generates the numbers using an identity field: procedure usp_createidentity
begin transaction
insert into [tblOrderNumber] with default values
rollback ' done so no records in this table
select @OrderNumber = scope_identity()
I call this from another proc that inserts values into my order table:
procedure usp_Insert @OrderNumber int as
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
EXEC usp_GetNewOrderNumber @OrderNumber = @OrderNumber output INSERT INTO [dbo].[tblOrder] ([OrderNumber]) values (@orderNumber) ' inserts value from other stored proc
COMMIT TRANSACTION
END TRY
BEGIN CATCH
if (XACT_STATE() = -1)
ROLLBACK TRANSACTION
else
if (XACT_STATE() = 1)
COMMIT TRANSACTION END CATCH
Here is the problem. When I run usp_Insert I get the following: Error 266 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
This refers to the usp_GetNewOrderNumber that is called inside the other proc as shown above.
The problem does not happen if I put each statement in usp_Insert in its own try/catch. transaction statements.
Maybe it has something to do with the rollback call in the usp_getneworder.
What do I need to do to get rid of this. problem and still run these within one try/catch trans statement set.
I'm launching the aforementioned statement from one MASTER session windows and I get this message, I am stuck, I though ROLLBACK INMEDIATE go throught any already session open.
Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'GFSYSTEM' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
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
Hello experts, I made a huge mistake on a production server, ran an update without the WHERE clause. UPDATE myTableSET Field1 = 'SOmetext',Field2 = 'sometext2'Is there a way to rollback? I read something a while ago about transaction logs.SOS!Thanks
HI, Suppose I execute any transaction say inserted some rows. I want to delete my previous transaction using rollback. What is the proper syntax for that. In Query Window: Insert ---------- executed rollback. it was asking for name of the transaction. How to give that. Help me out Thanks Bye
This is my stored procedure. Problem is that a rollback does not happen when there is an error ? How can this be, am starting and ending the transaction?
I have rollback on one of the users. I tried killing her process through Enterprise Manager and Query Analyzer, but I still can see her process on the screen. However, her login is grayed out after I execute the kill query.
I have encountered this problem several times, and the only way I can get clear the rollback is by rebooting the server.
Does anyone have a sugeestion on how to clear the rollback without rebooting.
Hi All A question on DTS ROLLBACK CAPABILITY. Is it possible to setup a DTS pkg to roll back the loaded files if a step fails? If a step fail records inserted by Data Pump to a table should be removed. Is this possible? If so how can we achieve this?
is it possible to roll back a transaction here? i heard that it will be possible only if we wrote the code only in certain block.please tell me the block.
The code inserts into table TABLES. TABLES has a primary key. If the insert statement has no error it should insert 'ROWS INSERTED' to table CONTROL and update table DATETRACKING else insert 'ROWS NOT INSERTED' to table CONTROL and do not update table DATETRACKING. 1- I will like to improve my code if it is possible. 2- Can I use a rollback?
DECLARE @ROWCOUNT INT DECLARE @ERROR INT DECLARE @LASTDT DATETIME
SET @LASTDT =GETDATE() BEGIN TRAN insert tables select 4
SELECT @ROWCOUNT = @@ROWCOUNT, @ERROR = @@ERROR IF (@ERROR <> 0) BEGIN INSERT CONTROL SELECT 'ROWS NOT INSERTED', @ROWCOUNT END ELSE BEGIN INSERT CONTROL SELECT 'ROWS INSERTED', @ROWCOUNT END COMMIT TRAN
IF (@ROWCOUNT > 0) BEGIN UPDATE DATETRACKING SET LASTDATEDL = GETDATE() END
I have SQL server 2000 with SP4, 1.Some of the reason,I want roll back SP4 and I want replace SP3. 2.Is it true SP4 slow down the Quey performance compare with SP3.
My rollback does not work In my SP I want any of cmdS,cmdS2,cmdS3,cmdS4 produces error Rollback must be executed for all of cmdS,cmdS2,cmdS3,cmdS4 . I tested for error producing situation but no rollbak occured. How can I solve this problem. Thanks. Below is my SP . .......... .......... .......... begin transaction .......... ..........If Len(ltrim(rtrim(@cmdS)))>0 execute(@cmdS) If Len(ltrim(rtrim(@cmdS2)))>0 execute(@cmdS2) If Len(ltrim(rtrim(@cmdS3)))>0 execute(@cmdS3) If Len(ltrim(rtrim(@cmdS4)))>0 execute(@cmdS4) If Len(ltrim(rtrim(@cmdS)))>0 or Len(ltrim(rtrim(@cmdS2)))>0 or Len(ltrim(rtrim(@cmdS3)))>0 or Len(ltrim(rtrim(@cmdS4)))>0 Beginif @@ERROR <>0 begin rollback transaction set @Hata = 'Error !' end Else Beginset @Hata = 'Sucessfully executed :)' End End commit transaction RETURN
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!
I am just starting to falmiliarize myself with SQL transactions... I just created an SQL transaction... The first statement gets a value, if the value equals "" then the second statement executes... So if the value <> "" Then the second statement wont execute... What would happen in this scenario if the .Rollback is triggered? Heres my code:
Try conSqlConnect.Open() objTransaction = conSqlConnect.BeginTransaction cmdSelect.Transaction = objTransaction cmdInsert.Transaction = objTransaction dtrdatareader = cmdSelect.ExecuteReader() While dtrdatareader.Read() varCheckNumber1 = dtrdatareader("Status") End While dtrdatareader.Close() If varCheckNumber1 = "" Then cmdInsert.ExecuteNonQuery() End If objTransaction.Commit Catch objTransaction.RollBack Return "00" Finally If conSQLConnect.State = ConnectionState.Open Then conSqlConnect.Close() End If End Try
I've some problem in SQL Server 7.0 and hope some one out there will help me with it. The problem is as follows:
When I view a proces in the process info screen, I see one process says "Rollback" in the status window and the spid is greyed out like a system process. In the command window it says, Select and wait type is "Exchange". This is taking too much cpu cycles and causing much of the server to perform very slow.
I tried to kill this session from the ISQL and Enterprise manager, But to still see this process running on with out getting killed. Also this process keeps coming.
Please advice me how to get rid of this and why is it ocurring again and again.
I am getting an automatic rollback in a transaction that fails to insert a row, via a trigger, due to a duplicate key. The transaction is automatically rolled back and unless the error correctly handled a 'partial update' occurs. Why in the example below is the transaction automatically rolled back (statements 1 to 3) and is there any way of knowing what errors generate an auto rollback. For example if the duplicate key error occurs on the insert into table B below, and not the triggered update, then no auto rollback occurs.
Begin Tran -- 1 insert into A values ('A1') -- 2 updates OK insert into B values ('B1') -- 3 updates OK -- triggers insert into B_Hist -- 4 Fails with Duplicate Key error insert into C values ('C1') -- 5 updates OK Commit Tran -- 6 3902 error No corresponding Begin
I have this weird situation on one of the production database. Somebody ran a sql script couple days ago and must have hit cancelled. Somehow this script is still rolling back. The script is actually very simple statement..should not take more than couple miliseconds to run. Is there any way to see whether rollback is really in process or this process has simply hung? is there any other way to get rid of this rollback without stopping and restarting sql server services? Can it cause problems in getting sql server up again if I restart sql server services?
This may sound a little silly, but does anyone have any words of wisdom on how to optimize a server/database for minimim rollback? We have some multimillion row tables we were trying to do updates against, and after several days they increased the size of the transaction log to the point they filled up the drive the database files/logs were on. We've now been running a rollback for about five days. I'd like to make sure this doesn't happen again.
In MOC (SQL 7.0 Implemetation), it says there will be rollback, but in my humbel opinion, there should be nothing happened.
I think there was no dirty writing in this case. No data pages written, no log pages written to disk. Am I right? Then, there might be nothing happened instead of ROLLBACK.
If I am wrong, would you please let me know what is wrong in my thougt?
If you have a begin tran in a sp that calls another sp and that called sp fails. can you execute a roll back from the called sp, or should it return and error which will roll back the begin tran in the original sp
I have been informed that most (if not some) RDBMS are faced with problems with the SQL ROLLBACK (also known as undo) command. I have been searching online, but have not found any useful relevant sources on what this issues are.
I am wondering if their are any experienced MS SQL Server (or any other RDBMS) users who might be able to provide feedback on this matter.
Don't ask, but I iadvertantly managed to delete 57,000+ records in the EM console and I need to roll back the database a few minutes to recover said information. I do not care about transactions that have occured since due to shutting the system down immediately upon realizing what I did. I have however never had the need to roll back the database before, so I have experience doing so. Any help is desperately needed and greatly appreciated!!!
