March 26, 2004 at 8:16 pm
Folks,
I've always understood that the BEGIN/COMMIT TRANSACTION statements are only meant for changes to data -- not for schema changes. But whenever I modify a table structure with Enterprise Manager and then (before saving the change) view the script that MSSQL creates, it alwayswraps it in BEGIN TRANSACTION / COMMIT TRANSACTION.
For instance, if I have a table called myTable, and I use Enterprise Manager to add a field called intTemp to it, the script it generates includes this snippet.
BEGIN TRANSACTION
ALTER TABLE dbo.myTable ADD
intTemp int NULL
GO
COMMIT
So, am I missing something? There are certainly times when our team needs to run some big scripts to update the structure of many DBs to keep their structures in synch...and if one fails midway, it would be great to have them ALL rolled back.
Thanks in advance,
- Tom
March 27, 2004 at 2:59 am
use pubs
go
create table test (c1 int)
begin tran
alter table test add c2 int
sp_help test
rollback
sp_help test
go
drop table test
March 29, 2004 at 9:54 am
Thanks!
After fiddling with it, I did have to make a minor change in your example code, but you gave me exactly what I was asking for. Much appreciated.
I'll put in the change for others to enjoy...
use pubs
go
create table test (c1 int)
begin tran
alter table test add c2 int
exec sp_help test
rollback
exec sp_help test
go
drop table test
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply