SQL 2012 :: Rollback Transaction In Nested Stored Procedure?

Nov 24, 2014

create proc proc1 (@param1 int)

as

begin try
declare @param2 int
begin transaction
exec proc2 @param2
commit transaction
end try
begin catch
if @@trancount > 0
rollback transaction
end catch

i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.

View 3 Replies


ADVERTISEMENT

Rollback Transaction To Previous Stored Procedure?

May 6, 2007

Hi all, I have a program that needs to delete records, then re-insert new records to a table. But I need to rollback the transaction IF the insert is not success (error occured). The delete and insert are in 2 difference stored procedure (which have rollback transaction) that calling from 1 stored procedure. My problem is that if Insert is not successful, but the records already deleted previously. How can we rollback the delete transaction when insert is not successful?
Note: if possible, I don't want to delete the records AFTER the insert is successful, or create a temp table to stored the deleted records 
======================================= 
create stored procedure combine_sp
as
begin
       call delete_sp -- have rollback transaction in the delete_sp
      -- what to do if following has error occured, but we already deleted the records above?
       call insert_sp -- have rollback transaction in the insert_sp
end
go
=======================================
Thanks a lot.

View 3 Replies View Related

SQL Server 2012 :: Run Stored Procedure Without Writing To Transaction Log?

Feb 26, 2015

Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.

The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.

Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?

View 6 Replies View Related

Is The Transaction Context Available Within A 'called' Stored Procedure For A Transaction That Was Started In Parent Stored Procedure?

Mar 31, 2008

I have  a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
 Any help on this would be appreciated.

View 1 Replies View Related

Throwing Exception In Stored Proc To Catch In C# And Rollback The Transaction

Sep 27, 2007




Hi,

I am using sql2k5. I just wanted to throw an error from stored procedure with some message to C# to rollback my transaction.
Here is how i wnated to do ( in sequence )

C#
=====
Open a connection
Begin the transaction
Execute the command

In the Stored Proc
===========
do multiple operations one by one
if error
stop processing further
Throw the error
C#
========
if exception
rollback the transaction
else
commit the transaction

I have tried using raise error in stored proc but never thrown exception

Can any one let me know how to achieve this scenario??

~Mohan Babu

View 5 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Will Stored Procedure Rollback If It Fails?

Mar 5, 2008

Hi everyone,

I don't specify the following statements in SP.
begin transaction
commit transaction
rollback

In sp, there are a lot of insert and update. If i execute SP and stop it in half way. will it rollback all update and insert statement? also, will it rollback if it fails?

cheers

View 1 Replies View Related

SQL Server 2014 :: Using (Try-Catch) And (Rollback) On Read Only Stored Procedure?

Apr 30, 2015

In general as understand if we have a stored procedure that does operations like inserts or updates, it makes perfect sense to use a rollback operation within a transaction.

So, if something goes wrong and the transaction does not complete, all changes will be reverted and an error description will be thrown for example.

Nevertheless, does using a rollback within a try catch statement, make sense in a read only stored procedure, that practically executes some dynamic sql just to select data from some tables?

I have around 100 Stored procedures, all of them read only. Today a colleague suggested adding try-catch blocks with rollback to all of them. But since they are just selecting data, I don't see a clear benefit of doing so, compared to the hassle of changing such a big number of SP's.

View 9 Replies View Related

Nested Stored Procedure

Jul 13, 2005

Hi, I have two tables:TABLE 1                        Table 2==========              ==========UID                                  TeamNameTeamID <------------------> TeamID      and I am trying to obtain the value of TeamName via a nested stored procedure. However, I am only allowed to obtain the @UID as input parameter. I tried writing the following:Select    table2.TeamNameFrom       Table1, Table2Where    (table1.TeamID=table2.TeamID) and (UID = @ UID)It seems to not work as TeamID was not given. Thus, my other idea of doing this is to:1) Execute a sql to first obtain the TeamID value with the @UID parameter given.then 2)Execute another sql to obtain the TeamName with the result from the first sql as the input parameter.However, I have never worked with nested sql, so it would be great if someone can link me a site that they know. Better yet, write me an example code.Thanks you very much!

View 3 Replies View Related

Nested Stored Procedure (help)

Jan 31, 2002

Is there a way I can do this? If this statement is true I would like for it to do the update stmt otherwiste do the insert stmt. The code shown below has syntax errors. Any help would be greatly appreciated.

IF (SELECT number, serial
FROM serial
WHERE number = @number and serial is null)

BEGIN

Update Statement

END

ELSE

BEGIN
Insert Statement


END

View 1 Replies View Related

Nested Stored Procedure

Jun 12, 2001

I have one View say V1 and a Table T1
the Structure is say V1 (a,b,a,d) 4 fields
T1 has (e,f,g,h,i,j ) 6 fields

I want to loop through The V1 view and for each record in the View, after
doing some checks i want to insert a record in the T1 table...
using stored procedures, as there could be thousands of records

Something like this

V1 = Select * from V1
While not V1.EOF

Insert into T1

Wend

View 2 Replies View Related

Nested Stored Procedure?

Mar 8, 2005

I have a stored procedure that returns a list of userIds that are available to the logged in user. I need to be able to use this list of userIds in another stored procedure whose purpose is to simply query a table for all results containing any of those userIds. So if my first Stored Procedure returns this: 2 3 5 6 I need my select statement to do something like this: select UserId, Column1, Column2 from Table1 where UserId = 2 or UserId = 3 or UserId = 5 or UserId = 6 I'm very new to stored procedures, can anyone help me with the syntax for doing this. I'm being pressured to get this done very quickly. Thanks for any help!

View 5 Replies View Related

TRANSACTIONS In SSIS (error: The ROLLBACK TRANSACTION Request Has No Corresponding BEGIN TRANSACTION.

Nov 14, 2006

I'm receiving the below error when trying to implement Execute SQL Task.

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'

I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.

Anyone know of the reason?

View 1 Replies View Related

How Do You NOT Rollback Some Code In Nested Procedures?

Apr 29, 2008

I have a procedure that calls other procedures which in turn call other procedures. I have a transaction in the first procedure since I want to rollback everything done if something goes wrong except for rows inserted in a loggtable in my database. The reason for this is that all error shall be saved in this table, otherwise there is no way to find out what error occured.

How do you solve this?

View 4 Replies View Related

Nested Stored Procedure Question!

Jun 10, 2004

Hi,
i'm calling proc2, proc3 from proc1, as soon as proc1 initiates proc2 kicks in and after completion of proc2 only it will jump to proc3 and so on..right
Please clarify on this.
or it would be great if you refer to any manual which gives details aabout nested stored procedure execution criteria..
any help is greately appreciated..
thanks

View 3 Replies View Related

Problem For Nested Stored Procedure

Feb 21, 2008



Hello Guys,
I have a stored procedure. In the stored procedure, I update a value of table field. At the end of the stored procedure, I called another stored procedure. In the second stored procedure, I try to read the value of the field I just updated in the first stored procedure. However, The second stored procedure still get the old value of the field. But, sometimes, it is ok.

Any ideas?

Thanks in advance!

troy

View 1 Replies View Related

T-SQL (SS2K8) :: Nested Stored Procedure - Multiple Results Of Record Sets Coming

Oct 1, 2014

I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.

I have 2 problems in that

1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.

I have attached the scripts also, the line i described in step1 is

View 9 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

What To Do After A Transaction Rollback?

Dec 9, 2003

Hi!

I just made a transaction which writes and reads some data from a database as one unit. Now, I would like to know, what happens, if this transaction does not commit, but instead performs a rollback? Should I use a boolean loop which keeps on trying the transaction until it commits or is there an alternative? I would appreciate any suggestions or help!

Thanks!
Timothy

View 1 Replies View Related

ROllback Transaction

Feb 26, 2007

How do I find out the transaction name to rollback. Thanks

View 1 Replies View Related

Could Not Rollback The Transaction

Apr 10, 2008

Hi all

I received the following message in the application log files while the QA team is tesing the application.

06:40:31 [ERROR ] com.inet.tds.ao: The connection is closed: there was an error on the database could not rollback the transaction

I checked at the SQL server logs and found any thing odd.

what does the above error indicates..

Thanks.

View 1 Replies View Related

Rollback Transaction

Jul 31, 2007

Hi all,
How to work rollback transaction in sql server.

Thanks&Regards
msrs

View 2 Replies View Related

Transaction Log Rollback

Jul 20, 2005

If someone could help me with this I will be in debt to you!I am a .net developer who is working on a system that has a sql server2000 backend. On the weekends, I work from home and I have MSDE on mylaptop. This morning I went to update the main SQL server as I addedsome new views .. however I managed to import the data too!!Therefore I have overwritten my local data to the main sql serverdatabase, which has ressulted in loss of loads of work!Can I rollback to the version before I exported my data from MSDE to themain SQL server. I did the export by using enterprise manager and rightclicking on the table on my local msde and then sending it to the realsql server!Please please please help??*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Rollback Transaction

Jun 15, 2007

i have a froeach loop,in which i have data flow task,which i need to rollback if any other task fails..

here i need to accept multiple files and i have to give a one second pause for between each file for this i have used a dummy for loop,as i am using this for loop i cant give the for each loop property Transaction Option as required...but i need to rollback if any task fails...i have a screen shot of the for each loop





please give me some ideas on this.

Thanks in advance

View 1 Replies View Related

ROLLBACK TRANSACTION

Jul 10, 2006

Hi everyone,
In the following T-SQL code snippet,when I attempt to insert more than one record I encounter an error because of the following trigger but I have some doubts about the performation of ROLLBACK TRANSACTION in here. So there is only one transaction occured in the specified table that contain both trigger and the specified table however I supposed that when we call ROLLBACK TRANSACTION, the transaction is aborted. But the code run the Raiserror statement which is located in after the ROLLBACK TRANSACTION statement. So why do this code( Raiserror) is run ? Should not it be terminated because of the ROLLBACK TRANSACTION statement ??

ALTER TRIGGER kimlikNo_Degistir
ON Bilgi FOR INSERT
AS
IF(SELECT COUNT(*) FROM Inserted) > 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('You are not allowed to enter more than one record',12,1)
END
ELSE
BEGIN
UPDATE M SET M.KimlikNo = B.Kimlk + M.KimlikNo FROM Muhasebe as M INNER JOIN Inserted AS B ON M.Ad = B.Ad
END


Thanks

View 8 Replies View Related

Rollback Last Transaction!

Dec 14, 2007

I made a mistake and ran a delete query that I should not have. There have been no other transactions/modifcations to the database since my delete query.

Can I roll this back from the log file?

Please help as soon as you can!

THANKS!!

View 7 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

Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?

Jul 20, 2005

Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?

View 1 Replies View Related

Transaction Rollback Is Used Before It Is Assigned A Value

Apr 6, 2006

I am moving some code that was created in visual studio 2002 into Visual Web Developer 2005 express edition and am getting a warning on some of my code before compilation.
The warning is "Variable lSqlTransaction is used before it has been assigned a value.  A null reference exception could result at runtime".  I experienced no problems in vs2002.
The offending line is the rollback command within the exception.  If I move it out of the exception it no longer flags the warning, if I move it after the throw statement it now longer flags the warning.  Any help appreciated.
Heres the code
  Public Shared Sub ExecuteNonQuery(ByVal lSQLCommand As SqlCommand)

Dim lSQLConnection As New SqlConnection(SQLConnString(enmSQLDB.enmSQLDB_TPSDB))
Dim lSQLTransaction As SqlTransaction

Try
'-----------------------------------------------------
'Try and open the database
'-----------------------------------------------------
lSQLConnection.Open()

'-----------------------------------------------------
'Create a transaction
'-----------------------------------------------------
lSQLTransaction = lSQLConnection.BeginTransaction

'-----------------------------------------------------
'Assign the connection and transaction to our object
'-----------------------------------------------------
lSQLCommand.Connection = lSQLConnection
lSQLCommand.Transaction = lSQLTransaction

'-----------------------------------------------------
'Execute the stored proc
'-----------------------------------------------------
lSQLCommand.ExecuteNonQuery()

'-----------------------------------------------------
'Use the transaction to commit the changes
'-----------------------------------------------------
lSQLTransaction.Commit()

Catch ex As Exception
'-----------------------------------------------------
'If any errors have been thrown then roll back without
'commiting....
'-----------------------------------------------------
lSQLTransaction.Rollback()

Throw New Exception("An error has occured whilst trying to update the database. " & _
"Your changes have not been saved.", ex)


Finally
If lSQLConnection.State = ConnectionState.Open Then
lSQLConnection.Close()
End If
End Try

End Sub
 

View 1 Replies View Related

Rollback Transaction .. Error

Nov 20, 1998

DECLARE @A INT, @B INT,@C INT
SELECT @A=2,@B=3

SELECT @C=@A*@B

SELECT @C

BEGIN TRANSACTION A
SELECT @A
SELECT @B
SELECT @C
END TRANSACTION A

THIS CODE PROCDUE the following errors
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'END'.

Can anyone help me telling me what I am doing wrong ?

thanks

Ali

View 1 Replies View Related

Transaction Not Commited Rollback?

Jul 23, 2005

In VB 6, using ADO, SQL Server 2000In my VB program I use the connection object (abc)to begin a transaction.abc.BeginTransI then, in my VB App, using ADO, execute multiple stored proceduresinserting records into temp tables ##Meetings,reading them, and then finnaly executing (from my VB appusing ADO) as stored procedure that inserts recordsinto a non-temp table.I then have a abc.CommitTransWhat if my VB program crashes before thecommittrans is executed, I assume that the transactionis rolled back, even though no explicit rollbackis issued, because my VB program has crashed.Thank You for your help,Laurence NuttallProgrammer Analyst IIIUCLA - Division of Continuing Education

View 1 Replies View Related

Transaction Rollback Issues

Aug 14, 2006

Hi

We are loading data from one extract file in to 4 different database tables. When we are loading if the first table gets loaded without any issues and the second table loading gives any issues, we need to rollback the data loaded in to the first table also.

In the package for achieving this, we are using Sequence container. In the sequency container we are having 4 different tasks for loading data to 4 different tables from the same extract file. We also tried setting the different transaction options given in the properties box of the packages, container and tasks.

Also we tried the properties by setting as mentioned in the Test# 7 and 8 in the following URL

http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp


Could you please explain what properties we need to give.


Thanks
Kumaran

View 8 Replies View Related

Rollback SQL/Oracle Transaction

May 7, 2007

Hello ,

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?



Thanks.

View 4 Replies View Related







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