How To Create A Transaction Commit/Rollback For Oracle DB In SSIS
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?
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.
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'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 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?
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 have a update trigger. In this trigger I need to insert few records in 3 tables. If error comes in any of these inserts then previous inserts to get committed. This trigger was written in Sybase and it was possible to create transaction and commit the transactions.
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.
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!
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.
I have a package that inserts data from a sql server table to an oracle table. I use a dataflow task to do the loading.
The weird thing is, if the package fails during execution, the transaction does not perform a rollback and the inserted data is committed.
The dataflow component has its TransactionOption set to Supported (this has always been the case for our sql to sql packages). However, now that I have a sql to oracle package, it won't work anymore.. also, setting the TransactionOption of the dataflow component to Required causes the whole package to fail.
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
I am a SSIS developer from the Netherlands. I have a question about the use of a transaction in a package. I have a Sequence container with some data flow tasks and some SQL tasks. The TransactionOption for this container is set to Required. The transactionOption for the tasks in the container are set to Supported. After this container I have put some other tasks (like a script task and an send mail task). This last 2 tasks have a TransactionOption set to Not Supported. I have done this this way because if something goes wrong in the Sequence Container everthing in this container will be rollbacked. This work fine! But if one of the last 2 task goes wrong (the tasks after the container) everthing in the container is also rollbacked. I don't know why this is happening. Can you help me with this?
-- 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
I am simply inserting records in a table, But if record already present then i dont want add that record and display resultset as -1.If record is not present then i want to add that record and commit transaction. I am doing like below : below is sample, I am considering only two columns.
CREATE PROCEDURE [dbo].[Sproc__Save_Teacher_Details] -- Add the parameters for the stored procedure here @FacilitiDetailID int ,@SpecialityType varchar(50) AS BEGIN
[code]....
above code is working fine for new recordset but if record exists then it is giving two resultsets -1 and 1, but i want to display only -1.