Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_ORA_MSSQL_datatypemappings

  No additional text.


Syntax


-- Name:
--		sp_MSrepl_ORA_MSSQL_datatypemappings

-- Description:
--		Define Oracle to SQL Server data type mappings

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

-- Security:
--		public

-- Notes:
--		


CREATE PROCEDURE sys.sp_MSrepl_ORA_MSSQL_datatypemappings
(
	@source_dbms			sysname,
	@source_version			sysname = NULL,
	@destination_dbms		sysname,
	@destination_version		sysname = NULL
)
AS
BEGIN
    DECLARE @msg nvarchar(4000)
    SELECT @msg = 'Creating datatype mappings for ' +
                    @source_dbms +
                    CASE
                        WHEN @source_version IS NOT NULL THEN @source_version
                        ELSE N''
                    END + ' to ' +
                    @destination_dbms +
                    CASE
                        WHEN @destination_version IS NOT NULL THEN @destination_version
                        ELSE N''
                    END
    PRINT @msg

	-- CHAR(n)
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'CHAR',
											@source_length_min		= 1,
											@source_length_max		= 2000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'char',
											@destination_length		= -1,
											@destination_nullable	= 1,
											@is_default				= 1
								
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'CHAR',
											@source_length_min		= 1,
											@source_length_max		= 2000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'nchar',
											@destination_length		= -1,
											@destination_nullable	= 1

	-- NCHAR(n)
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NCHAR',
											@source_length_min		= 1,
											@source_length_max		= 2000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'nchar',
											@destination_length		= -1,
											@destination_nullable	= 1,
											@is_default				= 1

	-- VARCHAR2(n)
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'VARCHAR2',
											@source_length_min		= 1,
											@source_length_max		= 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'varchar',
											@destination_length		= -1,
											@destination_nullable	= 1,
											@is_default				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'VARCHAR2',
											@source_length_min		= 1,
											@source_length_max		= 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'nvarchar',
											@destination_length		= -1,
											@destination_nullable	= 1

	-- NVARCHAR2(n)
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NVARCHAR2',
											@source_length_min		= 1,
											@source_length_max		= 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'nvarchar',
											@destination_length		= -1,
											@destination_nullable	= 1,
											@is_default				= 1

	-- LONG
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'LONG',
											@source_length_min		= 0,
											@source_length_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'text',
											@destination_nullable	= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'LONG',
											@source_length_min		= 0,
											@source_length_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'ntext',
											@destination_nullable	= 1,
											@dataloss               = 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'LONG',
											@source_length_min		= 0,
											@source_length_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'varchar(max)',
											@destination_nullable	= 1,
											@dataloss               = 1,
											@is_default				= 1
	
	-- CLOB
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'CLOB',
											@source_length_min		= 1,
											@source_length_max		= 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'text',
											@destination_nullable	= 1,
                                            @dataloss               = 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'CLOB',
											@source_length_min		= 1,
											@source_length_max		= 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'ntext',
											@destination_nullable	= 1,
                                            @dataloss               = 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'CLOB',
											@source_length_min		= 1,
											@source_length_max		= 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'varchar(max)',
											@destination_nullable	= 1,
                                            @dataloss               = 1,
											@is_default				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'CLOB',
											@source_length_min		= 1,
											@source_length_max		= 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'nvarchar(max)',
											@destination_nullable	= 1,
                                            @dataloss               = 1

	IF @source_version LIKE '8%'
	BEGIN
		-- NCLOB (Oracle 8)
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
												@source_version			= @source_version,
												@source_type			= 'NCLOB',
												@source_length_min		= 1,
												@source_length_max		= 4000,
												@source_nullable		= 1,
												@destination_dbms		= @destination_dbms,
												@destination_version	= @destination_version,
												@destination_type		= 'ntext',
												@destination_nullable	= 1,
                                                @dataloss               = 1

		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
												@source_version			= @source_version,
												@source_type			= 'NCLOB',
												@source_length_min		= 1,
												@source_length_max		= 4000,
												@source_nullable		= 1,
												@destination_dbms		= @destination_dbms,
												@destination_version	= @destination_version,
												@destination_type		= 'nvarchar(max)',
												@destination_nullable	= 1,
                                                @dataloss               = 1,
 												@is_default				= 1

	END
	ELSE
	BEGIN
		-- NCLOB (Oracle 9+)
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
												@source_version			= @source_version,
												@source_type			= 'NCLOB',
												@source_length_min		= 1,
												@source_length_max		= 2000,
												@source_nullable		= 1,
												@destination_dbms		= @destination_dbms,
												@destination_version	= @destination_version,
												@destination_type		= 'ntext',
												@destination_nullable	= 1,
                                                @dataloss               = 1

		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
												@source_version			= @source_version,
												@source_type			= 'NCLOB',
												@source_length_min		= 1,
												@source_length_max		= 2000,
												@source_nullable		= 1,
												@destination_dbms		= @destination_dbms,
												@destination_version	= @destination_version,
												@destination_type		= 'nvarchar(max)',
												@destination_nullable	= 1,
                                                @dataloss               = 1,
 												@is_default				= 1
	END

	-- NUMBER (INT) == ORACLE 38-bit precision INTEGER ( NUMBER(NULL,0) )
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_scale_min		= 0,
											@source_scale_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'numeric',
											@destination_precision	= 38,
											@destination_scale		= 0,
											@destination_nullable	= 1,
											@is_default				= 1


	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_scale_min		= 0,
											@source_scale_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'int',
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_scale_min		= 0,
											@source_scale_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'tinyint',
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_scale_min		= 0,
											@source_scale_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'smallint',
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_scale_min		= 0,
											@source_scale_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'bigint',
											@destination_nullable	= 1,
											@dataloss				= 1
	
	-- NUMBER(p)
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'numeric',
											@destination_precision	= -1,
											@destination_nullable	= 1,
											@is_default				= 1

	/*
	 * INT mappings are left up to the user to add themselves in order to simplify choices
	 *
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'tinyint',
											@destination_nullable	= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'smallint',
											@destination_nullable	= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'int',
											@destination_nullable	= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'bigint',
											@destination_nullable	= 1
	*
	*/

	-- NUMBER (x,0)
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_scale_min		= 0,
											@source_scale_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'numeric',
											@destination_precision	= -1,
											@destination_scale		= 0,
											@destination_nullable	= 1,
											@is_default				= 1


	/*
	 * INT mappings are left up to the user to add themselves in order to simplify choices
	 *
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_scale_min		= 0,
											@source_scale_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'int',
											@destination_nullable	= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_scale_min		= 0,
											@source_scale_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'tinyint',
											@destination_nullable	= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_scale_min		= 0,
											@source_scale_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'smallint',
											@destination_nullable	= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_scale_min		= 0,
											@source_scale_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'bigint',
											@destination_nullable	= 1
	*
	*/

	-- NUMBER(p,s) where s > 0
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 38,
											@source_scale_min		= 1,
											@source_scale_max		= 38,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'numeric',
											@destination_precision	= -1,
											@destination_scale		= -1,
											@destination_nullable	= 1,
											@is_default				= 1

	-- NUMBER
	-- Default equivalent to max standard SQL Server numeric value
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'float',
											@destination_nullable	= 1,
											@dataloss				= 1,
											@is_default				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'numeric',
											@destination_precision	= 38,
											@destination_scale		= 8,
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'numeric',
											@destination_precision	= 38,
											@destination_scale		= 0,
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'numeric',
											@destination_precision	= 38,
											@destination_scale		= 38,
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'real',
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'varchar',
											@destination_length		= 40,
											@destination_nullable	= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'tinyint',
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'smallint',
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'int',
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'NUMBER',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'bigint',
											@destination_nullable	= 1,
											@dataloss				= 1

	-- FLOAT
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'FLOAT',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'float',
											@destination_nullable	= 1,
											@dataloss				= 1,
											@is_default				= 1

	-- FLOAT(p) 1-53
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'FLOAT',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 1,
											@source_precision_max	= 53,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'float',
											@destination_precision	= -1,
											@destination_nullable	= 1,
											@dataloss				= 1,
											@is_default				= 1

	-- FLOAT(p) 54-62
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'FLOAT',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 54,
											@source_precision_max	= 62,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'float',
											@destination_nullable	= 1,
											@dataloss				= 1,
											@is_default				= 1

	-- REAL (FLOAT(63)
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'FLOAT',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 63,
											@source_precision_max	= 63,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'float',
											@destination_nullable	= 1,
											@dataloss				= 1,
											@is_default				= 1
	
	-- FLOAT(p) 64-126
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'FLOAT',
											@source_length_min		= 22,
											@source_length_max		= 22,
											@source_precision_min	= 64,
											@source_precision_max	= 126,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'float',
											@destination_nullable	= 1,
											@dataloss				= 1,
											@is_default				= 1
	
	-- RAW
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'RAW',
											@source_length_min		= 1,
											@source_length_max		= 2000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'varbinary',
											@destination_length		= -1,
											@destination_nullable	= 1,
											@is_default				= 1

	-- LONG RAW
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'LONG RAW',
											@source_length_min		= 0,
											@source_length_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'varbinary(max)',
											@destination_nullable	= 1,
											@is_default				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'LONG RAW',
											@source_length_min		= 0,
											@source_length_max		= 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'image',
											@destination_nullable	= 1,
                                            @dataloss               = 1

	-- BLOB
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'BLOB',
											@source_length_min		= 1,
											@source_length_max		= 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'image',
											@destination_nullable	= 1,
                                            @dataloss               = 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'BLOB',
											@source_length_min		= 1,
											@source_length_max		= 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'varbinary(max)',
											@destination_nullable	= 1,
                                            @dataloss               = 1,
											@is_default				= 1

	-- BFILE
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'BFILE',
											@source_length_min		= 530,
											@source_length_max		= 530,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'image',
											@destination_nullable	= 1,
                                            @dataloss               = 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'BFILE',
											@source_length_min		= 530,
											@source_length_max		= 530,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'varbinary(max)',
											@destination_nullable	= 1,
                                            @dataloss               = 1,
											@is_default				= 1

	-- DATE
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'DATE',
											@source_length_min		= 7,
											@source_length_max		= 7,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'datetime',
											@destination_nullable	= 1,
											@dataloss				= 1,
											@is_default				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'DATE',
											@source_length_min		= 7,
											@source_length_max		= 7,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'varchar',
											@destination_length		= 19,
											@destination_nullable	= 1,
											@dataloss				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'DATE',
											@source_length_min		= 7,
											@source_length_max		= 7,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'date',
											@destination_nullable	= 1,
											@dataloss				= 1

    -- TIMESTAMP (Oracle 9+ only)
    -- TIMESTAMP WITH TIME ZONE (Oracle 9+ only)
    -- TIMESTAMP WITH LOCAL TIME ZONE (Oracle 9+ only)
    IF @source_version NOT LIKE N'8%'
    BEGIN
        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 0,
                                                @source_scale_max       = 7,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'datetime',
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 1

        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 0,
                                                @source_scale_max       = 7,
                                                @source_nullable        = 1,
              @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'varchar',
                                                @destination_length     = 35,
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 0

        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 0,
                                                @source_scale_max       = 7,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'datetime2',
                                                @destination_scale      = -1,
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 0


        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 8,
                                                @source_scale_max       = 9,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'datetime',
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 1

        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 8,
                                                @source_scale_max       = 9,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'varchar',
                                                @destination_length     = 35,
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 0



/*
        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP WITH TIME ZONE',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 0,
                                                @source_scale_max       = 9,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'datetime',
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 1
*/
        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP WITH TIME ZONE',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 0,
                                                @source_scale_max       = 7,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'varchar',
                                                @destination_length     = 37,
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 1

        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP WITH TIME ZONE',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 0,
                                                @source_scale_max       = 7,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'datetimeoffset',
                                                @destination_scale      = -1,
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 0

        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP WITH TIME ZONE',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 8,
                                                @source_scale_max       = 9,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'varchar',
                                                @destination_length     = 37,
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 1

/*
        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP WITH LOCAL TIME ZONE',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 0,
                                                @source_scale_max       = 9,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'datetime',
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 0
*/
        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP WITH LOCAL TIME ZONE',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 0,
                                                @source_scale_max       = 7,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'varchar',
                                                @destination_length     = 37,
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 1

        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP WITH LOCAL TIME ZONE',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 0,
                                                @source_scale_max       = 7,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'datetimeoffset',
                                                @destination_scale     = -1,
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 0


        exec sys.sp_MSrepl_adddatatypemapping   @source_dbms            = @source_dbms,
                                                @source_version         = @source_version,
                                                @source_type            = 'TIMESTAMP WITH LOCAL TIME ZONE',
                                                @source_length_min      = 7,
                                                @source_length_max      = 13,
                                                @source_scale_min       = 8,
                                                @source_scale_max       = 9,
                                                @source_nullable        = 1,
                                                @destination_dbms       = @destination_dbms,
                                                @destination_version    = @destination_version,
                                                @destination_type       = 'varchar',
                                                @destination_length     = 37,
                                                @destination_nullable   = 1,
                                                @dataloss               = 1,
                                                @is_default             = 1
    END




	-- ROWID
    exec sys.sp_MSrepl_adddatatypemapping   @source_dbms			= @source_dbms,
	                                        @source_version         = @source_version,
	                                        @source_type            = 'ROWID',
	                                        @source_length_min      = 1,
	                                        @source_length_max      = 4000,
	                                        @source_nullable        = 1,
	                                        @destination_dbms       = @destination_dbms,
	                                        @destination_version    = @destination_version,
	                                        @destination_type       = 'char',
	                                        @destination_length		= 18,
	                                        @destination_nullable   = 1,
	                                        @dataloss               = 1,
	                                        @is_default             = 1

	-- UROWID
    exec sys.sp_MSrepl_adddatatypemapping   @source_dbms			= @source_dbms,
	                                        @source_version         = @source_version,
	                                        @source_type            = 'UROWID',
	                                        @source_length_min      = 1,
	                                        @source_length_max      = 4000,
	                                        @source_nullable        = 1,
	                                        @destination_dbms       = @destination_dbms,
	                                        @destination_version    = @destination_version,
	                                        @destination_type       = 'char',
	                                        @destination_length		= 18,
	                                        @destination_nullable   = 1,
	                                        @dataloss               = 1,
	                                        @is_default             = 1
END

 
Last revision 2008RTM
See also

  sp_MSdrop_replcom (Procedure)
sp_MSrepl_createdatatypemappings (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