Transaction Not Rolling Back
Mar 19, 2007
I have a stored procedure that calls another stored procedure with the
first stored procedure opening a transaction:
BEGIN
SET XACT_ABORT ON
BEGIN TRANSACTION
does various updates/inserts
calls 2nd stored procedure to proccess updates/inserts common to many
other stored procedures
does more various updates/inserts
commit
END
The problem I'm having is that within the 2nd stored procedure is that
if it encounters an error, it does not roll back the entire
transaction and I finish up with missing records in the database. Am
using this in the 2nd stored procedure:
if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')
begin
RAISERROR('error message', 16, 1)
RETURN
end
What could the problem be? From what I've read, it seems as though
you can't have an open transaction within one sp that calls another sp
and it maintains the same transactoin? Is this corrrect?
I tired the following too, and I still couldn't get it to work. Any
ideas anyone?
************ sp 1 ***********
Declare @AddressError char(3)
SET XACT_ABORT ON
BEGIN TRANSACTION
exec Sp2
@AddressError OUTPUT,
@variable1,
@variable2,
etc. etc
************** sp 2 *****************
@AddressError char(3) OUTPUT,
if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')
begin
RAISERROR('error message', 16, 1)
RETURN
end
SET XACT_ABORT ON
BEGIN TRANSACTION
process updates/inserts
Set @AddressError = 'no'
Commit
******** back to sp 1************
If @AddressError <'no'
BEGIN
rollback transaction
END
continue doing updates/inserts
commit
View 1 Replies
ADVERTISEMENT
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
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
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
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
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
View Related
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
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 isnt 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
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
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
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
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
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
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
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
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
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
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
Feb 8, 2001
Hi all,
I'm getting the following error message in NT event view:
Error: 9002, Severity: 17, State: 2
The log file for database 'db_sys' is full. Back up the transaction log for the database to free up some log space.
But I don't know how to back up the transaction log for the database.
Do I need TRUNCTE LOG (in E.M) to free up some log space?
I only know back up all the database using ALL TASKS -> BACKUP DATABASE in E.M. Please help me. Thanks in advance.
TH
View 1 Replies
View Related
Feb 28, 2008
Hello, I do not know how to implement transaction roll back in asp.net application. I am using SqlHelper class to communicate with my sql db.Thanks, junior
View 1 Replies
View Related
Apr 3, 2015
The following transaction runs, then reverts back after 10 mins.
begin Tran [UpdateSundays]
update ScanAssetInformation with (rowlock)
set BusinessDate = dateadd(day,-2,BusinessDate)
where datepart(weekday,BusinessDate) = 1
commit tran [UpdateSundays]
begin Tran [UpdateSaturdays]
update ScanAssetInformation with (rowlock)
set BusinessDate = dateadd(day,-1,BusinessDate)
where datepart(weekday,BusinessDate) = 7
commit tran [UpdateSaturdays]
View 2 Replies
View Related
Aug 15, 2007
When using the maintenance plan to backup t logs, should I enclude the system database logs such as master, model, etc...?
Thanks!
View 1 Replies
View Related
Feb 1, 2001
hi, I would like to know the correct reaction for a crash in both senarios.
First senario,
I made a full back up at 6 am , then scheduled sql server to make transaction log back up every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 12:30. How would I resotre the data in the first senario....Can I restore the full back up done at 6 am then restore the last transaction log backup ( which is 12 Noon ) . I am not sure If I need to resotre the whole tran from 6 am till the time it was crashed.
Second senario,
I made a full back up at 6 am, then scheduled sql server to make Incremental backup every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 3:00 pm. How would I resotre the data in the second senario. ....Do I restore the full backup at 6 am then restore each incremental backup backwords ( 2,12,10,8)
AS you can see, I am not sure how to deal with this issue, I do appreciate your feedback.
Best regards
Ahmed
View 1 Replies
View Related
Feb 1, 2001
hi, I would like to know the correct reaction for a crash in both senarios.
First senario,
I made a full back up at 6 am , then scheduled sql server to make transaction log back up every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 12:30. How would I resotre the data in the first senario....Can I restore the full back up done at 6 am then restore the last transaction log backup ( which is 12 Noon ) . I am not sure If I need to resotre the whole tran from 6 am till the time it was crashed.
Second senario,
I made a full back up at 6 am, then scheduled sql server to make Incremental backup every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 3:00 pm. How would I resotre the data in the second senario. ....Do I restore the full backup at 6 am then restore each incremental backup backwords ( 2,12,10,8)
AS you can see, I am not sure how to deal with this issue, I do appreciate your feedback.
Best regards
Ahmed
View 2 Replies
View Related
Mar 4, 2008
Hi,
Is there a way to write to a log-table inside a transaction which is rolled back without rollback of this log-entry.
thanks in advance
Raimund
View 9 Replies
View Related
May 4, 2015
I am confused about save transaction in the below scenario :
begin transaction
save transaction t1
delete from #t1
save transaction t2
begin try
delete from #t2
[Code] ....
If there is error after delete #t2 , transaction t1 is rolled back. But i am not able to understand why i am getting error in the statement 'rollback transaction t2' . I am getting error as 'Cannot roll back t2. No transaction or savepoint of that name was found.'. but save point t2 is mentioned in the code.
View 3 Replies
View Related
Aug 16, 2007
Hi,
I followed Remus' post about not doing 'fire and forget'.
I have two queues, ProcessingSendQueue and ProcessingReceiveQueue.
Once i receive from ProcessingReceiveQueue, activation SP gets called on ProcessingSendQueue and ends conversation.
However,if I then get an exception, the action of the activation SP ( ie the ending of the conversation ) does not get rolled back... is this possible? I would have thought that the action of the activation SP would get rolled back too.
My ProcessingSendQueue activation SP is as follows:
ALTER PROCEDURE [dbo].[ProcessingSendQueue_AP]
AS
BEGIN
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @message_type SYSNAME;
DECLARE @message_body NVARCHAR(4000);
RECEIVE @dh = [conversation_handle], @message_type = [message_type_name], @message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [ProcessingSendQueue];
IF @dh IS NOT NULL
BEGIN
IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR (N'Received error %s from service [ProcessingReceiveQueue]', 10, 1, @message_body) WITH LOG;
END
END CONVERSATION @dh;
END
END
View 1 Replies
View Related
Nov 1, 2007
Goofed up and ran an update query. It messed up all the data in a single table. I'm trying not to restore the table from a previous backup since the backup is more than 20 GB. It's going to take forever to restore it. Any advice would be much appreciated!
Thanks!
Charles.
View 3 Replies
View Related
Mar 4, 2011
We had a siutation last night in our production environment that forced us to revert back to an earlier version of the database (before a major code rollout that failed). After restoring the days full backup (with NORECOVERY), and then restoring a DIFF backup (FULL RECOVERY and had checked Preserve Replication Settings)...the transaction replication failed.
Message #1
The replication agent has been successfully started. See the Replication Monitor for more information.
Message #2
2011-03-04 15:07:17.566 Copyright (c) 2008 Microsoft Corporation
2011-03-04 15:07:17.566 Microsoft SQL Server Replication Agent: logread
2011-03-04 15:07:17.566
2011-03-04 15:07:17.566 The timestamps prepended to the output lines are expressed in terms of UTC time.
2011-03-04 15:07:17.566 User-specified agent parameter values:
[code]....
I've tried reinitializing the publication/subscription and while that took brand new snapshots and copied it over to the replicated data server, it did not fix the problem.I read from a different post that I could try running "sp_replrestart" but that ran for about a half an hour and didn't appear to do anything but fill up our log files...did I not wait long enough?
The only thing I know to do at this point is to drop the publication on the production server and rebuild it completely (and with all the tables we're replicating that would take quite a bit of time.
View 9 Replies
View Related
Feb 4, 2008
Hi all
Iam working in Prodcution ENV,Please help how make space
The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space.
View 10 Replies
View Related
Apr 24, 2007
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies
View Related
May 14, 2015
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on theĀ roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
View 2 Replies
View Related
Jun 9, 2015
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies
View Related