to commit or not to commit - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

to commit or not to commit

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-14 : 05:23:42

i have an sql string which looks like:
"insert into....; update...; insert...; update...; etc "
it's just a bunch of inserts and updates.

i run it from ADO (not .net) and the thing i'm wondering is this a single transaction or not?
i'm not really fast enough to somehow cancel it manually to see...

Go with the flow & have fun! Else fight the flow

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-09-14 : 06:55:18

Can you check from profiler?

steve

Facts are meaningless. You could use facts to prove anything that's even remotely true!

Kristen
Test

22859 Posts

Posted - 2005-09-14 : 06:57:11

Depends .... but probably separate transactions (what's the implicit transaction setting in ADO?)

If you aren't bothered about error checking in the middle you could do worse than just putting BEGIN TRANSACTION; at the start and COMMIT; at the end couldn't you?

I think a syntax error (or deadlock or whatever) will still spoil your day though ... the BEGIN TRANSACTION will increment the transaction level. An error will cause a ROLLBACK (as the next statement is attempted), and then the rest will run without any transaction in place [or a new one if there is an implicit transaction]. And then the COMMIT will fail because there was no matching BEGIN TRANSACTION.

I'd recommend using an SProc

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-14 : 07:20:13

sproc isn't an option... saddly...
if nothing else i'll do
set xact_abort
begin tran
stuff
commit

that should do it.
thanx.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-14 : 07:23:20

is xact_abort undocumented?
What will it do?

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-14 : 07:31:27

SET XACT_ABORT in bol is nicly explained