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 |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2006-03-22 : 03:24:01
|
| Hi,In the stored procedure, the sql statements are being built as a string and then executed using exec sp_sqlexec @sqlThe sp works fine if I do not use the transaction but as soon as I run it with the transactions then the sp just continues executing without stopping.I wonder if transactions can not be used with this kinid of sql being built (i.e. dynamic sql)P.S. At this moment I would like to use the dynamic sql.Thanksfor example:begin transaction begin try set @sql = 'truncate table ' + @Database + '..' + @DestinationTable commit transaction end trybegin catch rollback transactionend catch |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2006-03-22 : 03:33:18
|
I am assuming that you have left out the sp_sqlexec purposefully in your code and it actually really is there when you run it  It might be that the try..catch logic should be included in the dynamic sql in order for it to work properly...You must remember that when using sp_sqlexec - it is executed in a new batch.That's just my thoughts - perhaps you should just try it  Duane. |
 |
|
|
|
|
|
transaction