5

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

1 Answer 1

3

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

1
  • 1
    I get XACT_STATE() = 1 on both Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) and on Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86) Sep 16 2010 20:09:22 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
    – bernd_k
    Apr 10, 2011 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.