Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_trigger_exec_rpc

  No additional text.


Syntax
create procedure sys.sp_MSscript_trigger_exec_rpc
(
    @publisher   sysname,
    @publisherdb sysname,
    @publication sysname,
    @procname   sysname,
    @proc_owner sysname,
    @cftproc    sysname,
    @objid       int,
    @op_type     char(3) = 'ins',
    @indent      int = 0,
    @identity_col sysname = null,
    @ts_col       sysname = null,
    @primary_key_bitmap   varbinary(4000),
    @pubversion int
)
as
BEGIN
    set nocount on
    declare @cmd nvarchar(4000)
                ,@min_identity int
                ,@max_identity int
                ,@fisqueued bit
                ,@varindent int
                ,@object_owner sysname

    select @fisqueued = case when (@cftproc is NULL) then 0 else 1 end
            ,@object_owner = schema_name(ObjectProperty(@objid, 'SchemaId'))
    
    -- Set version guid param and column first.
    
    if @op_type in ('upd')
    begin
        exec sys.sp_MSscript_trigger_version_updates @objid, 'msrepl_tran_version', @indent,
                    @primary_key_bitmap
    end
    
    -- continue scripting
    
    select @cmd = N'
        if (@update_mode_id = 1) or (@update_mode_id in (3, 5) and @failover_mode_id = 0)
        begin
            ' + N'
            ' + N'-- immediate mode
            ' + N'-- '
    insert into #proctext(procedure_text) values (@cmd)
    
    -- prepare the proc invocation
    -- 1st two variables are subscriber server name and database name for cycle detection
    
    select @cmd = N'
            select @rpc_proc = @connect_string + N''.'' + ' + N'N''' +
                sys.fn_replreplacesinglequote(quotename(@proc_owner)) collate database_default + N'.' +
                sys.fn_replreplacesinglequote(quotename(@procname)) collate database_default + N'''
            exec @retcode = @rpc_proc @subscriber, @subscriber_db,
                        '
    insert into #proctext(procedure_text) values (@cmd)
    
    -- script the trigger variables
    
    select @min_identity = @@identity
    if @op_type in ('ins', 'upd')
        exec sys.sp_MSscript_trigger_variables @objid, '@', N'', @indent, N'', 0,  @identity_col, @ts_col

    if @op_type = 'upd'
    begin
        exec sys.sp_MSscript_trigger_variables @objid, '@', '_old', @indent, ',', 0, null, null
        insert into #proctext(procedure_text) values (N'
                        ,@bitmap')
    end
    else if @op_type = 'del'
        exec sys.sp_MSscript_trigger_variables @objid, '@', '_old', @indent, ' ', 0, null, null

    select @max_identity = @@identity
    
    -- continue scripting
    
    select @cmd = N'
            if (@@error != 0 or @retcode != 0)
            begin
                if (@retcode = -2)
                    exec sys.sp_MSreplraiserror 21064
                else if @retcode = 5
                    exec sys.sp_MSreplraiserror 20515
                else
                    exec sys.sp_MSreplraiserror 21054
                    goto FAILURE
            end
        end'
    insert into #proctext(procedure_text) values (@cmd)
    
    -- queued specific processing
    
    if (@fisqueued = 1)
    begin
        declare @artidstring sysname
                    ,@qversion int
                    ,@foldqueuestyle bit

        
        -- Check the publisher version
        
        if (@pubversion = 1)
        begin
            
            -- Publisher is Pre80SP3
            -- Use the new style of parameter passing - Queue version 1
            
            select @foldqueuestyle = 1
        end
        else if (@pubversion > 1)
        begin
            
            -- Publisher is 80SP3 or later
            -- get the article id as string
            -- Use the new style of parameter passing - Queue version 2
            
            select @artidstring = cast(artid as sysname)
            from (dbo.MSsubscription_articles as a join dbo.MSsubscription_agents as b
                on a.agent_id = b.id)
            where UPPER(b.publisher) = UPPER(@publisher)
                and b.publisher_db = @publisherdb
                and b.publication = @publication
                and a.dest_table = object_name(@objid)
                and a.owner = @object_owner
            if (@artidstring is null)
            begin
                -- could not find a owner qualified article entry
                return 1
            end
            select @foldqueuestyle = 0
        end
        else
        begin
            
            -- bad publisher version
            
            return 1
        end
        
        -- Are we going old style - for backwards compatibility for SQL Queue
        -- For MSMQ we use a newer clsid for queue version and will
        -- use whatever is assigned to the queue during its creation
        -- A newer publisher will imply a newer distributor and we are
        -- okay.
        
        if (@foldqueuestyle = 1)
        begin
            
            -- Update the version on the sql queue to v1 if necessary
            
            update dbo.MSsubscription_agents
            set queue_id = 'mssqlqueue'
            where UPPER(publisher) = UPPER(@publisher)
                and publisher_db = @publisherdb
                and publication = @publication
                and update_mode in (4,5)
                and queue_id != 'mssqlqueue'
        end
        else
        begin
            
            -- Update the version on the sql queue to v2 if necessary
            
            update dbo.MSsubscription_agents
            set queue_id = 'mssqlqueuev2'
            where UPPER(publisher) = UPPER(@publisher)
                and publisher_db = @publisherdb
                and publication = @publication
                and update_mode in (4,5)
                and queue_id != 'mssqlqueuev2'
        end
        
        -- continue scripting
        
        select @cmd = N'
        else
        begin
            ' + N'
            ' + N'-- handle queued cases
            ' + N'
            if (@update_mode_id in (2,3))
            begin'
        insert into #proctext(procedure_text) values (@cmd)
        
        -- MSMQ based write
        
        if (@foldqueuestyle = 1)
        begin
            
            -- old style scripting - non secure
            
            select @cmd = N'
                exec @retcode = sys.sp_replsendtoqueue @queue_id, @tran_id, N' +
                quotename(@publication, '''') + N', N' +
                quotename(@cftproc, '''') + N', N' +
                quotename(@proc_owner, '''')    + N', N' +
                quotename(@procname, '''') + N', @subscriber, @subscriber_db,
'
        end
        else
        begin
            
            -- new style scripting - secure
            
            select @cmd = N'
                exec @retcode = sys.sp_replsendtoqueue @queue_id, @tran_id, N' +
                quotename(@publication, '''') + N', N' +
                quotename(@artidstring, '''') + N', N' +
                quotename(@op_type, '''')    + N', N' +
                quotename(@procname, '''') + N', @subscriber, @subscriber_db,
'
        end
        insert into #proctext(procedure_text) values (@cmd)
        
        -- script the trigger variables
        
        select @varindent = @indent + 8
                ,@min_identity = @@identity
        if @op_type in ('ins', 'upd')
            exec sys.sp_MSscript_trigger_variables @objid, '@', N'', @varindent, N'', 0,  @identity_col, @ts_col,
                        @no_output = 1
        if @op_type = 'upd'
        begin
            exec sys.sp_MSscript_trigger_variables @objid, '@', '_old', @varindent, ',', 0, null, null,
                        @no_output = 1
            insert into #proctext(procedure_text) values (N'
                    ,@bitmap')
        end
        else if @op_type = 'del'
            exec sys.sp_MSscript_trigger_variables @objid, '@', '_old', @varindent, ' ', 0, null, null,
                        @no_output = 1

        select @max_identity = @@identity
        
        -- SQL Queued case
        -- we loop here if the command length exceeds queue message length and
        -- break the command into partial cmds and send it in more than one
        -- queue message
        
        select @cmd = N'
            end
            else if (@update_mode_id in (4,5))
            begin
                select @partial_cmd = 1, @start_offset = 0, @end_offset = 0
                while (@partial_cmd != 0)
                begin'
        insert into #proctext(procedure_text) values (@cmd)

        if (@foldqueuestyle = 1)
        begin
            
            -- old style scripting - non secure
            
            select @cmd = N'
                    exec @retcode = sys.sp_replwritetovarbin @start_offset, @end_offset output, @vb_buffer output, @vb_bufferlen output, N' +
                    quotename(@publisher, '''')    + N', N' +
                    quotename(@publisherdb, '''')    + N', N' +
                    quotename(@publication, '''')    + N', @tran_id, N' +
                    quotename(@cftproc, '''')    + N', N' +
                    quotename(@proc_owner, '''')    + N', N' +
                    quotename(@procname, '''') + N', @subscriber, @subscriber_db,
'
        end
        else
        begin
            
            -- new style scripting - secure
            
            select @cmd = N'
                    exec @retcode = sys.sp_replwritetovarbin @start_offset, @end_offset output, @vb_buffer output, @vb_bufferlen output, N' +
                    quotename(@publisher, '''')    + N', N' +
                    quotename(@publisherdb, '''')    + N', N' +
                    quotename(@publication, '''')    + N', @tran_id, N' +
                    quotename(@artidstring, '''')    + N', N' +
                    quotename(@op_type, '''') + N', N' +
                    quotename(@procname, '''') + N', @subscriber, @subscriber_db,
'
        end
        insert into #proctext(procedure_text) values (@cmd)
        
        -- script the trigger variables
        
        select @min_identity = @@identity
        if @op_type in ('ins', 'upd')
            exec sys.sp_MSscript_trigger_variables @objid, '@', N'', @varindent, N'', 0,  @identity_col, @ts_col,
                        @no_output = 1
        if @op_type = 'upd'
        begin
            exec sys.sp_MSscript_trigger_variables @objid, '@', '_old', @varindent, ',', 0, null, null,
                        @no_output = 1
            insert into #proctext(procedure_text) values (N'
                    ,@bitmap')
        end
        else if @op_type = 'del'
            exec sys.sp_MSscript_trigger_variables @objid, '@', '_old', @varindent, ' ', 0, null, null,
                        @no_output = 1

        select @max_identity = @@identity
        
        -- continue scripting
        
        select @cmd = N'
                    if @@error != 0 or @retcode != 0
                    begin
                        exec sys.sp_MSreplraiserror 21052
                        goto FAILURE
                    end'
        insert into #proctext(procedure_text) values(@cmd)
        
        -- handle partial messages
        
        select @cmd = N'
                    select @partial_cmd = case when (@end_offset > 0) then 1 else 0 end
                    exec @retcode = sys.sp_MSsendtosqlqueue @@procid, N' +
                    quotename(@publisher, '''') + N', N' +
                    quotename(@publisherdb, '''') + N', N' +
                    quotename(@publication, '''') + N', N' +
                    quotename(@object_owner, '''') +
                    N', @tran_id, @vb_buffer, @vb_bufferlen, 1, @partial_cmd '
        insert into #proctext(procedure_text) values(@cmd)
        
        -- continue scripting
        
        select @cmd = N'
                    if @@error != 0 or @retcode != 0
                    begin
                        exec sys.sp_MSreplraiserror 21052
                        goto FAILURE
                    end
                    select @start_offset = @end_offset
                end
            end'
        insert into #proctext(procedure_text) values(@cmd)
        
        -- continue scripting
        
        select @cmd = N'
            if @@error <>0 or @retcode <> 0
            begin
                exec sys.sp_MSreplraiserror 21052
                goto FAILURE
            end
        end
    '
        insert into #proctext(procedure_text) values(@cmd)
    end
    
    -- all done
    
    return 0
END

 
Last revision SQL2008SP2
See also

  sp_MSscript_multirow_trigger (Procedure)
sp_MSscript_singlerow_trigger (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