Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Begin-Commit Transaction Expand / Collapse
Author
Message
Posted Wednesday, June 18, 2008 5:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 30, 2012 1:09 AM
Points: 6, Visits: 23
Hi
I have two procedures one is master insert, detail insert.

So in this where i m suppose to user the Begin-Commit Transaction in front end or backend.

Currently i m calling these procedures separately from front end (vb.net)


Sachin Thamke
Mumbai, India
Post #518907
Posted Wednesday, June 18, 2008 6:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 5, 2014 9:54 PM
Points: 1,687, Visits: 451
Use in stored procedure only. Once you complete your transaction commit it.


- Pradyothana


http://www.msqlserver.com
Post #518930
Posted Wednesday, June 18, 2008 7:38 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 15,725, Visits: 28,130
Different people do it different ways. You can control the transaction from the application, but you need to be very cautious about getting extraneous code in the way of the transaction, holding locks & blocks open longer on the database than you need to. You can also control the transaction through the stored procedures, but in this case, I'd recommend you create a wrapper proc that calls each of the other two in the correct order within a transaction.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #519012
Posted Wednesday, June 18, 2008 2:47 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 4:24 AM
Points: 685, Visits: 104
i'd say control the transactions from the app. you may want to consider using xml to send your header and details in an xml string and then do your stuff in the stored proc.


Everything you can imagine is real.

Post #519450
Posted Wednesday, June 18, 2008 11:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 30, 2012 1:09 AM
Points: 6, Visits: 23
Thanks for u r replay

I m using the stored procedure for storing usp_InsertMaster,usp_InsertDetail. the detail sp used to store the items for sending the XML parameters there is a limit of 8000 char.
What's why i can not create the wrapper procedure e.g.







Sachin Thamke
Mumbai, India
Post #519580
Posted Wednesday, June 18, 2008 11:53 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:55 AM
Points: 2,366, Visits: 1,845
Hi

I have'nt understood your point... but if 8000 char is a limitation you can use xml data type or Varchar(MAX) .


"Keep Trying"
Post #519591
Posted Thursday, June 19, 2008 2:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:57 AM
Points: 1,093, Visits: 1,222
sachinthamke (6/18/2008)
Thanks for u r replay

I m using the stored procedure for storing usp_InsertMaster,usp_InsertDetail. the detail sp used to store the items for sending the XML parameters there is a limit of 8000 char.
What's why i can not create the wrapper procedure e.g.


Sachin, it seems that you are inserting data in Parent and Child table as well. then why don't you merge the two Insert statements into single Sproc so that you can easily control the transaction. And you don't need to make one more call the DB as well. In a single call you can do all the stuff.

Mahesh


MH-09-AM-8694
Post #519664
Posted Thursday, June 19, 2008 5:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 7, 2008 5:15 AM
Points: 3, Visits: 51
Use identity variable to keep the relation intact, :)

happy programming!!
Post #519739
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse