Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_extended_logging

 

Stored procedure that is no longer available in SQL 2008. sp_extended_logging modifies the extended logging status for a given table. This can be handfull if you have difficulties to setup replication.




Syntax
create procedure sys.sp_extended_logging
(
    @table sysname      -- table or indexed view
    ,@enable bit = 1    -- 0 = disable, 1 = enable
)
as
begin
    set nocount on
    declare @colname sysname
                ,@dbname sysname
                ,@object_id int
                ,@tran_replicated bit
                ,@tran_filtered bit
                ,@active tinyint
                ,@initiated tinyint
                ,@cmd nvarchar(1024)
                ,@retcode int
                ,@system_type_id int
                ,@user_type_id int
                ,@action nvarchar(1000)
                ,@raised_error int
                ,@raised_message nvarchar(4000)
                ,@trancount int

    -- sku check - only for enterprise/developer/standard
    if (sys.fn_MSrepl_editionid () not in (30,31,20))
    begin
        declare @edition sysname
        select @edition = CONVERT(sysname, SERVERPROPERTY('Edition'))
        raiserror(22988, 16, -1, @edition)
        return 1
    end


    -- security check - should be dbo or sysadmin

    exec @retcode = sp_MSreplcheck_publish
    if @@ERROR != 0 or @retcode != 0
        return 1

    set @raised_error = 0
    set @object_id = object_id(@table)

    -- make sure the object exists and corresponds to a table or indexed view
    if (not exists (
        select * from sys.objects as o left outer join sys.indexes as i on o.object_id = i.object_id
            where o.object_id = @object_id and (o.type = N'U' or o.type = N'V' and i.object_id is not null)
        ))
    begin
        raiserror (N'''%s'' does not exist or is not a table or indexed view', 16, -1, @table)
        return 1
    end


    -- show a warning to trace this ad-hoc activity

    declare @enable_int int
    select @enable_int = convert(int, @enable)
    raiserror(N'Info: Attempting to set extended logging for ''%s'' to ''%d''', 10, 1, @table, @enable_int) with log

    -- Wrap transaction processing in a TRY/CATCH block
    BEGIN TRY

       set @trancount = @@trancount

       begin tran
        save tran sp_extended_logging

        select @tran_replicated = 0
                ,@tran_filtered = 0
                ,@active = 2
                ,@initiated = 3

        --lock the table before checking and making metadata change
        set @action = N'LockMatchID'
        exec %%Object(MultiName = @table).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)

        --check to see if this object is being replicated by tran replication
        if (@enable = 0 and object_id('sysarticles') is not null and object_id('syssubscriptions') is not null)
        begin
            --determine if table has active tran repl subscription
            if exists (select * from sysarticles A join syssubscriptions S on A.artid = S.artid where A.objid = @object_id and S.status = @active)
            begin
                set @tran_replicated = 1
            end

            --determine if table needs has_replication_filter set for tran replication
            if exists( select * from sys.objects where type = 'RF' and parent_object_id = @object_id )
                or exists( select * from sysarticles A join syspublications P on A.pubid = P.pubid where A.objid = @object_id and (P.allow_queued_tran = 1 or P.allow_sync_tran = 1))
                or exists( select * from sysarticles where objid = @object_id and (upper(upd_cmd) like 'CALL%' OR upper(upd_cmd) like 'XCALL%' ) )
                or exists( select * from sysarticles where objid = @object_id and (upper(del_cmd) like 'XCALL%' ) )
                or exists( select * from sysarticles A join syssubscriptions S on A.artid = S.artid where S.status = @initiated)
            begin
                set @tran_filtered = 1
            end

        end

        -- Set the database published bit if we are enabling so sp_repldone can be called
 -- If we are disabling, attempt to make sure the database is not published
       if(@enable = 1 or (object_id(N'dbo.syspublications') is null and
            not exists(select * from sys.objects where is_published=1 and object_id != @object_id)))
       begin
           set @action = N'SetPublished'
           select @dbname = db_name()
           exec %%DatabaseEx(Name = @dbname).SetPublished(Value = @enable)
       end

        --enable, do it anyway regardless of tran repl
        --disable, only do it when tran repl does not need is_replicated anymore
        if(@enable = 1 or @tran_replicated = 0)
        BEGIN
            set @action = N'SetReplicated'
            EXEC %%Relation(ID = @object_id).SetReplicated(Value = @enable)

            DECLARE #hC CURSOR LOCAL FAST_FORWARD FOR
            SELECT DISTINCT name, system_type_id, user_type_id from sys.columns
            where  object_id = @object_id
            OPEN #hC
            FETCH #hC into @colname, @system_type_id, @user_type_id
            WHILE (@@fetch_status <> -1)
            begin
                set @action = N'SetReplicated'
                EXEC %%ColumnEx(ObjectID = @object_id, Name = @colname).SetReplicated(Value = @enable)

                --image, text. ntext
                if (@system_type_id not in (34, 35, 99) and @user_type_id not in  (34, 35, 99)) --image, text. ntext
                begin
                    set @action = N'SetLogForRepl'
                    EXEC %%ColumnEx(ObjectID = @object_id, Name = @colname).SetLogForRepl(Value = @enable)
                end

                FETCH #hC into @colname, @system_type_id, @user_type_id
            end
            CLOSE #hC
            DEALLOCATE #hC

        END

        --enable, do it anyway regardless of tran repl
        --disable, only do it when tran repl does not need has_replication_filter set anymore
        if(@enable = 1 or @tran_filtered = 0)
        BEGIN
            set @action = N'SetHasFilterProc'
            EXEC %%Relation(ID = @object_id).SetHasFilterProc(Value = @enable)
        END

        if(@enable = 0 and @tran_replicated = 1 and @tran_filtered = 0)
        begin
            set @action = N'sp_MSSetLogForRepl'
            exec @retcode = sp_MSSetLogForRepl @object_id
        end

        commit tran
    END TRY

    BEGIN CATCH

        if @@trancount > @trancount
        begin
            if XACT_STATE() = 1
            begin
                rollback tran sp_extended_logging
                commit tran
            end
        end

        set @raised_error = ERROR_NUMBER()
        set @raised_message = ERROR_MESSAGE()

    END CATCH

    if @raised_error = 0
        return 0

    raiserror(N'Could not modify the the extended logging status for table %s. The failure occurred when executing the command ''%s''. The error returned was %d: ''%s''. Use the action and error to determine the cause of the failure and resubmit the reque
st.', 16, -1, @table, @action, @raised_error, @raised_message)
    return 1
end

 
Last revision
See also

  sp_MSdrop_repltran (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash