Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_adddatatypemapping

  No additional text.


Syntax


-- Name:
--		sp_MSrepl_adddatatypemapping

-- Description:
--		Add source/destination data type mapping

-- Returns:
--		0 if successful
--		1 if failed

-- Security:
--		Internal

-- Notes:
--		Implicitly will create a DBMS map if it doesn't
--		currently exist.  DBMS types must exist before
--		adding a mapping.  If no default currently
--		exists, this is set as the default mapping


CREATE PROCEDURE sys.sp_MSrepl_adddatatypemapping
(
	@source_dbms				sysname,
	@source_version				varchar(10) = NULL,
	@source_type				sysname,
	@source_length_min			bigint = NULL,
	@source_length_max			bigint = NULL,
	@source_precision_min		bigint = NULL,
	@source_precision_max		bigint = NULL,
	@source_scale_min			int = NULL,
	@source_scale_max			int = NULL,
	@source_nullable			bit = NULL,
	@destination_dbms			sysname,
	@destination_version		varchar(10) = NULL,
	@destination_type			sysname,
	@destination_length			bigint = NULL,
	@destination_precision		bigint = NULL,
	@destination_scale			int = NULL,
	@destination_nullable		bit = NULL,
	@destination_createparams	int = NULL,
	@dataloss					bit = 0,
	@is_default					bit = 0
)
AS
BEGIN
	DECLARE @retcode				int
	DECLARE @src_dbms_id			int
	DECLARE @src_datatype_id		int
	DECLARE @dest_dbms_id			int
	DECLARE @dest_datatype_id		int
	DECLARE @map_id					int
	DECLARE @datatype_mapping_id	int
	
	-- Prepare dbms for case insensitive searches
	SET @source_dbms		= UPPER(@source_dbms)
	SET @destination_dbms	= UPPER(@destination_dbms)

    SET NOCOUNT ON
	set @retcode = 0

	
    -- Security Check: require sysadmin
	
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
    BEGIN
        RAISERROR(21089,16,-1)
        RETURN (1)
    END

	-- Verify source length range
	IF @source_length_min > @source_length_max
	BEGIN
		RAISERROR(21771, 16, -1, 'Source length', @source_length_min, @source_length_max)
		RETURN (1)
	END

	-- Verify precision range
	IF @source_precision_min > @source_precision_max
	BEGIN
		RAISERROR(21771, 16, -1, 'Source precision', @source_precision_min, @source_precision_max)
		RETURN (1)
	END

	-- Verify scale range
	IF @source_scale_min > @source_scale_max
	BEGIN
		RAISERROR(21771, 16, -1, 'Source scale', @source_scale_min, @source_scale_max)
		RETURN (1)
	END
	
	-- Get source info
	EXEC @retcode = sys.sp_MSrepl_getdbmsinfo
					@dbms			= @source_dbms,
					@version		= @source_version,
					@type			= @source_type,
					@dbms_id		= @src_dbms_id OUTPUT,
					@datatype_id	= @src_datatype_id OUTPUT
					
	IF @retcode <> 0 OR @@ERROR <> 0
	BEGIN
		RETURN (@retcode)
	END

	-- Get destination info
	EXEC @retcode = sys.sp_MSrepl_getdbmsinfo
					@dbms			= @destination_dbms,
					@version		= @destination_version,
					@type			= @destination_type,
					@dbms_id		= @dest_dbms_id OUTPUT,
					@datatype_id	= @dest_datatype_id OUTPUT
					
	IF @retcode <> 0 OR @@ERROR <> 0
	BEGIN
		RETURN (@retcode)
	END

	BEGIN TRAN
	SAVE TRAN adddatatypemapping
	
	-- Verify that the mapping doesn't already exist
	IF EXISTS
	(
		SELECT	datatype_mapping_id
		FROM	msdb.dbo.MSdbms_map map,
				msdb.dbo.MSdbms_datatype_mapping dm
		WHERE	map.map_id			= dm.map_id
		  AND	map.src_dbms_id		= @src_dbms_id
		  AND	map.dest_dbms_id	= @dest_dbms_id
		  AND	map.src_datatype_id	= @src_datatype_id
		  AND	map.src_len_min		= @source_length_min
		  AND	map.src_len_max		= @source_length_max
		  AND	map.src_prec_min	= @source_precision_min
		  AND	map.src_prec_max	= @source_precision_max
		  AND	map.src_scale_min	= @source_scale_min
		  AND	map.src_scale_max	= @source_scale_max
		  AND	map.src_nullable	= @source_nullable
		  AND	dm.dest_datatype_id	= @dest_datatype_id
		  AND	dm.dest_length		= @destination_length
		  AND	dm.dest_precision	= @destination_precision
		  AND	dm.dest_scale		= @destination_scale
		  AND	dm.dest_nullable	= @destination_nullable
	)
	BEGIN
		RAISERROR(21657, 16, -1, @source_type)
		
		IF @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRAN adddatatypemapping
			COMMIT TRAN
		END
		RETURN (1)
	END
	
	-- Add source mapping if it doesn't exist
	SELECT	@map_id = map_id
	FROM	msdb.dbo.MSdbms_map
	WHERE	src_dbms_id		= @src_dbms_id
	  AND	dest_dbms_id	= @dest_dbms_id
	  AND	src_datatype_id	= @src_datatype_id
	  AND	src_len_min		= @source_length_min
	  AND	src_len_max		= @source_length_max
	  AND	src_prec_min	= @source_precision_min
	  AND	src_prec_max	= @source_precision_max
	  AND	src_scale_min	= @source_scale_min
	  AND	src_scale_max	= @source_scale_max
	  AND	src_nullable	= @source_nullable

	IF @map_id IS NULL OR @@ERROR <> 0
	BEGIN
		INSERT INTO msdb.dbo.MSdbms_map
		(
			src_dbms_id,
			dest_dbms_id,
			src_datatype_id,
			src_len_min,
			src_len_max,
			src_prec_min,
			src_prec_max,
			src_scale_min,
			src_scale_max,
			src_nullable
		)
		VALUES
		(
			@src_dbms_id,
			@dest_dbms_id,
			@src_datatype_id,
			@source_length_min,
			@source_length_max,
			@source_precision_min,
			@source_precision_max,
			@source_scale_min,
			@source_scale_max,
			@source_nullable
		)
		
		IF @@ERROR <> 0
		BEGIN
			IF @@TRANCOUNT > 0
			BEGIN
				ROLLBACK TRANSACTION adddatatypemapping
				COMMIT TRAN
			END
			RETURN (1)
		END
		
		SELECT @map_id = IDENT_CURRENT('msdb.dbo.MSdbms_map')
	END
	
	-- Determine create params
	IF @destination_createparams IS NULL
	BEGIN
		SET @destination_createparams = 0

		IF @destination_precision IS NOT NULL
			SELECT @destination_createparams = @destination_createparams | 1

		IF @destination_scale IS NOT NULL
			SELECT @destination_createparams = @destination_createparams | 2

		IF @destination_length IS NOT NULL
			SELECT @destination_createparams = @destination_createparams | 4
	END
	
	-- Add data type mapping
	INSERT INTO msdb.dbo.MSdbms_datatype_mapping
	(
		map_id,
		dest_datatype_id,
		dest_length,
		dest_precision,
		dest_scale,
		dest_nullable,
		dest_createparams,
		dataloss
	)
	VALUES
	(
		@map_id,
		@dest_datatype_id,
		@destination_length,
		@destination_precision,
		@destination_scale,
		@destination_nullable,
		@destination_createparams,
		@dataloss
	)
	
	IF @@ERROR <> 0
	BEGIN
		IF @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRANSACTION adddatatypemapping
			COMMIT TRAN
		END
		RETURN (1)
	END
	
	SELECT @datatype_mapping_id = IDENT_CURRENT('msdb.dbo.MSdbms_datatype_mapping')

	IF NOT EXISTS
	(
		SELECT	map_id
		FROM	msdb.dbo.MSdbms_map
		WHERE	map_id = @map_id
		  AND	default_datatype_mapping_id IS NOT NULL
	) OR (@is_default = 1)
	BEGIN
		EXEC @retcode = sys.sp_MSrepl_setdefaultdatatype
						@map_id					= @map_id,
						@datatype_mapping_id	= @datatype_mapping_id
						
		IF @retcode <> 0 OR @@ERROR <> 0
		BEGIN
			IF @@TRANCOUNT > 0
			BEGIN
				ROLLBACK TRANSACTION adddatatypemapping
				COMMIT TRAN
			END
			RETURN (1)
		END
	END

	COMMIT TRAN
	RETURN (@retcode)		
END

 
Last revision 2008RTM
See also

  sp_adddatatypemapping (Procedure)
sp_add_datatype_mapping (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSrepl_MSSQL_DB2_datatypemappings (Procedure)
sp_MSrepl_MSSQL_ORA_datatypemappings (Procedure)
sp_MSrepl_MSSQL_SAS_datatypemappings (Procedure)
sp_MSrepl_ORA_MSSQL_datatypemappings (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