MS SQL RSS MS SQL RSS

commiting inner query while outer transaction is rollbacked

Please support our MS SQL advertiser: Intel Parallel Studio Home
Thread Solved

•
•
Join Date: May 2009
Posts: 5
Reputation: ankushbansal89 is an unknown quantity at this point 
Solved Threads: 0
ankushbansal89 ankushbansal89 is offline Offline
Newbie Poster

commiting inner query while outer transaction is rollbacked

 
0
  #1
Jun 26th, 2009
I have written a complex stored procedure which have a log table which keeps the track of success/failure of each and every query in the stored procedure. If there is any error in any of the query entire transaction is rollback . while rollingback, data in log table is also rollback .so if anybody plz suggest a way to commit the log file while rollbacking of outer loop.



MS SQL Syntax (Toggle Plain Text) 
  1. CREATE PROCEDURE TEST
  2. AS
  3.  BEGIN TRY
  4.  BEGIN TRANS T1
  5.  UPDATE TABLE1 SET MyColumn = 'ABC'
  6. -- SOME QUERY
  7. --SOME QUERY
  8. --SOME QUERY 
  9.  
  10.  
  11.  BEGIN TRANS T2
  12.  
  13.  --UPDATE IN LOG TABLE 
  14.  
  15.  
  16.  COMMIT TRANS T2
  17.  
  18. --SOME QUERY
  19.  
  20. --SOME QUERY
  21.  
  22. END TRY
  23.  
  24. BEGIN CATCH
  25.  
  26.  ROLBACK TRANS T1
  27.  
  28. END CATCH
  29.  
  30.  COMMIT TRANS T1
  31. GO


thanks in advance
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 1
Reputation: jaivinoth1782 is an unknown quantity at this point 
Solved Threads: 1
jaivinoth1782 jaivinoth1782 is offline Offline
Newbie Poster

Re: commiting inner query while outer transaction is rollbacked

 
0
  #2
Jun 26th, 2009
I am having Two Tables Category,and Product Tables

Catid is the Primary KEY for cat,

I want to count the Product List
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2009
Posts: 3,207
Reputation: sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of 
Solved Threads: 571
Sponsor
sknake's Avatar
sknake sknake is online now Online
.NET Enthusiast

Re: commiting inner query while outer transaction is rollbacked

 
0
  #3
Jun 26th, 2009
Try putting your log query in exec('query here') and see if that rolls back. I think that may be a work around but I haven't tested it before.
Scott Knake
Custom Software Development
Apex Software, Inc.
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2009
Posts: 5
Reputation: ankushbansal89 is an unknown quantity at this point 
Solved Threads: 0
ankushbansal89 ankushbansal89 is offline Offline
Newbie Poster

Re: commiting inner query while outer transaction is rollbacked

 
0
  #4
Jun 29th, 2009
•
•
•
•
Originally Posted by sknake View Post
Try putting your log query in exec('query here') and see if that rolls back. I think that may be a work around but I haven't tested it before.
Thank for the reply ...
but ur idea is not working ...
i got the another solution by using goto and label ...
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2009
Posts: 3,207
Reputation: sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of 
Solved Threads: 571
Sponsor
sknake's Avatar
sknake sknake is online now Online
.NET Enthusiast

Re: commiting inner query while outer transaction is rollbacked

 
0
  #5
Jun 29th, 2009
I'm glad you found a solution to your problem and good luck!

Please mark this thread as solved if you have found an answer to your question.
Scott Knake
Custom Software Development
Apex Software, Inc.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MS SQL Forum
Thread Tools Search this Thread



connectingtodatabaseinuse count cursor database datepart deadlock delete_trigger highperformancecomputing hpc hpcserver2008 ibm iis loop maximum microsoft ms multithreading news number permission query result server sets sql sqlserver sqlserver2005 supercomputing uniqueid update view
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC