Begin-Commit Transaction

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Begin-Commit Transaction

Begin-Commit Transaction

Rate Topic

Display Mode

Topic Options

Author

Message

sachinthamke

Posted Wednesday, June 18, 2008 5:53 AM

Forum 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

Pradyothana Shastry

Posted Wednesday, June 18, 2008 6:29 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, March 25, 2013 5:30 AM
Points: 1,640, Visits: 423

Use in stored procedure only. Once you complete your transaction commit it.

- Pradyothana

http://www.msqlserver.com

Post #518930

Grant Fritchey

Posted Wednesday, June 18, 2008 7:38 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:57 PM
Points: 13,371, Visits: 25,152

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

bledu

Posted Wednesday, June 18, 2008 2:47 PM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, February 05, 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

sachinthamke

Posted Wednesday, June 18, 2008 11:01 PM

Forum 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

ChiragNS

Posted Wednesday, June 18, 2008 11:53 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

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

Mahesh Bote

Posted Thursday, June 19, 2008 2:29 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

sachinthamke (6/18/2008)

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

vnagwan

Posted Thursday, June 19, 2008 5:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 07, 2008 5:15 AM
Points: 3, Visits: 51

Use identity variable to keep the relation intact, :)

happy programming!!

Post #519739

« Prev Topic | Next Topic »

Permissions