Use Of Begin+Commit/Rollback - Or Not?
Sep 26, 2004
Hi
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).
Thanks.
View 1 Replies
ADVERTISEMENT
May 5, 2008
hi,
I have wriiten the code cn.Open();
SqlCommand CmdInsertAct1 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName1.Text.ToString() + "','" + txtActAmt1.Text.ToString() + "','" + ddlAct1.SelectedValue + "' )", cn);SqlCommand CmdInsertAct2 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName2.Text.ToString() + "','" + txtActAmt2.Text.ToString() + "','" + ddlAct2.SelectedItem.Value + "')", cn);
SqlCommand CmdInsertAct3 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName3.Text.ToString() + "','" + txtActAmt3.Text.ToString() + "','" + ddlAct3.SelectedItem.Value + "')", cn);SqlCommand CmdInsertAct4 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName4.Text.ToString() + "','" + txtActAmt4.Text.ToString() + "','" + ddlAct4.SelectedItem.Value + "')", cn);
SqlCommand CmdInsertAct5 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName5.Text.ToString() + "','" + txtActAmt5.Text.ToString() + "','" + ddlAct5.SelectedItem.Value + "')", cn);
CmdInsertAct1.ExecuteNonQuery();
CmdInsertAct2.ExecuteNonQuery();
CmdInsertAct3.ExecuteNonQuery();
CmdInsertAct4.ExecuteNonQuery();
CmdInsertAct5.ExecuteNonQuery();
cn.Close();....................................................................
in this code I want to put Commit,Begin,Rollback Transactions.Plz help me.send replies urgently.
View 3 Replies
View Related
Jun 27, 2007
Hello,
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.
Thanks and Regards.
Beh Chun Yit
View 1 Replies
View Related
Mar 14, 2001
Many times i write stoted procedures with transaction blocks.
I have delete a row after begin transaction and in continue i
read from table the select statement get back the deleted row:
begin tran
delete mytable
where id = @myid
and seqid = 3
select sum(balance)
from mytable
where id = @myid
............
...............
commit tran
.... OR
rollback tran
the sum(balance) function has calculate the balance of row 3
I use SQL 7.0
Thanks
Renato
View 1 Replies
View Related
Aug 10, 2001
No I did not write this below, this is from a vendor, I used profiler and I believe their SP is causing a blocking problem on their vendor supplied DB. It thought at the least always have a begin end or a begin trans commit trans. ANy quick opinions greatly appreciated
create procedure write_planned_service_rec
@p1 varchar(20),@p2 varchar(20),@p3 varchar(20),@p4 varchar(20),@p5 varchar(20),
@p6 varchar(20),@p7 varchar(20),@p8 varchar(20),@p9 varchar(20),
@p10 varchar(20),@p11 varchar(20),@p12 varchar(20),@p13 varchar(20),@p14
varchar(20),
@p15 varchar(20),@p16 varchar(20),@p17 varchar(20),@p18 varchar(20),@p19
varchar(20),
@p20 varchar(20)
AS
IF @p20 = 'P'
update patient set date_insurance_updated = getdate() where patient_id =
@p1 and practice_id = @p13
View 1 Replies
View Related
Feb 28, 2008
Can help me?
I have to insert BEGIN and COMMIT Transaction in my stored procedure (call to a trigger)
Code Snippet
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [dbo].[DETTAGLIO_TURNI_DIFENSORI]
(
@tipo_albo VARCHAR(50),
@data_inizio DATETIME,
@data_fine DATETIME,
@descrizione VARCHAR(50),
@idturno INT,
@n_dif INT
)
AS
-- DICHIARAZIONE VARIABILI...
DECLARE @dett_idturno as INT
DECLARE @totale_giorni as INT
DECLARE @tipo_albo_A as VARCHAR(9)
DECLARE @tipo_albo_B as VARCHAR(9)
DECLARE @data_odierna as DATETIME
DECLARE @tot_avvocati as INT
DECLARE @avv_giorno as INT
DECLARE @avv_giornoA as INT
DECLARE @avv_giornoB as INT
DECLARE @conta_giorni as INT
DECLARE @incremento_giorni as INT
DECLARE @incr_dif_giorno as INT
DECLARE @nuovo_idturno as INT
DECLARE @idalboturno as INT
DECLARE @old_turni as INT
DECLARE @tot_giorniA as INT
DECLARE @tot_giorniB as INT
DECLARE @conta_giorni_incremento as INT
DECLARE @conta_avvocati as INT
DECLARE @avv as INT
DECLARE @altri_turni AS INT
DECLARE @neg_giorni_incremento as int
-------------------------------------------------------------------------------------------------------
-- SET VARIABILI PER EVITARE PROBLEMI CON NULL o 0
SET @totale_giorni = 0
SET @tot_avvocati = 0
SET @avv_giorno = 0
SET @conta_giorni = 0
SET @incremento_giorni = 0
SET @incr_dif_giorno = 0
SET @idalboturno = 0
SET @old_turni = 0
--------------------------------------------------------------------------------------------------------
SET @nuovo_idturno = @idturno
BEGIN TRAN
-- conta i giorni
SELECT @totale_giorni = (DATEDIFF(dd, @data_inizio, @data_fine))+1
-- select da vista avvocati per tipo difensori selezionato.. (controllare anche data_fine_iscrizione?)
create table #Tmp
(
[ID_anagrafica] varchar(50)
)
IF @tipo_albo = 'DIFO'
BEGIN
SET @tipo_albo_A = 'DIFM'
SET @tipo_albo_B = 'CPT'
-- select per contare..
SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_ORDINARI
SELECT @tot_avvocati As tot_avvocati
-- creo tabella temporanea..
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_ORDINARI ORDER BY ID_ANAGRAFICA
END
IF @tipo_albo = 'DIFM'
BEGIN
SET @tipo_albo_A = 'DIFO'
SET @tipo_albo_B = 'CPT'
-- select per contare..
SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_MINORI
SELECT @tot_avvocati as tot_avvocati
-- creo tabella temporanea..
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_MINORI ORDER BY ID_ANAGRAFICA
END
IF @tipo_albo = 'CPT'
BEGIN
SET @tipo_albo_A = 'DIFM'
SET @tipo_albo_B = 'DIFO'
-- select per contare..
SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_CPT
SELECT @tot_avvocati as tot_avvocati
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_CPT ORDER BY ID_ANAGRAFICA
END
DECLARE @idanagrafica varchar(50)
-- GIORNI PER AVVOCATO
-- tot_giorni * n_dif_minimo / tot_avvocati = tot_giorni_avvocato (con intero successivo)
SET @incremento_giorni = ((@totale_giorni * @n_dif)/@tot_avvocati)
-- controllo se c'è resto..
IF ((@totale_giorni * @n_dif)%@tot_avvocati) <> 0
BEGIN
SET @incremento_giorni = @incremento_giorni + 1
END
-- ogni avvocato deve essere difensore per almeno 2 giorni di seguito..
-- quindi se l'incremento è minore di 2 deve essere uguale a 2
IF @incremento_giorni < '2'
BEGIN
SET @incremento_giorni = '2'
END
-- AVVOCATI AL GIORNO
-- numero variabile.. prendere in considerazione il primo intero e l'intero successivo..
SET @avv_giorno = (@incremento_giorni * @tot_avvocati)/@totale_giorni
SET @avv_giornoB = @avv_giorno
-- controllo il resto della divisione.. se <> 0 @avv_giornoB = @avv_giorno + 1..
-- altrimenti i due valori sono uguali..
IF ((@incremento_giorni * @tot_avvocati)%@totale_giorni) <> 0
BEGIN
SET @avv_giornoA = @avv_giorno + 1
END
ELSE
BEGIN
set @avv_giornoA = @avv_giorno
END
-- conteggi giorni totali difensori...
-- giorni con N difensori
SET @tot_giorniB = ((@avv_giornoB * @incremento_giorni)/@totale_giorni)
-- giorni con M difensori
SET @tot_giorniA = @totale_giorni - @tot_giorniB
declare @totale as int
declare @conta_inseriti as int
set @conta_inseriti = 0
set @totale = (@avv_giornoA * @tot_giorniA) + (@avv_giornoB * @tot_giorniB)
-- ciclo per totale dei giorni
SET @conta_giorni = 1
SET @conta_giorni_incremento = 0
SET @conta_avvocati = 0
WHILE @conta_giorni <= @totale_giorni BEGIN
-- ogni giorno @avv deve essere ZERO
SET @avv = 0
IF @conta_giorni <= @tot_giorniA
BEGIN
SET @avv_giorno = @avv_giornoA
END
ELSE
BEGIN
SET @avv_giorno = @avv_giornoB
END
-- ciclo per ogni giorno per totale di avvocati/giorno
SET @incr_dif_giorno = 0
WHILE (@incr_dif_giorno < @avv_giorno) AND EXISTS(SELECT TOP 1 ID_anagrafica FROM #Tmp) BEGIN
SET @conta_avvocati = @conta_avvocati + 1
SET @data_odierna = DATEADD(dd, (@conta_giorni-1), @data_inizio)
SET @neg_giorni_incremento = -1 * @conta_giorni_incremento
SET @old_turni = 0
SET @altri_turni = 0
SELECT TOP 1 @idanagrafica = ID_anagrafica from #Tmp
DELETE #Tmp WHERE ID_anagrafica = @idanagrafica
-- query che controlla i turni già assegnati per altre liste...
SELECT @old_turni = COUNT(*)
FROM Albo_Turno_Dettaglio CROSS JOIN
ALBO_TURNO
WHERE
Albo_Turno_Dettaglio.idalbo = @idanagrafica
AND
(
ALBO_TURNO.Tipo = @tipo_albo_A --- probabile problema con trigger
OR
ALBO_TURNO.Tipo = @tipo_albo_B
)
AND
(
Albo_Turno_Dettaglio.Data
BETWEEN
DATEADD(dd, (@neg_giorni_incremento + 1), @data_odierna)
AND
DATEADD(dd, (@incremento_giorni-@conta_giorni_incremento), @data_odierna)
)
SELECT @old_turni AS old_turni
-- (nel caso in cui il ciclo ricominci..) controllare che questo avvocato non abbia
-- già un set di giorni in questo turno...
-- passato il primo controllo.. deve passare anche questo..
SELECT @altri_turni = COUNT(*)
FROM Albo_Turno_Dettaglio
WHERE
Albo_Turno_Dettaglio.idalbo = @idanagrafica
AND
Albo_Turno_Dettaglio.idturno = @nuovo_idturno
SELECT @altri_turni AS altri_turni
IF @old_turni = NULL
BEGIN
SET @old_turni = 0
END
IF @ALTRI_TURNI = NULL
BEGIN
SET @ALTRI_TURNI = 0
END
IF (@old_turni = 0 AND (@altri_turni = 0 OR @altri_turni < (@incremento_giorni)))
BEGIN
-- se non ci sono turni sovraposti assegna il turno all'avvocato x N giorni (incremento_giorni)
-- seleziono il dettaglio con idturno max per aumentare di uno...
SET @idalboturno = 1
SET @dett_idturno = 1
SELECT @dett_idturno = MAX(idalboturno)
FROM Albo_Turno_Dettaglio
if (@dett_idturno) = null
begin
set @dett_idturno = 0
end
SET @idalboturno = @dett_idturno + 1
BEGIN TRAN
INSERT Albo_Turno_Dettaglio
(
idalboturno,
idalbo,
idturno,
data
)
VALUES
(
@idalboturno,
@idanagrafica, -- da cursore
@nuovo_idturno,
@data_odierna -- problemi inserimento data??
)
COMMIT
-- valorizza il numero degli avvocati del giorno + 1
SET @incr_dif_giorno = @incr_dif_giorno + 1
END
ELSE
BEGIN
-- non incrementare la variabile...
SET @incr_dif_giorno = @incr_dif_giorno
END
END -- END WHILE AVVOCATI PER GIORNI
SET @conta_giorni_incremento = (@conta_giorni_incremento + 1)
IF @conta_giorni_incremento < @incremento_giorni
BEGIN
--DEVO RIPOPOLARE LA TABELLA PERCHE' OGNI AVVOCATO DEVE AVERE X GIORNI..
-- cancello tutti i record e poi inserisco di nuovo..
DELETE FROM #Tmp
IF @tipo_albo = 'DIFO'
BEGIN
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_ORDINARI ORDER BY ID_ANAGRAFICA
END
IF @tipo_albo = 'DIFM'
BEGIN
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_MINORI ORDER BY ID_ANAGRAFICA
END
IF @tipo_albo = 'CPT'
BEGIN
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_CPT ORDER BY ID_ANAGRAFICA
END
END
ELSE
BEGIN
set @conta_giorni_incremento = 0
END
SET @conta_giorni = @conta_giorni + 1
END
DROP TABLE #TMP
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 9 Replies
View Related
Dec 15, 2007
I have a cursor loop through a set of records that looks something like this.
OPEN database_cursor
FETCH NEXT FROM database_cursor
INTO @iID
WHILE @@FETCH_STATUS = 0
BEGIN
update table 1
update table
......
FETCH NEXT FROM database_cursor
INTO @iID
END
CLOSE database_cursor
DEALLOCATE database_cursor
Is there a way i could put all the UPDATE statements within a transaction. either everything goes or nothing.THnaks
Thanks,
View 3 Replies
View Related
Dec 21, 2007
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?
View 4 Replies
View Related
Apr 4, 2007
Hi,
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
View 1 Replies
View Related
Oct 27, 2006
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
View 2 Replies
View Related
Jun 11, 2002
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...
exec @res = gmw_updatesynclog 'cal',@recid,'MASTER','D'
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...
TIA
JeffP...
View 2 Replies
View Related
Oct 9, 2000
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.
View 1 Replies
View Related
Apr 29, 2004
I need a trigger to be fired or a stored procedure to be called on an event of COMMIT or ROLLBACK.
Non of ORACLE, SQL-SERVER, DB2 have this feature.
Please let me know any alternative's
View 2 Replies
View Related
Apr 14, 2008
hi friends,
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
set nocount off
end
View 3 Replies
View Related
May 12, 2008
Hi
Need a help for the below req.
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.
Please advise.
View 1 Replies
View Related
Nov 6, 2005
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!
View 1 Replies
View Related
Sep 18, 2007
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!
View 5 Replies
View Related
Jun 26, 2007
Does SQL Server 2005 Express support rollbacks? For example:
BEGIN;
UPDATE CustomerSET LastName = 'Jones';
ROLLBACK;
The ROLLBACK will undo/reverse the changes made by the UPDATE befor a COMMIT is executed.
View 4 Replies
View Related
Nov 10, 2006
Hi ,
In the below procedure in emp table name column is not null.
so when i try to update null value it should rollback previous transactions and should not commit.
but it is updating the previous queries and raising error on 3rd query. not rolliing back. I need to rollback previous queries.
the xactstate i get here is 1.
Please help me with this.
ALTER PROCEDURE [dbo].[tran]
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
declare @name varchar(50)
set @name = null
update dept set dname='ddd' where id=10
update emp set name='eee' where id=354
update emp set name=@name where id=354
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (XACT_STATE())=-1 ROLLBACK TRANSACTION
END CATCH
END
venp---
Hi ,
View 6 Replies
View Related
Aug 19, 2015
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."
View 2 Replies
View Related
Sep 17, 2015
I am trying to use sql transaction in foreach loop where I am passing 1 ID @ a time .I want to rollback only current ID.1st task in foreach is execute sql task with begin tran and next is data flow task with 2 source and destination for 2 different tables (if anyone fails then rollback from all tables). After data flow is another execute sql task with rollback tran on failure (logical condition on pipe is OR). The rollback works but only rollback 1 table although I want to rollback on current iteration ID of foreach from both tables.
View 3 Replies
View Related
Jun 15, 2008
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
View 4 Replies
View Related
Oct 23, 2015
I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.
Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint]
AS
DECLARE @TableName VARCHAR (50)
[Code] ...
View 2 Replies
View Related
Nov 13, 2007
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.
Thanks
View 9 Replies
View Related
Oct 8, 2007
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?
View 4 Replies
View Related
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?
View 5 Replies
View Related
Aug 28, 2006
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
View 5 Replies
View Related
Jun 1, 2007
Hi
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.
Thanks,
Prashant
View 3 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 19, 2007
I am running an Execute SQL task that does a Begin Tran, then the next task in the sequence is a data task which imports a XML file into two tables. If i doo a Rollback Tran only one of the two tables is rolled back.
Is it possible to have both tables rolled back from one Begin tran command or do i need to split the datatasl into two and treat each import as a seperate issue ?
The connection is set to retainsameconnection
thanks
View 7 Replies
View Related
Oct 11, 2000
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.
Thanks
micky
View 1 Replies
View Related
Sep 24, 2007
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?
View 8 Replies
View Related
Oct 8, 2007
Hi,
I want to rollback my t-sql if it encounters an error. I wrote this code:
begin tran mytrans;
insert into table1 values (1, 'test');
insert into table1 values (1, 'jsaureouwrolsjflseorwurw'); -- it will encounter error here since max value to be inputted is 10
commit tran mytrans;
I forced my insert to have an error by putting a value that exceeds the data size. However, I didn't do any rollback. Anything i missed out?
cherriesh
View 4 Replies
View Related
