October 21, 2009 at 1:54 pm
Hi
I want to create a transactional DDL script that alters a set of dependent objects such as views and stored procedures
because CREATE PROC and CREATE VIEW *have* to be the first statement in a batch
the best solution within SQL seems to be
set xact_abort on
begin tran
exec('alter view ...')
exec('alter view ...')
exec('alter view ...')
exec('alter view ...')
commit tran
whilst experimenting I came across this monster gotcha:
set xact_abort on
go
begin tran
go
exec('alter view 1')
go
select 1 / 0
go
exec('alter view 2')
go
exec('alter view 3')
go
rollback tran
go
that is nasty - alter view 1 will rollback
but alter view 2 and 3 will be committed
and rollback tran will throw an error
also this is pretty nasty:
--START BATCH--
create proc dbo.spBlah
as
select getdate()
delete from sales
--END BATCH--
exec sp_helptext spBlah
will now return:
create proc dbo.spBlah
as
select getdate()
delete from sales
October 21, 2009 at 3:25 pm
First issue:
The behaviour you are seeing is due to SQL Server's error handling features.
From information available in http://www.sommarskog.se/error-handling-I.html and http://www.sommarskog.se/error-handling-II.html
Since you are setting XACT_ABORT - most statement abort errors become batch abort errors. A divide-by-zero error is normally a statement abort error but since XACT_ABORT is used this becomes a batch abort error. Also if an error is raised then the current transaction is automatically rolled back since XACT_ABORT is set.
And since you are using GO as a batch separator - each statement becomes a batch by itself.
So the divide by zero errors out and the transaction is rolled back - and since the divide by zero is the only statement in the batch that particular batch (SELECT 1/0) is aborted.
Control then goes to the next batch - altering views 2 and 3. Then the transaction rollback errors out as the XACT_ABORT has already rolled back the transaction and there is nothing to rollback.
One way to change this:
-- statements after error execute
SET XACT_ABORT ON
GO
BEGIN TRAN
GO
SELECT 100
GO
SELECT 1/0
GO
SELECT 200 -- this will be part of the output
GO
WHILE (@@TRANCOUNT > 0)
ROLLBACK TRAN;
GO
-- statements after error don't execute
SET XACT_ABORT ON
GO
BEGIN TRY
BEGIN TRAN
SELECT 100
SELECT 1/0
SELECT 200 -- doesn't come here - jumps to catch block on error in previous statement
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
WHILE (@@TRANCOUNT > 0)
ROLLBACK TRAN;
END CATCH
GO
Second issue:
Unless you select and execute only the part of the code that you want to put into the stored procedure or use a GO to indicate that everything before the GO is part of the stored procedure body everything after the CREATE PROCEDURE will be assumed to be part of the stored procedure.
Best practise is to add the GO after each CREATE/ALTER for objects:
create proc dbo.spBlah
as
select getdate();
GO
delete from sales;
sp_helptext dbo.spBlah;
October 22, 2009 at 9:39 am
Thanks for the detailed description of those gotchas
Did you have any thoughts on creating a transactional script with multiple CREATE/ALTER VIEW/PROC/TRIGGER/FUNCTION statements?
Is the exec('alter view') approach a good one / the only one?
begin try
begin tran
exec('alter view 1')
exec('alter view 2')
exec('alter view 3')
exec('alter view 4')
commit tran
end try
begin catch
rollback tran
end catch
October 22, 2009 at 9:47 am
Please see <<< questions below ...
SET XACT_ABORT ON <<< will this affect try catch whether ON or OFF?
GO
BEGIN TRY
BEGIN TRAN
SELECT 100
SELECT 1/0
SELECT 200 -- doesn't come here - jumps to catch block on error in previous statement
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
WHILE (@@TRANCOUNT > 0) <<< is this necessary? I thought rollback tran *always* leave @@trancount at 0 (since sql 2005)
ROLLBACK TRAN;
END CATCH
GO
October 23, 2009 at 12:06 am
You are right - the WHILE(@@TRANCOUNT>0) is not needed...I did not know that ROLLBACK TRANSACTION decremented the transaction count to 0 even with nested transactions...
I am not too sure about whether the XACT_ABORT settings affects the behaviour of TRY...CATCH - I haven't seen any changes to TRY/CATCH behaviour whenever I've used it...
October 23, 2009 at 1:40 am
Regarding the script to create/alter objects - I normally tend to have a script that checks if the object exists, drop it if it exists and then create it. Sort of like what is generated when objects are scripted from SQL Server.
I don't wrap up these scripts in a transaction.
And if dropping and creating objects all the appropriate permissions would have to be re-applied (as opposed to an alter).
The script would be something like:
IF OBJECT_ID('MyTable') IS NOT NULL
DROP TABLE MyTable;
GO
CREATE TABLE MyTable
...
GO
IF OBJECT_ID('MyView') IS NOT NULL
DROP VIEW MyView;
GO
CREATE VIEW MyView
...
GO
-- apply object specific permissions etc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply