Begin-Commit Transaction

Question

Post reply

Begin-Commit Transaction

sachinthamke

SSC Enthusiast

Points: 152
More actions
June 18, 2008 at 5:53 am

#96320

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Pradyothana Shastry SSCertifiable Points: 5211 More actions · Answer 1

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

- Pradyothana http://www.msqlserver.com

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

bledu SSChampion Points: 12335 More actions · Answer 3

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.

sachinthamke SSC Enthusiast Points: 152 More actions · Answer 4

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

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"

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 6

[font="Verdana"]

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[/font]

MH-09-AM-8694

vnagwan Grasshopper Points: 11 More actions · Answer 7

Use identity variable to keep the relation intact, 🙂

happy programming!!