Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_changearticlecolumndatatype

  No additional text.


Syntax
create procedure sys.sp_MSrepl_changearticlecolumndatatype
(
	@publication	sysname,
	@article		sysname,
	@column			sysname,
	@mapping_id		int,
	@type			sysname,
	@length			bigint,
	@precision		bigint,
	@scale			bigint,
	@nullable		bit,
	@publisher		sysname,
	@publisher_type	sysname
)
AS
BEGIN
    DECLARE @article_id			int
    DECLARE @table_id			int
    DECLARE	@publisher_status	int
    DECLARE @dest_type			varchar(10)
    DECLARE @dest_type_name		sysname
    DECLARE @dest_length		bigint
    DECLARE @dest_prec			bigint
    DECLARE @dest_scale			int
    DECLARE @dest_nullable		bit
    DECLARE @dataloss			bit
    DECLARE @src_type			sysname
    DECLARE @src_length			bigint
    DECLARE @src_prec			int
    DECLARE @src_scale			int
    DECLARE @publishercolumn_id	int
    DECLARE @retcode			int
    DECLARE @publisher_dbms		sysname
    DECLARE @publisher_version	sysname
	
    SET NOCOUNT ON

	-- Security Check
	exec @retcode = sys.sp_MSreplcheck_publish
	if @@ERROR <> 0 or @retcode <> 0
		return(1)

	-- Set publisher DBMS and version (for getting default data type mapping)
	if @publisher_type like 'ORACLE%'
	BEGIN
		SELECT	@publisher_dbms		= '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
		RAISERROR (21645, 16, -1, @publisher_type)
		RETURN (1)
	END

    -- Parameter Check:  @publication.
    -- Make sure that the publication exists and that it conforms to the
    -- rules for identifiers.

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

    EXECUTE @retcode = dbo.sp_validname @publication
    IF @retcode <> 0
		RETURN (1)

    -- Verify publication exists
    IF NOT EXISTS
    (
    	SELECT	*
    	FROM	MSpublications p,
				master..sysservers s
		WHERE	p.publication		= @publication
		  AND	p.publisher_id		= s.srvid
		  AND   UPPER(s.srvname collate database_default)    = UPPER(@publisher) collate database_default
	)
    BEGIN
        RAISERROR (20026, 11, -1, @publication)
        RETURN (1)
    END
	
    -- Parameter Check:  @article.
    -- Check to make sure that the article exists in the publication.

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

    -- Get table and article id's
    SELECT	@article_id			= a.article_id,
			@table_id			= a.table_id,
			@publisher_status	= a.publisher_status
    FROM	IHarticles a,
			MSpublications p,
			master..sysservers s
    WHERE	a.name				= @article
      AND	p.publication		= @publication
      AND	a.publication_id	= p.publication_id
      AND	a.publisher_id		= s.srvid
      AND   UPPER(s.srvname collate database_default)    = UPPER(@publisher) collate database_default

    IF @table_id IS NULL or @article_id IS NULL
    BEGIN
        RAISERROR (20027, 11, -1, @article)
        RETURN (1)
    END

    
    -- Column must exist
    
	IF @column IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, N'@column', 'sp_MSrepl_changearticlecolumndatatype')
	END
	
    SELECT	@publishercolumn_id	= publishercolumn_id,
			@src_type			= type,
			@src_length			= length,
			@src_prec			= prec,
			@src_scale			= scale
    FROM	IHpublishercolumns
    WHERE	table_id	= @table_id
    AND		name		= @column

    IF @publishercolumn_id IS NULL
    BEGIN
        RAISERROR(21616, 16, -1, @column, @article)
        RETURN (1)
    END

	BEGIN TRAN
    SAVE TRANSACTION changearticlecolumndatatype

	-- Set article bit to show that the mappings are not the defaults
	UPDATE	IHarticles
	SET		use_default_datatypes = 0
	WHERE	article_id = @article_id

	IF (@mapping_id IS NULL) AND (@type IS NULL)
	BEGIN
		-- Get default for this type
		EXEC @retcode = sys.sp_getdefaultdatatypemapping
						@source_dbms			= @publisher_dbms,
						@source_version			= @publisher_version,
						@source_type			= @src_type,
						@source_length			= @src_length,
						@source_precision		= @src_prec,
						@source_scale			= @src_scale,
						@destination_dbms		= 'MSSQLSERVER',
						@destination_version	= NULL,
						@destination_type		= @dest_type_name OUTPUT,
						@destination_length		= @dest_length OUTPUT,
						@destination_precision	= @dest_prec OUTPUT,
						@destination_scale		= @dest_scale OUTPUT,
						@destination_nullable	= @dest_nullable OUTPUT,
						@dataloss				= @dataloss OUTPUT

		IF @retcode <> 0 or @@ERROR <> 0
		BEGIN
			IF @@TRANCOUNT > 0
			BEGIN
				ROLLBACK TRANSACTION changearticlecolumndatatype
				COMMIT TRAN
			END
			RETURN (1)
		END
	END
	ELSE
	BEGIN
		IF (@mapping_id IS NOT NULL) AND (@mapping_id > 0)
		BEGIN
			-- Get type, length, prec, scale from mapping
			SELECT	@dest_type_name	= destination_type,
					@dest_length	= destination_length,
					@dest_prec		= destination_precision,
					@dest_scale		= destination_scale,
					@dest_nullable	= destination_nullable
			FROM	sys.fn_MSrepl_getdatatypemappings
					(
						@publisher_dbms,
						@publisher_version,
						@src_type,
						@src_length,
						@src_prec,
						@src_scale,
						1,
						N'MSSQLSERVER',
						NULL,
						0,
						@mapping_id
					)
    		END
		ELSE
		BEGIN
			-- Set type, length, prec, scale from inputs
			SELECT	@dest_type_name	= @type,
					@dest_length	= @length,
					@dest_prec		= @precision,
					@dest_scale		= @scale,
					@dest_nullable	= @nullable
		END

		-- Verify mapping exists
		EXEC @retcode =	sys.sp_MSrepl_checkdatatypemapping
						@source_dbms			= @publisher_dbms,
						@source_version			= @publisher_version,
						@source_type			= @src_type,
						@source_length			= @src_length,
						@source_precision		= @src_prec,
						@source_scale			= @src_scale,
						@destination_dbms		= 'MSSQLSERVER',
						@destination_version	= NULL,
						@destination_type		= @dest_type_name,
						@destination_length		= @dest_length,
						@destination_precision	= @dest_prec,
						@destination_scale		= @dest_scale,
						@destination_nullable	= @dest_nullable

		IF @retcode != 0 OR @@ERROR != 0
		BEGIN
			IF @@trancount > 0
			BEGIN
				ROLLBACK TRANSACTION changearticlecolumndatatype
				COMMIT tran
			END
			RETURN (1)
		END
	END
	
	-- Get core type (necessary due to varchar(max) cases)
	IF UPPER(RIGHT(@dest_type_name, 5)) = N'(MAX)'
	BEGIN
		SELECT @dest_type_name = LEFT(@dest_type_name, LEN(@dest_type_name) - 5)
		SELECT @dest_length = -1
	END
	
	-- Get systype id for destination data type
	SELECT	@dest_type = type_id(LOWER(@dest_type_name))

	IF (@dest_type IS NULL) OR (@dest_type = 0)
	BEGIN
   		RAISERROR(21779, 16, -1, @src_type)
		
		IF @@trancount > 0
		BEGIN
			ROLLBACK TRANSACTION changearticlecolumndatatype
			COMMIT TRAN
		END
		RETURN (1)
	END

	-- Update data type mapping info
	UPDATE	IHcolumns
	SET		mapped_type			= @dest_type,
			mapped_length		= @dest_length,
			mapped_prec			= @dest_prec,
			mapped_scale		= @dest_scale,
			mapped_nullable		= @dest_nullable
	WHERE	name = @column
	  AND	publishercolumn_id	= @publishercolumn_id
	  AND	article_id			= @article_id
	
    IF @@ERROR <> 0
    BEGIN
        IF @@trancount > 0
        BEGIN
            ROLLBACK TRANSACTION changearticlecolumndatatype
            COMMIT TRAN
        END
        RETURN (1)
    END

	-- Flush article cache
	EXEC @retcode = sys.sp_IHreplflush	@publisher

	IF @retcode != 0 OR @@ERROR != 0
	BEGIN
		IF @@trancount > 0
		BEGIN
			ROLLBACK TRANSACTION changearticlecolumndatatype
			COMMIT TRAN
		END
		RETURN (1)
	END
	
	-- Reinit article
    EXECUTE @retcode  = sys.sp_MSreinit_article
						@publication				= @publication,
						@article					= @article,
						@need_new_snapshot			= 1,
						@need_reinit_subscription	= 1,
						@force_invalidate_snapshot	= 1,
						@force_reinit_subscription	= 1,
						@publisher					= @publisher,
						@publisher_type				= @publisher_type

	IF @retcode != 0 OR @@ERROR != 0
	BEGIN
		IF @@trancount > 0
		BEGIN
			ROLLBACK TRANSACTION changearticlecolumndatatype
			COMMIT TRAN
		END
		RETURN (1)
	END

	COMMIT TRAN
	RETURN (0)
END

 
Last revision 2008RTM
See also

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