Why is this rollback needed when using sp_addextendedproperty in a stored procedure?
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