Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscriptpublicationcustomprocs

  No additional text.


Syntax

-- Name: sp_MSscriptpublicationcustomprocs

-- Description: This is an internal procedure that does the real work
--              for sp_scriptpublicationcustomprocs and
--              sp_MSgeneratenosyncsubscriptionsetupscript scripting out
--              a publication's (or an article thereof) custom procedures.

-- Notes: 1) Reconciliation procedures for concurrent snapshot will
--           not be scripted by this procedure. It does not really make
--           sense to have concurrent snapshots for nosync subscriptions.
--        2) Custom procedures will not be scripted out for articles
--           without the auto-generate custom procedure (0x2) schema_option.
--        3) IMPORTANT: The #scripttext temporary table is assumed to be
--           created before this procedure is called.

-- Parameter: @publication sysname

-- Security: This is an internal system procedure.
-- Requires Certificate signature for catalog access

create procedure sys.sp_MSscriptpublicationcustomprocs
(
    @publication    sysname,
    @article        sysname = N'all',
    @publisher		sysname = NULL,
    @publisher_type	sysname = N'MSSQLSERVER',
    @usesqlclr bit = 0
)
as
begin
    set nocount on

    declare @retcode			int,
            @artid				int,
            @pubid				int,
            @cursor_allocated	bit,
            @cursor_opened		bit,
            @current_article	sysname,
            @ins_cmd			nvarchar(255),
            @upd_cmd			nvarchar(255),
            @del_cmd			nvarchar(255),
            @schema_option		int,
            @repl_freq			int,
            @formattedmessage	nvarchar(4000),
            @publishertype		tinyint

    -- Initializations
    select @retcode = 0,
           @pubid = null,
           @cursor_allocated = 0,
           @cursor_opened = 0,
           @publishertype = case when (@publisher_type = N'MSSQLSERVER') then 1 else 2 end
    
    -- Make sure the current database is enabled for transaction replication
    -- Check if the current database is published
    IF (sys.fn_MSrepl_istranpublished(db_name(),1) = 0)
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    -- Get publication id
    SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)
    if @pubid is null
    begin
        raiserror(20026, 16, -1, @publication)
        return 1
    end

    -- Parameter check: The specified @publication is a valid Transactional
    -- publication
    SELECT	@repl_freq = repl_freq
    FROM	syspublications
    WHERE	name  = @publication
      AND	pubid = @pubid
    -- Don't script out custom procs for a snapshot publication
    if @repl_freq = 1
    begin
        raiserror(21515, 16, -1, @publication)
        return 1
    end

    -- Script header
    select @formattedmessage = formatmessage(21516, @publication, db_name())
    IF (@@ERROR != 0)
    BEGIN
        SELECT @retcode = 1
        GOTO Failure
    END

    insert into #scripttext(line) values(N'--')
    insert into #scripttext(line) values(N'-- ' + @formattedmessage)
    insert into #scripttext(line) values(N'--')
    insert into #scripttext(line) values(N'')
    insert into #scripttext(line) values(N'')

    -- Open cursor through all table articles in the specified publication and script out
    -- custom procs as necessary

	DECLARE harticle CURSOR local fast_forward FOR
	SELECT	artid,
			ins_cmd,
			upd_cmd,
			del_cmd,
			name,
			sys.fn_replgetbinary8lodword(schema_option)
	FROM	dbo.sysarticles
	WHERE	pubid = @pubid
	  AND	(type & 1) <> 0
	  AND	(@article = N'all' or name = @article)

    IF (@@ERROR != 0)
    BEGIN
    	SELECT @retcode = 1
    	GOTO Failure
    END

    select @cursor_allocated = 1

    open harticle

    IF (@@ERROR != 0)
    BEGIN
    	SELECT @retcode = 1
    	GOTO Failure
    END

    select @cursor_opened = 1

    FETCH	harticle
    INTO	@artid,
    		@ins_cmd,
    		@upd_cmd,
    		@del_cmd,
    		@current_article,
    		@schema_option

    while (@@fetch_status<>-1)
    begin
        IF (@schema_option & 2) = 0
        BEGIN

	       SELECT @formattedmessage = formatmessage(21517,@current_article)

		IF (@@ERROR != 0)
		BEGIN
			SELECT @retcode = 1
			GOTO Failure
		END

            insert into #scripttext(line) values(N'----')
            insert into #scripttext(line) values(N'---- ' + @formattedmessage)
      	     insert into #scripttext(line) values(N'----')
            insert into #scripttext(line) values(N'')
		
            goto SkipArticle
        end

        SELECT @formattedmessage = formatmessage(21518,@current_article)

	IF (@@ERROR != 0)
	BEGIN
		SELECT @retcode = 1
		GOTO Failure
	END

        insert into #scripttext(line) values(N'----')
        insert into #scripttext(line) values(N'---- ' + @formattedmessage)
        insert into #scripttext(line) values(N'----')
        insert into #scripttext(line) values(N'')

        -- INSERT
        IF LOWER(substring(@ins_cmd,1,len(N'call'))) = N'call' OR
        	LOWER(substring(@ins_cmd,1,len(N'scall'))) = N'scall'
        BEGIN
            INSERT INTO #scripttext(line)
            EXEC @retcode = sys.sp_MSscriptcustominsproc @artid, @publishertype, @publisher, @usesqlclr

			IF (@@ERROR != 0) OR (@retcode != 0)
			BEGIN
				SELECT @retcode = 1
				GOTO Failure
			END

            INSERT INTO #scripttext(line) VALUES('go')
            INSERT INTO #scripttext(line) VALUES('')
        END
        ELSE IF LOWER(SUBSTRING(@ins_cmd,1,len(N'sql'))) = N'sql'
        BEGIN
            SELECT @formattedmessage = formatmessage(21519)

			IF (@@ERROR != 0)
			BEGIN
				SELECT @retcode = 1
				GOTO Failure
			END

            insert #scripttext(line) values('-- ' + @formattedmessage)
            insert #scripttext(line) values('')
        END
        ELSE
        BEGIN
            SELECT @formattedmessage = formatmessage(21520,@ins_cmd)

			IF (@@ERROR != 0)
			BEGIN
				SELECT @retcode = 1
				GOTO Failure
			END
            insert #scripttext(line) values('-- ' + @formattedmessage)
      	     insert #scripttext(line) values('')
        END

		-- UPDATE
        IF	(LOWER(SUBSTRING(@upd_cmd,1,len(N'call'))) = N'call') OR
        	(LOWER(SUBSTRING(@upd_cmd,1,len(N'mcall'))) = N'mcall') OR
        	(LOWER(SUBSTRING(@upd_cmd,1,len(N'xcall'))) = N'xcall') OR
        	(LOWER(SUBSTRING(@upd_cmd,1,len(N'vcall'))) = N'vcall') OR
        	(LOWER(SUBSTRING(@upd_cmd,1,len(N'scall'))) = N'scall')
        BEGIN
            INSERT INTO #scripttext(line)
            EXEC @retcode = sys.sp_MSscriptcustomupdproc @artid, @publishertype, @publisher, @usesqlclr

			IF (@@ERROR != 0) OR (@retcode != 0)
			BEGIN
				SELECT @retcode = 1
				GOTO Failure
			END

            insert into #scripttext(line) values('go')
            insert into #scripttext(line) values('')
        END
        ELSE IF LOWER(SUBSTRING(@upd_cmd,1,len(N'sql'))) = N'sql'
        BEGIN
            SELECT @formattedmessage = formatmessage(21519)

			IF (@@ERROR != 0)
			BEGIN
				SELECT @retcode = 1
				GOTO Failure
			END
            insert #scripttext(line) values('-- ' + @formattedmessage)
      	     insert #scripttext(line) values('')
        END
        ELSE
        BEGIN
            SELECT @formattedmessage = formatmessage(21520,@upd_cmd)

			IF (@@ERROR != 0)
			BEGIN
				SELECT @retcode = 1
				GOTO Failure
			END

            insert #scripttext(line) values('-- ' + @formattedmessage)
      	     insert #scripttext(line) values('')
        END

		-- DELETE
        IF	(LOWER(SUBSTRING(@del_cmd,1,len(N'call'))) = N'call') OR
        	(LOWER(SUBSTRING(@del_cmd,1,len(N'xcall'))) = N'xcall') OR
        	(LOWER(SUBSTRING(@del_cmd,1,len(N'vcall'))) = N'vcall')
        BEGIN
     INSERT INTO #scripttext(line)
            EXEC @retcode = sys.sp_MSscriptcustomdelproc @artid, @publishertype, @publisher, @usesqlclr

			IF (@@ERROR != 0) OR (@retcode != 0)
			BEGIN
				SELECT @retcode = 1
				GOTO Failure
			END

            insert into #scripttext(line) values('go')
            insert into #scripttext(line) values('')
        end
        else if lower(substring(@del_cmd,1,len(N'sql'))) = N'sql'
        begin
            select @formattedmessage = formatmessage(21519)

			IF (@@ERROR != 0)
			BEGIN
				SELECT @retcode = 1
				GOTO Failure
			END

	      insert #scripttext(line) values('-- ' + @formattedmessage)
	       insert #scripttext(line) values('')

        end
        else
        begin
            select @formattedmessage = formatmessage(21520,@del_cmd)

			IF (@@ERROR != 0)
			BEGIN
				SELECT @retcode = 1
				GOTO Failure
			END

     	     insert #scripttext(line) values('-- ' + @formattedmessage)
            insert #scripttext(line) values('')

        end

SkipArticle:
        FETCH	harticle
        INTO	@artid,
        		@ins_cmd,
        		@upd_cmd,
        		@del_cmd,
        		@current_article,
        		@schema_option
    end

Failure:

    if @cursor_opened <> 0
    begin
        close harticle
    end

    if @cursor_allocated <> 0
    begin
        deallocate harticle
    end
    return @retcode
end

 
Last revision 2008RTM
See also

  sp_MSdrop_asm_tranrepl (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSgeneratenosyncsubscriptionsetupscript (Procedure)
sp_MSrepl_scriptpublicationcustomprocs (Procedure)
sp_MSscriptpeerconflictdetection_customprocs (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