Rolling Back Transaction After .Commit() Runs Successfully

Jun 10, 2007

I have a page that runs a transaction correctly after a button click.

I want to allow someone to click a button that rolls back the transaction, after the transaction runs on the first button click.

I can also successfully roll back within the first button click.

 I'm getting a NullReference error when trying to access SqlTransaction.Rollback() outside the button click.

 If SqlTransaction.Commit() completes without error can SqlTransaction.Rollback() be called after?

I tried making 'trans' a more global variable and it still gave me the error.

Button Click 1:

Dim trans As SqlTransaction

trans = connection.BeginTransaction()

try

'run SQL Statement

trans.Commit()

Catch e As Exception

 trans.Rollback()

throw e

end try

 

Button Click 2:

trans.Rollback()

 

View 4 Replies


ADVERTISEMENT

Transaction Not Rolling Back

Mar 19, 2007

I have a stored procedure that calls another stored procedure with thefirst stored procedure opening a transaction:BEGINSET XACT_ABORT ONBEGIN TRANSACTIONdoes various updates/insertscalls 2nd stored procedure to proccess updates/inserts common to manyother stored proceduresdoes more various updates/insertscommitENDThe problem I'm having is that within the 2nd stored procedure is thatif it encounters an error, it does not roll back the entiretransaction and I finish up with missing records in the database. Amusing this in the 2nd stored procedure:if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendWhat could the problem be? From what I've read, it seems as thoughyou can't have an open transaction within one sp that calls another spand it maintains the same transactoin? Is this corrrect?I tired the following too, and I still couldn't get it to work. Anyideas anyone?************ sp 1 ***********Declare @AddressError char(3)SET XACT_ABORT ONBEGIN TRANSACTIONexec Sp2@AddressError OUTPUT,@variable1,@variable2,etc. etc************** sp 2 *****************@AddressError char(3) OUTPUT,if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendSET XACT_ABORT ONBEGIN TRANSACTIONprocess updates/insertsSet @AddressError = 'no'Commit******** back to sp 1************If @AddressError <'no'BEGINrollback transactionENDcontinue doing updates/insertscommit

View 1 Replies View Related

Rolling Back Transaction

Jun 16, 2007

Hi,



I have a case where I read from SQL Server DB and write to a flat file.

I have one Data Flow Task inside which I have a OLEDB source component that feeds rows to a script component that writes to a flat file. I have set the txn attributes for the container to "Required" and "Read committed" . But I find that rows are written to flat file even when I throw an exception from my script component. Question is how do I prevent rows from being written to the flat file if error/exception happens. I want the whole process to be in a single transaction.



Thanks

View 3 Replies View Related

My Transaction Isnt Rolling Back When It Should

Apr 9, 2008

Ive got an insert statement that fails, and below that I have code like the following:


IF @@ERROR <> 0
BEGIN
-- Roll back the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error INSERT INTO Address.', 16, 1)
print 'test was here'
RETURN
END


However, there is now rollback and the inserts below it are going through.

what do i have wrong ?

View 5 Replies View Related

Mirroring And Rolling Back Transaction Log

Mar 17, 2008

hi,
i want to create a disaster recovery site, to which i can fail over (not automatically),
and also to have the option to return the database to a point in time?
for example,
if my principal server fails in 17:00,
i want to have the option to make the mirror server available for users from 17:00 (or at least close to that time),
and also to be able the return to the data from 16:00 (in the mirror site).
Is it possible, and what is the best way to do it?

Thanks.

View 1 Replies View Related

Rolling Back Transaction Logs Database

Feb 3, 2004

I have a Development database and I want to roll it back to Monday morning. I backed up the database and used the command:

RESTORE DATABASE ITTEST
FROM ITTEST20040203
WITH NORECOVERY
GO

RESTORE LOG MyNwind
FROM ITTEST20040203
WITH RECOVERY, STOPAT = 'FEBRUARY 2, 2004 09:00 AM'
GO

The transaction logs have never been truncated.

But it does not seem to have worked. It this the best way to do a roll back the database or have I missed out something.

thanks.

View 2 Replies View Related

SQL Server 2014 :: Stored Proc Timing Out Halfway Through - Rolling Back Without Transaction

Jul 7, 2015

We have a high volume database with 1000's of users and 1000's of procs. Our application enforces a 20 second timeout on all connections.

We can't adjust the 20 seconds - this is a business rule.

It sometimes happens that a proc does not complete within 20 seconds and then times out halfway though. This causes data inconsistency where 50% of the code was saved to the DB and 50% was not - seeing that a stored proc is not transactional and therefor does not roll back the code.

We can't put the code in a TRANSACTION in order to roll back when a time out occurs, because this causes exclusive locks on the tables.

So I guess my question is:
Is it possible to undo/rollback all the code in a proc when a timeout occurs - without using a TRANSACTION? And if a TRANSACTION is the only way - how do I avoid the exclusive lock and blocks?

View 4 Replies View Related

SQL Server Admin 2014 :: Scheduled Job Runs Successfully But Table Does Not Update

Jun 25, 2015

I have a job scheduled that imports a table from a Oracle database. The job runs at 3am and reports success. But for some reason when i query the table to see how many records there are, I see the same row count as the day before (it should increase everyday- student enrollment). When i execute the package manually, the table updates fine.

SQL Server 2014/Win7 x64.

View 7 Replies View Related

Rolling Back SQL Server 2005 Databases Back To SQL Server 2000

Sep 22, 2006

Does anybody know of a way to rollback SQL Server 2005
databases back to SQL Server 2000? Is there a way of doing it without
resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database
to a SS 2000 instance without any issues.



I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I
want to rollback to SS 2000, which was a lot more stable. I am having
several major issues that are affecting my whole company's day-to-day
operations and the managers are not happy. Some of the issues include
night time batch running very sluggish for no apparent reason. This is a
biggest problem because it only occurs once or so a week and causes a disturbance
with the daily activities when the night time processing isn€™t completed on
time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I
don't believe it is a matter of my application needing to be retuned because if
that was the case, then why isn't it running sluggish every night? Also,
it's never the same day that the sluggish behavior occurs. If it was occurring
on the same night, then I would have something to investigate within our
application, but it doesn't. Another issue that I am having involves a
night time job that restores a copy of the production database to the Data
Warehouse server to be used for updating the data warehouse. Again, most
of the time it runs great (~2 1/2 hours), but once or twice a week, it goes
stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening
the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the
database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended
state. What's a suspended state and how can I get it out of suspended
state? I can find no information about suspended state in BOL. I
did a Google and nothing came up. If this suspended state was put
in for security reasons, great, but then tell me how I can remove the suspended
state. I am also not happy with the
fact that I can't get accurate information about the queries that are actively
running at that particular moment. In SS 2000, when I noticed high CPU
usage on the server, I would run the sp_who2 active stored proc and it would
show me all the active thread and how much CPU it was consuming. I would
then find the running threads with the highest CPU numbers and investigate the
query and see if we could improve it. Now in SS 2005, I get in the same
situation and run the sp_who2 stored proc, and there is no smoking gun.
All of the active threads are showing very little CPU usage, which I am very
suspect of. What the heck happen to sp_who2? I looked at some of
the other ways of looking at running processes (i.e... sys.sysprocesses) and
they don't appear to be giving the information that I need.



I am very unhappy and I just want to roll back to SS 2000 and wait a couple of
years before I upgrade to SS 2005.


Dave Brown

View 1 Replies View Related

Rolling-back

Mar 27, 2001

If I issue -
update x set y = 'z'

is there a way to undone/roll-back what was done in SQL 7 ?


Thanks very much.

-FB

View 2 Replies View Related

Rolling Back DTS

Sep 8, 2004

I have created a DTS Package that does the following:
1 Delete all data from table 1 (SQL Task)
2 Import Data from .csv file into table 1 (Data import)
3 insert records from table 1 into table 2 when they dont exist in table 2 (SQl Task)

This all works fine, but now i want a rollback function in step 1 and 2.
So when Step 1 is finished and something goes wrong i want the deleted data back.

Can somebody help me?

Thanks in advance.

View 1 Replies View Related

Rolling Back In SQL Server

Feb 19, 2004

One of our engineers here by mistake deleted some very important data without any begin trans block and we need the data back very badly.. We have not closed the session as of now.. The engineer was logged in as sa to the DB.

Can someone please help soon on urgent basis...

View 6 Replies View Related

Rolling Back Inserts

Feb 16, 2006

I'm performing a stored proc that has 4 inserts. I only want the inserts to complete as a batch. If one fails, I want to rollback the whole transaction. Does anyone know the syntax?? :)

View 4 Replies View Related

Rolling Back Inserts

Mar 3, 2006

I want to insure that each of my insert statements in a stored proc are rolled back if any of the inserts fail. I already have the below statement with error handling but is this correct? It seems to me that all the steps should be made part of an entire transaction so if one part fails then it all fails. Can someone help me w/ the syntax of this??

CREATE PROCEDURE Addrecords AS

--USERS
INSERT INTO [Production].[dbo].[USERS]([LastName], [UserName], [EmailAddress], [Address1], [WorkPhone], [Company], [CompanyWebsite], [pword], [IsAdmin], [IsRestricted],[AdvertiserAccountID])
SELECT dbo.fn_ReplaceTags (convert (varchar (8000),Advertisername)), [AdvertiserEmail], [AdvertiserEmail],[AdvertiserAddress], [AdvertiserPhone], [AdvertiserCompany], [AdvertiserURL], [AccountNumber],'3',0, [AccountNumber]
FROM production
WHERE not exists (select * from users Where users.Username = temp.AdvertiserEmail)
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END


--PROPERTY
INSERT INTO [Production].[dbo].[Property]([ListDate],[CommunityName],[TowerName],[PhaseName],[Unit], [Address1], [City], [State], [Zip],[IsActive],[AdPrintId])
SELECT [FirstInsertDate],[PropertyBuilding],[PropertyStreetAddress],PropertyCity + ' ' + PropertyState + ' ' + PropertyZipCode as PhaseName,[PropertyUnitNumber],[PropertyStreetAddress],[PropertyCity], [PropertyState], [PropertyZipCode],'0',[AdPrintId]
FROM [Production].[dbo].[Temp]
WHERE AdvertiserEmail IS NOT NULL
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END

View 2 Replies View Related

SQL Server 2014 :: How To Roll Back All Changes If Not All Statements Executed Successfully

Oct 15, 2015

I have a script contains multiple statements to update multiple tables. How can I make sure that either all statements get executed successfully or no changes apply to the tables (in case one or more errors occur)? I've been searching on Internet and it seems like I need to use Rollback and begin transaction.

View 5 Replies View Related

Rolling Back SQL Server 2000 SP 4 To SP 3

Feb 15, 2006

We have installed SQL Server 2000 Service Pack 4 recently and have had various issues with some of our Stored Procedures. (which we didn't have with SP 3)

We are looking at our options regarding rolling back to Service Pack 3.

Can this be done ?
&
If so, is there a method documented on how to do this ?

Any advice would be appreciated.

View 3 Replies View Related

Rolling Back Multiple Transactions?

Apr 4, 2013

I have a stored proc that is executed from within another stored proc, both of these procs run transactions in them that update data.

Now if either of these 2 SPs fail i want to be able to roll back the transactions that have occured so that the data doesn't change.

View 5 Replies View Related

Rolling Back Security For Some Dba's Using Sql 2005

Mar 29, 2007

Don't know if this is possible or not:



Goal: set security on running specific stored procedures based upon user login and databse access

I have some DBA's who want to retain full control of databses / stored procedures as they now have but I want to restrict or rollback some of the changes that were implemented when the sql 2005 was set up. The sql 2005 EE is in a clustered system and uses Mixed Mode Authentication.

An example of what I want to restrict: The DBA's want to be able to view and kill processes for the different databases that are installed under their instance. The problem is other customer databases are also under the same instance.

Is their a way I can combine or have the stored procedure sp_lock only show the processes for the databases they have access to based upon their login? My concern is they will kill a process and affect the other customers.



Thanks for any info or pointers.



carl

View 1 Replies View Related

Rolling Back Sql 2000 To A Point In Time

Aug 9, 2005

I would appreciate a bit of advice here. There is a largish complaint here regarding a cutomer who has entered data in 1 of our online forms, but we suspect this was then overwritten by a cached form she also had open.
Anyway to cut a long story short i need to roll back the database to a point in time.

Not something i have ever had to do.

The row where the id field is equal to 3352, and this would have been written to the database at 14:58:36 on 08-aug-2005. This was over written by the data in row with id 3380 at about 11am this morning, now is this is a live database on a webserver so i cant compromise its uptime as it get written to about 3 times a minute, so how should i go about this?


Anyway, what do you think?


Cheers

View 2 Replies View Related

Rolling Back Transactions For Multiple Stored PRocs

Oct 5, 2007

I have a quick question.

I need to execute some stored procedures in certain steps... all performing some inserts/updates

What i need is, a mechanism, by which i can roll back to the previous state if i encounter an exception (either in the app or SP)

so, if the my first two SP execute fine, perform their functionality like insert/update, and the third one fails...

how can i roll back to the initial state in ASP.NET.

would appreciate any info.
or redirection to the the location where i can look up some info on that.

AJ

View 1 Replies View Related

How Do I Make Use Of Begin Transaction And Commit Transaction In SSIS.

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

BEGIN TRANSACTION And COMMIT TRANSACTION

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

BEGIN TRANSACTION COMMIT TRANSACTION Help

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

How To Commit The Transaction

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 1 Replies View Related

Commit Transaction Logic

Oct 28, 2007

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

View 6 Replies View Related

Transaction In Packages; When Do They Commit?

Oct 31, 2007

Hello,

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?

Thanks! Greetings, Pieter.

View 4 Replies View Related

BEGIN And COMMIT Transaction

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

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

T-SQL (SS2K8) :: Add Record And Commit Transaction

Oct 6, 2014

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.

View 4 Replies View Related

Transaction Commit Doesn't Work

Sep 27, 2007

Could somebody also help me on the issue: I am using OLE DB to setup database with SQL CE 3.1, what i need is to flush the buffer to database file before program exit, I am using DBPROPVAL_SSCE_TCM_FLUSH and Transaction commit, but they don't work for me, My code is here:

1. the code to do transaction commit


// Access Transaction Interface

hr = pIDBCreateCommand->QueryInterface(IID_ITransactionLocal, (void **) &pTransLocal);

ULONG lTransLevel;

// Start the transaction

hr = pTransLocal->StartTransaction( ISOLATIONLEVEL_READCOMMITTED, 0, NULL, &lTransLevel );

// Execute the command with paramters.

hr = pICommandText->Execute(NULL, IID_NULL, &params, &cRowsAffected, NULL);

if( FAILED( hr ) )

{

goto Exit;

}

// commit

hr = pTransLocal->Commit( FALSE, XACTTC_SYNC, 0 );

pTransLocal->Release();



2. the code to set up property:


void LogDatabase::SetSessionProperty( IDBCreateSession *pISession )

{

DBPROPSET dbpropset[1]; // Property Set used to initialize provider

DBPROP sessionProps[1];

VariantInit(&sessionProps[0].vValue);

ISessionProperties *pISessionProperties = NULL;

//************** We Initial a session properties here *************

// Initialize a session object.

HRESULT hr = pISession->CreateSession(NULL, IID_ISessionProperties,

(IUnknown**) &pISessionProperties);

// Initialize the property to change commit mode.

sessionProps[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;

sessionProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;

sessionProps[0].vValue.vt = VT_I4;

sessionProps[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;



// Initialize the session property set.

dbpropset[0].guidPropertySet = DBPROPSET_SSCE_SESSION;

dbpropset[0].rgProperties = sessionProps;

dbpropset[0].cProperties = sizeof(sessionProps)/sizeof(sessionProps[0]);

// Set the session property.

hr = pISessionProperties->SetProperties(sizeof(dbpropset)/sizeof(dbpropset[0]),

dbpropset);

VariantClear(&sessionProps[0].vValue);

}

View 1 Replies View Related

Commit Fail And Transaction Log Is Full

Feb 15, 2007

Hi,

Our testing server experienced a timeout exception when execute System.Data.SqlClient.SqlTransaction.Commit() in SQL Server 2000 thru .Net Framework 1.1. And this happened at 2007-02-13 18:07:05,954.
It was strange to us that all the insert statement can be executed without error within the transaction but the commit operation fails.
Moreover, after about 40 minutes, we found that the transaction log of this database is full.

Here is the exception and SQL Server 2000 Error Log:
Exception Stack Trace:
DateTime: 2007-02-13 18:07:05,954
Req Id: bccdae08-cc47-4f85-8f48-5f0b9dbbbf88
Exception: MyDatabaseException
Detail:
MyDatabaseException:
Index #0
Server: MySQLServer
Source: .Net SqlClient Data Provider
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
LineNumber: 0
Procedure: ConnectionRead (recv()).
State: 0
Error Number: -2 ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ReadBuffer()
at System.Data.SqlClient.TdsParser.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream)
at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String sqlBatch, String method)
at System.Data.SqlClient.SqlConnection.ExecuteTransaction(String sqlBatch, String method)
at System.Data.SqlClient.SqlTransaction.Commit()


SQL Server Error Log:

2007-02-13 13:02:23.72 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944769, first LSN: 9434:22326:1, last LSN: 9434:22360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:MSSQLBackupMyDatabaseName.bak'}).
2007-02-13 15:50:52.40 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944970, first LSN: 9442:38096:1, last LSN: 9443:748:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'Legato#4f96edfd-7fdb-4cd1-a740-3fe9a54d66c6'}).
2007-02-13 18:48:51.42 spid66 Error: 9002, Severity: 17, State: 62007-02-13 18:48:51.42 spid66 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:52:40.61 spid58 Error: 9002, Severity: 17, State: 6
2007-02-13 18:52:40.61 spid58 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:53:22.69 spid61 Error: 9002, Severity: 17, State: 6
2007-02-13 18:53:22.69 spid61 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:54:01.11 spid57 Error: 9002, Severity: 17, State: 6
2007-02-13 18:54:01.11 spid57 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..



Are these 2 things related? Would a full transaction log cause a commit operation fail?

Please kindly advice.

Thanks a lot.

View 3 Replies View Related

Error 266 - I'm Confused, Only One Transaction With COMMIT And ROLLBACK

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

ADO.NET Transaction Fails To Update Database After .Commit()

Nov 24, 2003

SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives???? Anyone else ever heard of such a thing. Please help, before I loose my sanity.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved