Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSvalidatepeertopeerarticles

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSvalidatepeertopeerarticles
(
	@publication sysname
)
AS
BEGIN
	DECLARE @retcode 			bit,
			@publisher 			sysname,
		    @publisher_db 		sysname,
		    @msg_temp			sysname,
			@article			sysname,
			@pubid				int,
			@artid				int,
			@objid				int,			
			@status				int,
			@dest_owner			sysname,
			@dest_table			sysname,
			@filter				int,
			@filter_clause		nvarchar(max),
			@type				tinyint,
			@schema_option		binary(8),
			@ins_cmd			nvarchar(255),
			@upd_cmd			nvarchar(255),
			@del_cmd			nvarchar(255)
			
	SELECT @publisher		= publishingservername(),
		    @publisher_db	= DB_NAME()

	DECLARE #validatepeerarts CURSOR LOCAL FAST_FORWARD FOR
		SELECT sa.pubid,
				ISNULL(sa.name, N''),
				sa.artid,
				sa.objid,
				sa.status,
				ISNULL(sa.dest_owner,  SCHEMA_NAME(OBJECTPROPERTY(sa.objid, 'SchemaId'))),
				ISNULL(sa.dest_table, OBJECT_NAME(sa.objid)),
				ISNULL(sa.filter, 0),
				ISNULL(sa.filter_clause, N''),
				sa.type,
				sa.schema_option,
				ISNULL(sa.ins_cmd, N''),
				ISNULL(sa.upd_cmd, N''),
				ISNULL(sa.del_cmd, N'')
			FROM syspublications sp
				JOIN sysextendedarticlesview sa
					ON sp.pubid = sa.pubid
			WHERE sp.name = @publication
	
	OPEN #validatepeerarts

	FETCH #validatepeerarts INTO @pubid,
									@article,
									@artid,
									@objid,
									@status,
									@dest_owner,
									@dest_table,
									@filter,
									@filter_clause,
									@type,
									@schema_option,
									@ins_cmd,
									@upd_cmd,
									@del_cmd

	WHILE @@FETCH_STATUS != -1
	BEGIN
		-- we only check these for table type articles...
		IF @type IN (1, 257)
		BEGIN
			-- Disable anything but parameterized statements for now...
			IF (@status & 16) != 16
			BEGIN
				SELECT @msg_temp = CONVERT(nvarchar(100), @status)

				-- Peer-To-Peer publications only support a '%s' value of %s. Article '%s' currently has a '%s' value of '%s'. This value must be changed to continue.
				RAISERROR (21851, 16, -1, 'status', '''16'' (parameterized statements)', @article, 'status', @msg_temp)
				GOTO FAILURE
			END
			
			-- here we verify that there is no vertical filtering.
			-- note that we skip over the timestamp column since it may
			-- be replicated as timestamp (this will be checked below)
			IF EXISTS (SELECT *
						FROM sys.columns
						WHERE object_id = @objid
							AND system_type_id != TYPE_ID(N'timestamp')
							AND column_id NOT IN (SELECT colid
													FROM sysarticlecolumns
													WHERE artid = @artid))
			BEGIN
				-- Peer-To-Peer publications do not support %s. Article '%s' currently has %s. This must be changed to continue.
				RAISERROR(21852, 16, -1, 'vertical filtering', @article, 'vertical filtering enabled')
				GOTO FAILURE
			END

			-- Only allow manual identity ranges
			IF @objid in (SELECT objid
							FROM MSpub_identity_range)
				OR EXISTS(SELECT *
							FROM sysarticleupdates
							WHERE pubid = @pubid
								AND artid = @artid
								AND identity_support = 1)
			BEGIN
				-- Peer-To-Peer publications do not support %s. Article '%s' currently has %s. This must be changed to continue.
				RAISERROR(21852, 16, -1, 'auto identity range management', @article, 'auto identity range managment enabled')
				GOTO FAILURE
			END

			-- check the 'none' auto-identity range management option
			IF EXISTS(SELECT *
						FROM sysarticles as sysa
							JOIN sys.columns as syscol
								ON sysa.objid = syscol.object_id
						WHERE syscol.object_id = @objid
							AND syscol.is_identity = 1
							AND NOT (CONVERT(int, sysa.schema_option) & 0x4 = 0x4
								AND ColumnProperty(syscol.object_id, syscol.name, N'IsIdNotForRepl') = 1))
			BEGIN
				-- Peer-To-Peer publications do not support %s. Article '%s' currently has %s. This must be changed to continue.
				RAISERROR(21852, 16, -1, '''none'' for the identity range management', @article, '''none'' set for the identity range managment')
				GOTO FAILURE
			END
		END
		
		-- Disallow any type of dest owner/object name change...
		IF @dest_table != N''
			AND @dest_table != OBJECT_NAME(@objid)
		BEGIN
			-- Peer-To-Peer publications do not support %s. Article '%s' currently has %s. This must be changed to continue.
			RAISERROR(21852, 16, -1, 'destination object name changes', @article, 'different source and destination object names')
			GOTO FAILURE
		END

		IF @dest_owner != N''
			AND @dest_owner != SCHEMA_NAME(OBJECTPROPERTY(@objid, 'SchemaId'))
		BEGIN
			-- Peer-To-Peer publications do not support %s. Article '%s' currently has %s. This must be changed to continue.
			RAISERROR(21852, 16, -1, 'destination object owner name changes', @article, 'different source and destination object owner names')
			GOTO FAILURE
		END

		-- Ensure that we are not attempting to create an invalid
		-- Peer-To-Peer publication/subscription configuration...
		EXEC @retcode = sys.sp_MSdetectinvalidaddarticle @publication = @publication,
															@dest_table = @dest_table,
															@dest_owner = @dest_owner
		IF @@ERROR <> 0 OR @retcode <> 0
			GOTO FAILURE

		-- Disallow any type of filtering...
		IF @filter != 0
			OR @filter_clause != N''
			OR @type IN (3, 5, 7, 259, 261, 263)
		BEGIN
			-- Peer-To-Peer publications do not support %s. Article '%s' currently has %s. This must be changed to continue.
			RAISERROR(21852, 16, -1, 'horizontal filtering', @article, 'horizontal filtering enabled')
			GOTO FAILURE
		END

		-- Disallow any tables with timestamp
		IF ISNULL(OBJECTPROPERTY(@objid, 'TableHasTimestamp'), 0) = 1
			AND (sys.fn_replgetbinary8lodword(@schema_option) & 0x8) = 0
		BEGIN
			-- Peer-To-Peer publications do not support replicating timestamp columns as varbinary(8). Adding an article with this option or adding/altering a table to include a timestamp column as varbinary(8) is not allowed.
			RAISERROR (21734, 16, -1)
			GOTO FAILURE
		END
		
		IF LEFT(UPPER(LTRIM(@ins_cmd)), 3) IN (N'SQL')
		BEGIN
			-- Peer-To-Peer publications do not support %s. Article '%s' currently has %s. This must be changed to continue.
			RAISERROR(21852, 16, -1, 'SQL command types', @article, 'an insert command type of SQL')
			GOTO FAILURE
		END

		IF LEFT(UPPER(LTRIM(@upd_cmd)), 3) IN (N'SQL')
		BEGIN
			-- Peer-To-Peer publications do not support %s. Article '%s' currently has %s. This must be changed to continue.
			RAISERROR(21852, 16, -1, 'SQL command types', @article, 'an update command type of SQL')
			GOTO FAILURE
		END

		IF LEFT(UPPER(LTRIM(@del_cmd)), 3) IN (N'SQL')
		BEGIN
			-- Peer-To-Peer publications do not support %s. Article '%s' currently has %s. This must be changed to continue.
			RAISERROR(21852, 16, -1, 'SQL command types', @article, 'a delete command type of SQL')
			GOTO FAILURE
		END
		
		FETCH #validatepeerarts INTO @pubid,
										@article,
										@artid,
										@objid,
										@status,
										@dest_owner,
										@dest_table,
										@filter,
										@filter_clause,
										@type,
										@schema_option,
										@ins_cmd,
										@upd_cmd,
										@del_cmd
	END

	CLOSE #validatepeerarts
	DEALLOCATE #validatepeerarts
	

	RETURN 0
FAILURE:
	
	RETURN 1
END

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSrepl_changepublication (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