Transact SQL :: Commit And Rollback Transaction Based Upon Results Of A Calculation
Oct 5, 2015
In t-sql 2012, I have the following sql that I would like the following to occur:
1. commit or rollback a transaction based upon the results of a calculation listed below,
2. I would like to have a message appear if the commit was successful or the rollback needed to occur. I basically want a way to be able to tell from messages if a rollback occurred or a commit happened.
DECLARE @TransactionName varchar(20) = 'Transaction1';
@STARTLOCKERCNT INT = 0, @LOCKDIFCNT INT = 0, @ENDLOCKERCNT INT = 0
DECLARE @lockmap TABLE (lockID int NOT NULL PRIMARY KEY,
schoolID int NOT NULL,
UNIQUE(schoolID,lockID)
)
[Code] ....
Thus can you modify the sql I just listed above so that I meet the goals that I just listed above?
I have a transaction that calls one other sproc and also executes another set of queries, but for some reason I'm getting error 266: "Msg 266, Level 16, State 2, Procedure AddUserHaveTag, Line 26. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1."There is NO transaction in the sproc AddTag. It is also included below.Here is the sproc with the transaction:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON 3 go4 5 ALTER PROCEDURE [dbo].[AddUserHaveTag] 6 (7 @UserHaveID int,8 @Tag varchar(24),9 @UserHaveTagExists bit OUTPUT 10 )11 AS12 SET NOCOUNT OFF13 DECLARE @ErrorCode int14 DECLARE @TagID int15 DECLARE @TagExists bit16 17 BEGIN TRAN 18 19 -- Call proc to add tag to Tags table 20 EXEC AddTag @Tag, @TagID OUTPUT, @TagExists OUTPUT 21 22 -- Check for errors 23 IF @ErrorCode <> 0 GOTO ERROR24 25 -- Check for existing record, otherwise insert 26 IF EXISTS (SELECT 1 FROM UserHaveTags WHERE UserHaveID = @UserHaveID AND TagID = @TagID)27 BEGIN28 SET @UserHaveTagExists = 129 RETURN 030 END31 ELSE32 BEGIN33 INSERT INTO UserHaveTags (UserHaveID, TagID) VALUES (@UserHaveID, @TagID)34 SET @UserHaveTagExists = 035 END 36 37 -- Check for errors 38 IF @ErrorCode <> 0 GOTO ERROR39 40 COMMIT TRAN 41 42 ERROR:43 IF (@ErrorCode <> 0)44 BEGIN45 PRINT 'Unexpected error occurred!' 46 ROLLBACK TRAN47 END Here is the AddTag sproc:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON 3 go4 5 ALTER PROCEDURE [dbo].[AddTag] 6 (7 @Tag varchar(24),8 @TagID int OUTPUT,9 @TagExists bit OUTPUT 10 )11 AS12 SET NOCOUNT OFF13 14 IF EXISTS (SELECT 1 FROM Tags WHERE Tag = @Tag)15 BEGIN16 SELECT @TagID = TagID FROM Tags WHERE Tag = @Tag17 SET @TagExists = 118 RETURN 019 END20 ELSE21 BEGIN22 INSERT INTO Tags (Tag) VALUES (@Tag)23 SET @TagID = SCOPE_IDENTITY()24 SET @TagExists = 025 ENDAny advice?Also if you see any glaring errors or things I could be doing better, I'm open to suggestions. I'm fairly new to sprocs and transactions. Thanks,Travis
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 able to connect to the Oracle database to insert the data into multiple tables using OLEDB connection via Oracle Provider for OLEDB. However, i wish to create a transaction so that i'm able to rollback all the data in the case where the insertion fails in one of the table. May i know where should i start from?
i'm getting following exception when i try to execute stored procedure.{"Ambiguous column name 'MemberID'. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting"Transaction count after EXECUTE indicates that......" In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */ here is my stored procCREATE PROCEDURE dbo.ExportFile @intMonth INT, @intYear INT, @dtFirstDayOfMonth DATETIMEAS BEGINBEGIN TRANSACTION /* I have some logic here that will select rows into temporary table #TEMPRESULT */ UPDATE dbo.RebateInstanceItem SET ResubmitCreated = @dtmNewCreated FROM #TEMPRESULT tTempResult WHERE MemberID = tTempResult.MemberID AND RebateInstanceItem.IsResubmit = 'Y' AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated OR RebateInstanceItem.ResubmitCreated IS NULL) IF @@ERROR<>0 GOTO ERR_HANDLER // when error it will goto error_handler that will rollback DROP TABLE #TEMPRESULT IF @@ERROR<>0 GOTO ERR_HANDLERCOMMIT TRANSACTIONRETURN 0ENDERR_HANDLER: ROLLBACK TRANSACTION RETURN 1GO
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
I have this sql stored procedure in SQL Server 2012:
ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes] @newBatches as dbo.CreateBatchList READONLY , @productId int , @cLevelRatio int , @nLevelRatio int AS set nocount on;
I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."
This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.
Requirements:
1. If 'Channel' is not equal to "Omnibus" where the 'Trans Description'is equal to "Purchase" and "Redemption" for one purchase and one redemption that match on 'System' , 'Account TA Number' , 'Product Name' , 'Settled Date' , and where the 'Trade Amount' of the purchase and redemption is within 5%, then display those set of records.
2. If deemed wash trades, allow user to update the purchase and redemption pair 'Trans Description' from "Purchase" to "Exchange In" and 'Trans Description' from "Redemption" with "Exchange out"
System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description Trade Amount
SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption $ 25,68,458.15
when doing an 'update table set field1 = 'N',using sql query analyzer, is the update committed immediately? If it isn't commited can a 'rollback' be executed, and what is the format of the rollback command?
I'm using an SQL Express database over a network, using a C# Express program. So I had to use pure SQL connections and commands instead of using Data Sources (couldn't find a way for it to work). In the program / DB I've got a couple of Master - Detail situations. Something like:
Product:
-----------
productID
(...)
Acessories:
----------------
acessID
(...)
ProductAcess:
--------------------
productID
acessID
So when inserting a new Product, I'll have to first insert the product (with product name, price, and so on) and once I get the product ID from the insert command, I'll insert the ProductAcess rows. I've found a problem in this though. If for some reason the insert of the product is successful, but the insert of ProductAcess fails, I've got a big mess in hands because I'll have a row in Product with no rows in ProductAcess (which shouldn't happen in my program scenario). I could solve this by deleting all rows from the DB which connected in someway to the product that failed to insert, but would be far better and correct if I used a commit command at the end of the insert commands to make sure only the right data would be inserted (saving time and resources). I use this all the time in Oracle databases, but don't know if it is possible in SQL Express... Is it? How? Thanks
I have several sets of code that need to delete rows from more than one database at a time. The rows are basically linked without being identified as having a foreign key. This means I issue two deletes. If one fails, especially the second one, there is no way to roll the first delete back.Can someone either point me to some code that enables me to link the deletions, allowing me to insure that both are successful or both do not occur. I cannot identify any fields on the secondary database table as specifically linked to the primary, as the secondary database is a storage medium for images, that may be linked to more than one different table.TIA for any opinions, options, etc. Tom
I first must delete any existing log for my current record.
Then verify that the "exec @res = gmw_updatesynclog..." has not failed and a delete log entry has been successfully written as verified by the gmw_updatesynclog's return of int 16.
If all is well then I incrment my counter and delete the record, here in testing I'm just updateing the activity code to del so I can find them, in live I'll just be deleting them.
--My error Server: Msg 266, Level 16, State 2, Line 0 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2. -- end my error
...excerpt from my proc and nested cursor.... ------------------------------ begin calls to be deleted if @rectype = 'C' and (len(rtrim(@notes))<20 or @notes is null) begin transaction
delete gmtlog -- delete any older del log for this recid where frecid = @recid and fieldname = 'zzzDel' and tableid ='"' if @@error = 0 -- 0 or 1 rows affected...
if @res <> 16 rollback transaction goto endd --cleanup and fetch next record
if @res = 16 -- increment count_call_dels set @count_call_dels = @count_call_dels +1 update cal -- update in test delete in live set actvcode = 'DEL' where recid = @recid commit --transaction ------------------------------ end calls to be ... end excerpt...
Hi, I have a procedure of 6500 lines in which i have given a save point at the beginning of the procedure. And am storing the error number in a variable through out the procedure using select of @@error. And at the end if my @error_number is not zero then am rolling back the tranasction else commit the transaction.
Its giving me the error Msg 266, Level 16, State 2, Line 5437 Transaction count after execute indicates that a commit or rollback transaction statement is missing. Previous count = 0, Current count = 1.
I have an overnight process that takes transactions from an external system & applies updates to a single db table. Other processes may be active on the db but none touch the tables I'm using. I cannot guarantee the volume of source transactions (may vary from 100s to 100,000s).
My question is should I protect the update within a begin+commit/rollback or should I have a recovery procedure to run in the event of failure (that would delete any rows added to my db table)? (My preference is to do the latter - so I'm really looking for any reasons why I shouldn't take this approach).
Iam Executing the sp logic.suppose incase if any problem occurs inbetween execution(NO SPACE,communication failure,log full) data is getting commited partially insteady of rollbacking entire transaction.
CREATE procedure RBI_Control_sp as begin
set nocount on --Checking the count before truncating exec fin_ods..count_sp
--Truncating the Table exec fin_ods..trun_sp
--Data Transfer exec fin_ods..RBI_Data_Transfer_sp
--Checking the count after Data transfer exec fin_ods..count_sp
--temp table Table population,Fetching data from the fin_ods[erp Table] exec FIN_wh..RBI_SPExecution_sp
I have a SP which calls another SP with in a Cursor. The main SP updated two of the DB tables. Based on the data passed from main SP the nested SP updates another two table.
What I want at the end of loop the transaction will be committed. In between if any point it fails means the whole transaction will be rollback.
I am using TRY & CATCH to handle transaction commit & Rollback . But I am getting below error
Error coming in SQL end is: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
Hi,I still haven't got a decent book on relational databases :-)My stored procedure insert_wire inserts values into two tables (wire andcablewire). The wire_ref (primary key) will be the same for both inserts.However, if for any reason the first insert fails then I would like arollback system to take place. I have tried testing for an error (@@error<> 0) after the 1st transaction but I just get a syntax error. Am I goingdown the right lines here? Any tips appreciated.Thanks, Mary.CREATE procedure insert_wire(in wire_ref VARCHAR(22), in standardVARCHAR(16), in a_color VARCHAR(16), in material VARCHAR(22),in metres INTEGER, in amps FLOAT(3), in volts FLOAT(3), in ni SMALLINT, insome_comment VARCHAR(32))BEGINinsert into cablewirevalues(wire_ref, standard, a_color, material, metres, some_comment);insert into wirevalues(wire_ref, amps, volts, ni);commit;END!
I have a series of questions about SSIS and transactions. The answers to these questions are probably so obvious that I can't see them, so please feel free to just point out what it is that I'm missing. My transaction-processing experience is very low-level, so I'm probably just not seeing how it's done at the high level of SSIS.
The first question is one that I may know the answer to, so please confirm:
Consider a package with TransactionOption set to Supported. It contains a single Execute SQL Task with TransactionOption set to Required. Is it true that if that Execute SQL Task succeeds, that the transaction commits, and that if the task fails, the transaction rolls back?
Consider another package with TransactionOption set to Supported. It contains a Sequence Container with TransactionOption set to Required. That container contains our same Execute SQL Task, but that is joined to a script task by a "success" precedence constraint. The script task simply returns Dts.Results.Failure. Is it the case that the transaction will roll back? That is, is it truly a simple failure result that would initiate the rollback?
If a DataFlow Task is the one that is set to Required, does that mean that every transactional operation within that task will commit in a single transaction? For instance, if I'm inserting five rows for each input record from a flat file, and if my flat file has 1000 records in it, will I see a single transaction with 5,000 rows? Thanks for your patience!
My source table has two columns... Policynum and PolicyStartdate and data looks like.. . Policynum PolicyStartdate 123G 01/01/2012 456D 02/16/2012 789A 01/21/2012 163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum ='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
First of all, this is my first time using SQL SERVER 2005 express, before that i'm using POSTGRESQL database.
I would like to know how what's the equivalent command for "BEGIN","ROLLBACK","COMMIT", these are the POSTGRESQL COMMAND use to start transaction, rollback transaction and commit transaction.
Example when i use this kind of command is . I need to insert data into 3 table. before insert into table1, i issue "begin", start to insert data into table1, if table1 no error, then i proceed to table 2 and table3. if table2 and table3 no error. then issue "commit" to commit the changes. but if any error happen between table1 and table 2 or table 2 and table3, i will issue "rollback" to roll any changes that i make to table1, table2 and table3.
Maybe some one can teach me how to achieve using SQL SERVER 2005 EXPRESS.
How do I make use of begin transaction and commit transaction in SSIS.
As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:
Hresult:0x80004005
descriptionyntax error or access violation.
its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.
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.
I am executing a stored procedure something like this
Create Procedure TEST @test1 @test2 AS BeGIN TRANSACTION ONE SELECT... UPDATE.. .... ....
.... TRUNCATE etc and lot of Select,update and delete statements like this ..... COMMIT TRANSACTION ONE
The Block of code which I have b/w BEGIN TRANSACTION AND COMMIT TRANSACTION ..Will it be rolled back if it encounters any errors in the SELECT,UPPDATE,DELETE ..statements which I have with the transaction one. IF not How do I roll back if it encounters any erros b/w the BEGIN TRANSACTION and END TRANSACTION.
I had thought that if any statement failed within a BEING TRANS .. COMMIT TRANS block, then all the statements would be rolled back. But I am seeing different behavior (SQL Server 2000 8.00.2039)
For instance, run these statements to set up a test: --DROP TABLE testTable1 --DROP TABLE testTable2 CREATE TABLE testTable1 (f1 varchar(1)) CREATE TABLE testTable2 (f1 varchar(1)) CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1) insert into testTable1(f1) values ('a')
So table testTable1 has a unique index on it..
Now try to run these statements:
--DELETE FROM testTable2 BEGIN TRANSACTION insert into testTable1(f1) values ('a') insert into testTable2(f1) values ('a') COMMIT TRANSACTION
SELECT * FROM testTable2
..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?
Iam Executing the sp logic.suppose incase if any problem occurs inbetween execution(NO SPACE,communication failure,log full) data is getting commited partially insteady of rollbacking entire transaction.
CREATE procedure RBI_Control_sp as begin
set nocount on --Checking the count before truncating exec fin_ods..count_sp
--Truncating the Table exec fin_ods..trun_sp
--Data Transfer exec fin_ods..RBI_Data_Transfer_sp
--Checking the count after Data transfer exec fin_ods..count_sp
--temp table Table population,Fetching data from the fin_ods[erp Table] exec FIN_wh..RBI_SPExecution_sp
Hai, in Sql server 2000 database, i want to do the following.. from my UI, i will be updating one table(only 3 columns among the table columns), But the number of records will be around 2000 to 2500. So every 200th record, i want to commit the transaction, so that i cannot lose the data.. Using query can i achieve this? or do i need to use the simple while loop logic. Pls advise me
