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.
| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2006-09-25 : 12:37:32
|
| What I'm trying to do is create a procedure and a job within a transaction, but I want to make sure the procedure is not created if the job creation fails. I tried a test like this but it left the db in a nasty state (I couldn't even view the procs in the db in EM till I closed the connection):BEGIN TRANGO --needed because "create proc" must be first statement in batch CREATE PROC test_delete AS select * from table1 --this table existsGO-- the following will be job creation in final script CREATE PROC test_delete2 AS select * from table2 --this table doesn't existCOMMIT TRANDo I need to explicitly do a rollback and then it will work? If I shouldn't use GO, how do I separate the proc creation from the job creation? |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-25 : 12:47:59
|
| It sounds like when you tried it you never executed the COMMIT, so you left the transaction open. You must either execute the COMMIT - if you want the change to be saved, or execute a ROLLBACK - if you want to lose the change. After starting the transaction with BEGIN TRAN you must commit or rollback otherwise that transaction is left open and will cause the blocking you saw. |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2006-09-25 : 12:54:46
|
| So there's never any automatic rollback when an error occurs inside a transaction? Do I need to use GO to separate the CREATE statements? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-25 : 13:06:03
|
| Some things will cause a rollback, but usually you need to detect the error and rollback yourself.You have to put the GO between certain CREATE statements. I'm not sure if you're trying to put those CREATE PROC statements inside another stored procedure? If so you cannot put the GO statements in there because you can't put GO in a stored procedure. You'd have to use dynamic SQL to do that, but you almost never want to be creating stored procedures inside other stored procedures so I'll assume for now you don't want to do that. |
|
|
|
|
|
|
transaction confusion