Script to generate replication procs for article

,

This script is basically a front-end for the sp_"scriptproc" stored procedures that ship with MSSQL.  This will allow you to create ALL of your procs (INS, UPD and DEL) on the fly using the article name instead of having to first search for the article ID.  It also uses sp_scriptdynamicupdproc where appropriate.  This facilitates quick repairs to transactional replication problems when time is of the essence.

This proc is usually only useful in situations where you have not used a snapshot to generate the schema.

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[usp_scriptreplproc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[usp_scriptreplproc]
GO

CREATE PROCEDURE dbo.usp_scriptreplproc
	(
	@dbname 	SYSNAME = NULL,
	@articlename 	SYSNAME,
	@action 	VARCHAR(6) = NULL
	)
AS

/*	
**  Purpose:   To quickly script replication stored procs without snapshoting
**  Author:    Richard Ding
**  Date:      1/30/2003
**  Modified by:  Sean Gorman
**  Date:    3/26/2007
**  Mods:  Now uses  sp_scriptdynamicupdproc for MCALL updates (SQL2K post SP3)
**	Changed eval of sysarticles to use name column instead of dest_table
**	Fixed Dynamic SQL USE statements
**	Forced comments on "Procedure text"
**	added NOCOUNT ON
**	added "dummy" tip
**	Added SQL 2005 Publisher Compatibility
**	Added error message and checks for article not found
**
**  Test code:
**    sp_chi_scriptreplproc 'choice', 'actv_profil', 'del'
**    sp_chi_scriptreplproc 'waste_mgmt', 'drum_trackg_actvty', 'ins'
**    sp_chi_scriptreplproc 'reference', 'company_master', 'update'
**    sp_chi_scriptreplproc null, 'actv_profil', 'del'
**    sp_chi_scriptreplproc 'choic', 'actv_profil', 'delelte'
**    sp_chi_scriptreplproc 'order_mgmt', 'sls_order_dspsl', null
*/

SET NOCOUNT ON

DECLARE @version INT
DECLARE @type_get NVARCHAR(512)
DECLARE @retval VARCHAR(5)
DECLARE @string VARCHAR(800)
DECLARE @actionstring VARCHAR(800)
DECLARE @exists_string NVARCHAR(100)

CREATE TABLE #results
	([results] TEXT)
CREATE TABLE #retval2
	([retval2] INT)

SELECT @version = SUBSTRING((CONVERT(VARCHAR(25), SERVERPROPERTY('productversion')) ), 1, 1)

BEGIN
	IF @dbname IS NULL
		SET @dbname = db_name()
	ELSE
		BEGIN
			DECLARE @returncode INT
			EXEC @returncode = sp_validname @dbname
			IF @returncode <> 0
				BEGIN
					RAISERROR(15224,-1,15,@dbname)
					RETURN(1)
				END
		END
		IF @version = 8
			BEGIN
				IF (SELECT category FROM master..sysdatabases WITH (NOLOCK) WHERE name = @dbname) = 0
					BEGIN
						DECLARE @errormsg8 VARCHAR(300)
						SET @errormsg8 = 'Database ''' + @dbname + ''' is NOT published. You must pick a published database.'
						RAISERROR (@errormsg8, 16, 1)
						RETURN
					END
			END
		IF @version = 9
			BEGIN
				IF (SELECT is_published FROM master.sys.databases WITH (NOLOCK) WHERE name = @dbname) = 0
					BEGIN
						DECLARE @errormsg9 VARCHAR(300)
						SET @errormsg9 = 'Database ''' + @dbname + ''' is NOT published. You must pick a published database.'
						RAISERROR (@errormsg9, 16, 1)
						RETURN
					END
			END
		SET @exists_string = N'SELECT artid FROM ' + @dbname + '.dbo.sysarticles WITH (NOLOCK) WHERE name = ''' + @articlename + CHAR(39)
		INSERT INTO #retval2
			EXEC (@exists_string)
		IF NOT EXISTS (SELECT retval2 FROM #RETVAL2 WHERE retval2 IS NOT NULL)
		BEGIN
					DECLARE @errormsg VARCHAR(300)
					SET @errormsg = 'Article ''' + @articlename + ''' cannot be found in ' + @dbname + '.dbo.sysarticles.  Please check the number and dial again.'
					RAISERROR (@errormsg, 16, 1)
					RETURN
		END
		IF @action IS NOT NULL AND @action NOT IN ('del', 'delete', 'ins', 'insert', 'upd', 'update')
			BEGIN
				RAISERROR ('Valid action types are ''del'', ''delete'', ''ins'', ''insert'', ''upd'', ''update''.  Please try again.', 16, 1)
				RETURN
			END

		SELECT '--***TIP:  Hit CTRL + T to get results in text.***   '
		PRINT CHAR(13)
		PRINT 'USE ' + @dbname
		PRINT CHAR(13)
		PRINT 'GO'
		PRINT CHAR(13) + CHAR(10)

		SET @type_get = N'SELECT @retval = SUBSTRING(upd_cmd, 1, 5) FROM ' + @dbname + '..sysarticles WITH (NOLOCK) WHERE name = ' + CHAR(39) + @articlename + CHAR(39)
		EXEC sp_executesql 
			@query = @type_get
			,@params = N'@retval VARCHAR(5) OUTPUT'
			, @retval = @retval OUTPUT


		SET @string = 'USE ' + @dbname + CHAR(10) + CHAR(13) +  'DECLARE @articleid INT'  + CHAR(10) + CHAR(13) +  'SET @articleid = ( SELECT artid FROM sysarticles WITH (NOLOCK) WHERE name = ''' + @articlename + ''')'  + CHAR(10) + CHAR(13)  


		IF @action IN ('del', 'delete')
			BEGIN
				SET @actionstring = ' EXEC sp_scriptdelproc @artid = @articleid '
			END

		IF @action IN ('ins', 'insert')
			BEGIN
				SET @actionstring = ' EXEC sp_scriptinsproc @artid = @articleid '
			END

		IF @action IN ('upd', 'update')
			BEGIN
				IF @retval = 'XCALL'
					BEGIN
						SET @actionstring = 
						'EXEC sp_scriptxupdproc @artid = @articleid '
					END
				IF @retval = 'MCALL'
					BEGIN
						SET @actionstring = 
						'EXEC sp_scriptdynamicupdproc @artid = @articleid '
					END
			END
		IF @action IS NULL
			BEGIN
				SET @actionstring =   
				'EXEC sp_scriptdelproc @artid = @articleid '
				 + CHAR(13) +  
				'EXEC sp_scriptinsproc @artid = @articleid '
				+ CHAR(13) 

				IF @retval = 'XCALL'
					BEGIN
						SET @actionstring = @actionstring + 							
						'EXEC sp_scriptxupdproc @artid = @articleid '
					END
				IF @retval = 'MCALL'
					BEGIN
						SET @actionstring = @actionstring + 							
						'EXEC sp_scriptdynamicupdproc @artid = @articleid '
					END
			END

		INSERT INTO #results
		EXEC (@string + @actionstring)
		

		SELECT results AS '--Procedure(s) Text Follows' FROM #results

END


GO

Rate

5 (1)

Share

Share

Rate

5 (1)