Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_helparticlecolumns

  No additional text.


Syntax


-- Name:
--          sp_MSrepl_helparticlecolumns

-- Description:
--          Returns information about an article's columns.

--          Internal implementation of helparticlecolumns.
--			Flag @is_snapshot is used to signal whether additional
--			output should be produced for consumption by snapshot.

-- Security:
--          SQL Server publication:     'sysadmin', db_owner of publishing database, PAL
--          Heterogeneous publication:  'sysadmin', db_owner of distribution database, PAL
-- Requires Certificate signature for catalog access

-- Returns:
--          Result set of article properties

-- Owner:
--          


create procedure sys.sp_MSrepl_helparticlecolumns
(
    @publication	sysname,
    @article		sysname,
    @publisher		sysname,
    @publisher_type	sysname,
    @is_snapshot	bit = 0
)
AS
BEGIN
    /*
    ** Declarations.
    */

    DECLARE @pubid				int
    DECLARE	@artid				int
    DECLARE @retcode			int
    DECLARE @username			sysname
	DECLARE @publisher_dbms		sysname
	DECLARE @publisher_version	sysname
    DECLARE @status				tinyint
    DECLARE @objid				int
	
	IF @publisher_type != N'MSSQLSERVER'
	BEGIN
		-- Set publisher DBMS and version (for checking data types)
		IF @publisher_type LIKE 'ORACLE%'
		BEGIN
			SELECT	@publisher_dbms = N'ORACLE'

			EXEC @retcode = sys.sp_IHgetversion	@publisher	= @publisher,
												@version	= @publisher_version OUTPUT

			IF @retcode != 0 OR @@ERROR != 0
			BEGIN
				RETURN (1)
			END
		END
		ELSE
		BEGIN
			SELECT @publisher_dbms    = @publisher_type
			SELECT @publisher_version = NULL
		END
	END

    SELECT @username = suser_sname()

    /*
    ** Parameter Check: @article.
    ** The @article name must conform to the rules for identifiers.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_helparticlecolumns')
            RETURN (1)
        END

    /*
    EXECUTE @retcode = sys.sp_validname @article

    IF @retcode <> 0
    RETURN (1)
    */

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_helparticlecolumns')
            RETURN (1)
        END

    EXECUTE @retcode = sys.sp_validname @publication

    IF @retcode <> 0
        RETURN (1)

    /*
    ** Security Check. SQL Server: Restrict to 'sysadmin', DBO of publishing database, PAL
    **                 Heterogeneous: 'sysadmin', DOB of distribution database, PAL
    */
    IF is_member(N'db_owner') <> 1
    BEGIN
        exec @retcode =	sys.sp_MSreplcheck_pull	@publication = @publication,
												@given_login = @username

        IF @retcode <> 0 OR @@error <> 0
            RETURN (1)
    END

	-- Get publication id
	SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)

    IF @pubid IS NULL
    BEGIN
        RAISERROR (20026, 11, -1, @publication)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @article, @publication.
    ** Check to make sure that the article exists in this publication.
    */
    IF NOT EXISTS
    (
    	SELECT	*
		FROM	sysextendedarticlesview
		WHERE	pubid = @pubid
		  AND	name = @article
	)
    BEGIN
        RAISERROR (20027, 11, -1, @article)
        RETURN (1)
    END


    /*
    ** Error out if this is a not a table based article
    */
    IF NOT EXISTS
    (
    	SELECT	*
		FROM	sysarticles
		WHERE	name = @article
		  AND	pubid = @pubid
		  AND	(type & 1) = 1
	)
	BEGIN
		RAISERROR (14112, 11, -1 )
		RETURN (1)
	END

    IF @publisher_type = N'MSSQLSERVER'
    BEGIN
		SELECT	@status	= status,
				@objid	= objid,
				@artid	= artid
		  FROM	sysarticles
		 WHERE	name	= @article
		   AND	pubid	= @pubid
	END
	ELSE
	BEGIN
		SELECT	@status	= status,
				@objid	= table_id,
				@artid	= article_id
		  FROM	IHarticles
		 WHERE	name			= @article
		   AND	publication_id	= @pubid
	END

    -- Get the timestamp column id
    CREATE TABLE #work_helparticlecolumns
    (
		colid		int,
		name		sysname,
		published	bit,
		colorder	int IDENTITY(1,1) not null,
		sourcetype	sysname null,
		sourcelen	int null,
		sourceprec	int null,
		sourcescale	int null,
		desttype	sysname null,
		destlen		int null,
		destprec	int null,
		destscale	int null
	)

	IF @publisher_type = N'MSSQLSERVER'
	BEGIN
		INSERT	#work_helparticlecolumns
				(
					colid,
					name,
					published,
					sourcetype,
					sourcelen,
					sourceprec,
					sourcescale,
					desttype,
					destlen,
					destprec,
					destscale
				)
		SELECT	sc.column_id,
				sc.name,
				case sc.system_type_id when 189
					then convert(bit, (@status & 32))
					else convert(bit, 0)
				end,
				sys.fn_MSrepl_sourcetype
				(
					type_name(sc.user_type_id),
					sc.max_length,
					sc.max_length,
					sc.precision,
					sc.precision,
					sc.scale,
					sc.scale,
					(
						SELECT	dt.createparams
						FROM	msdb.dbo.MSdbms dbms,
								msdb.dbo.MSdbms_datatype dt
						WHERE	dbms.dbms_id = dt.dbms_id
						  AND	dbms.dbms = @publisher_type
						  AND	dt.type = type_name(sc.user_type_id)
					)
				),
				sc.max_length,
				sc.precision,
				sc.scale,
				sys.fn_MSrepl_sourcetype
				(
					type_name(sc.user_type_id),
					sc.max_length,
					sc.max_length,
					sc.precision,
					sc.precision,
					sc.scale,
					sc.scale,
					(
						SELECT	dt.createparams
						FROM	msdb.dbo.MSdbms dbms,
								msdb.dbo.MSdbms_datatype dt
						WHERE	dbms.dbms_id = dt.dbms_id
						  AND	dbms.dbms = @publisher_type
						  AND	dt.type = type_name(sc.user_type_id)
					)
				),
				sc.max_length,
				sc.precision,
				sc.scale
		FROM	sys.columns sc
		WHERE	object_id = @objid
		ORDER BY column_id
	END
	ELSE
	BEGIN
		-- HREPL: Use IHcolumns instead
		SET IDENTITY_INSERT #work_helparticlecolumns ON
		INSERT	#work_helparticlecolumns
				(
					colid,
					name,
					published,
					colorder,
					sourcetype,
					sourcelen,
					sourceprec,
					sourcescale,
					desttype,
					destlen,
					destprec,
					destscale
				)
		SELECT	ihpc.publishercolumn_id,
				ihpc.name,
				0,
				ihpc.column_ordinal,
				sys.fn_MSrepl_sourcetype
				(
					ihpc.type,
					ihpc.length,
					ihpc.length,
					ihpc.prec,
					ihpc.prec,
					ihpc.scale,
					ihpc.scale,
					(
						SELECT	dt.createparams
						FROM	msdb.dbo.MSdbms dbms,
								msdb.dbo.MSdbms_datatype dt
						WHERE	dbms.dbms_id = dt.dbms_id
						  AND	dbms.dbms = @publisher_dbms
						  AND	dt.type = ihpc.type
						  AND	sys.fn_IHcompareversion(dbms.version, @publisher_version) = 1
					)
				),
				ihpc.length,
				ihpc.prec,
				ihpc.scale,
				sys.fn_MSrepl_sourcetype
				(
					type_name(ihc.mapped_type),
					ihc.mapped_length,
					ihc.mapped_length,
					ihc.mapped_prec,
					ihc.mapped_prec,
					ihc.mapped_scale,
					ihc.mapped_scale,
					(
						SELECT	dt.createparams
						FROM	msdb.dbo.MSdbms dbms,
								msdb.dbo.MSdbms_datatype dt
						WHERE	dbms.dbms_id = dt.dbms_id
						  AND	dbms.dbms = N'MSSQLSERVER'
						  AND	dt.type = type_name(ihc.mapped_type)
					)
				),
				ihc.mapped_length,
				ihc.mapped_prec,
				ihc.mapped_scale
		FROM	IHpublishercolumns ihpc LEFT OUTER JOIN (SELECT * FROM IHcolumns WHERE article_id = @artid) ihc
		  ON	ihpc.publishercolumn_id = ihc.publishercolumn_id
		WHERE	ihpc.table_id = @objid
		ORDER BY ihpc.column_ordinal
		SET IDENTITY_INSERT #work_helparticlecolumns OFF
	END

	UPDATE	#work_helparticlecolumns
	SET		published = 1
	WHERE	colid in
			(
				SELECT	colid
				FROM	sysarticlecolumns
				WHERE	artid = @artid
			)

	-- REQUIRED: Columns must be returned in column order for snapshot
	IF @is_snapshot = 0
	BEGIN
		-- Simple user version
		SELECT	colid		as [column id],
				name		as [column],
				published	as [published],
				sourcetype	as [publisher type],
				desttype	as [subscriber type]
		FROM	#work_helparticlecolumns
		ORDER BY colorder
	END
	ELSE
	BEGIN
		-- Extended snapshot version
		SELECT	colid		as [column id],
				name		as [column],
				published	as [published],
				sourcetype	as [publisher type],
				sourcelen	as [publisher length],
				sourceprec	as [publisher precision],
				sourcescale	as [publisher scale],
				desttype	as [subscriber type],
				destlen		as [subscriber length],
				destprec	as [subscriber precision],
				destscale	as [subscriber scale]
		FROM	#work_helparticlecolumns
		ORDER BY colorder
	END

    DROP TABLE #work_helparticlecolumns
END

 
Last revision 2008RTM
See also

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