Sql-server – Why is this rollback needed when using sp_addextendedproperty in a stored procedure

sql servertransaction

In enter link description here I showed, how I document databases.

To insert the extended properties, initially I uses plan sequences of sp_addextendedproperty calls.

But lately I wanted to catch the errors about not existing objects when I run the script on an older version of the database.

I found the following working code by trial and error

begin try 
drop procedure dbo.BK_add_Tableproperty
end try begin catch end catch

go
create procedure dbo.BK_add_Tableproperty (
    @table_name sysname,
    @property_name nvarchar(max),
    @Property_value sysname
)
as  
    declare @error integer;
begin try
    EXEC sys.sp_addextendedproperty @name= @property_name,
    @value = @Property_value, 
    @level0type=N'SCHEMA',
    @level0name=N'dbo', 
    @level1type=N'TABLE',
    @level1name=@table_name
end try begin catch
    SET @error = @@ERROR;
    if @error = 15135
        Print 'Table missing no extended properties added: ' + @table_name
    else if @error = 15233
        Print 'Table has already property: ' + @table_name + ' - ' + @property_name
    else
        Print 'unexpected error ' + str(@error)          
    ROLLBACK;  
end catch

go

What me confuses, is the fact, that I have to add a ROLLBACK to the catch path. At msdn I found no hint, that sp_addextendedproperty uses transactions. Can anyone explain this?

Answer to gbn:

But return exits unconditionally the procedure. I see only 1 commit in each path:

BEGIN TRANSACTION

begin
    EXEC %%ExtendedPropertySet().AddValue(Name = @name, Value = @value, Level0type = @level0type, Level0name = @level0name, Level1type = @level1type, Level1name = @level1name, Level2type = @level2type, Level2name = @level2name)
    IF @@error <> 0
    begin
        COMMIT TRANSACTION
        return (1)
    end
end

COMMIT TRANSACTION
return (0)

Reported as bug to Microsoft connect:

https://connect.microsoft.com/SQLServer/feedback/details/658556/sp-addextendedproperty-leaves-on-open-transaction-when-an-error-occured

Improved version (better error handling and fixed parameter type.
(the original problem is unaffected, but this handels NLS in erropr messages)

create procedure dbo.BK_add_Columnproperty (
    @table_name sysname,
    @Column_name sysname,
    @property_name nvarchar(max),
    @Property_value sql_variant
)
as  
begin try
    --SET XACT_ABORT ON
    EXEC sys.sp_addextendedproperty @name= @property_name,
    @value = @Property_value, 
    @level0type=N'SCHEMA',
    @level0name=N'dbo', 
    @level1type=N'TABLE',
    @level1name=@table_name,
    @level2type=N'COLUMN',
    @level2name=@Column_name
end try begin catch
    -- SELECT XACT_STATE()    -- returns 1 or -1 when SET XACT_ABORT ON 
    if ERROR_NUMBER() = 15135 -- object invalid
        print '      ' + str(ERROR_NUMBER()) + ' -- ' + ERROR_MESSAGE() 
    else
        print '##### ' + str(ERROR_NUMBER()) + ' -- ' + ERROR_MESSAGE() 
    ROLLBACK;  
end catch

go

Best Answer

This line..

SELECT OBJECT_DEFINITION(OBJECT_ID('sp_addextendedproperty'))

...shows that sp_addextendedproperty has 2 x COMMIT. This won't be a problem if the transaction can be COMMITed (i.e. isn't "doomed") but will leave a transaction open otherwise.

This could well be a bug to be reported on MS Connect

A catch block like this will show if the transaction is doomed

begin catch

SELECT XACT_STATE() -- -1 = doomed, 0 = none, 1 = commit or rollback

    SET @error = ERROR_NUMBER();

    if @error = 15135
        Print 'Table missing no extended properties added: ' + @table_name;
    else if @error = 15233
        Print 'Table has already property: ' + @table_name + ' - ' + @property_name;
    else
        Print 'unexpected error ' + str(@error); 

end catch

I've not seen this (an open txn) at work (still SQL Server 2005 SP3) and got the details from a local SQL Server 2008 on a VM. Could be version related.

Edit: just seen the Q update

Using SET XACT_ABORT ON will force a rollback anyway

Related Question