Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_MSSQL_ORA_datatypemappings

  No additional text.


Syntax


-- Name:
--		sp_MSrepl_MSSQL_ORA_datatypemappings

-- Description:
--		Define SQL -> Oracle subscriber mappings

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

-- Security:
--		Internal


CREATE PROCEDURE sys.sp_MSrepl_MSSQL_ORA_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

	-- bigint
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'bigint',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NUMBER',
											@destination_precision	= 19,
											@destination_scale      = 0,
											@destination_nullable	= 1,
											@is_default				= 1

	-- binary
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'binary',
											@source_length_min      = 1,
											@source_length_max      = 2000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'RAW',
											@destination_length     = -1,
--											@destination_length     = 2000,
											@destination_nullable	= 1,
											@is_default				= 1
											
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'binary',
											@source_length_min      = 2001,
											@source_length_max      = 8000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'BLOB',
											@destination_nullable	= 1,
											@is_default				= 1

	-- bit
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'bit',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NUMBER',
                                            @destination_precision  = 1,
                                            @destination_scale      = 0,
											@destination_nullable	= 1,
											@is_default				= 1

	-- char
	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_length     = 2000,
											@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      = 2001,
											@source_length_max      = 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'VARCHAR2',
											@destination_length     = -1,
--											@destination_length     = 4000,
											@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      = 4001,
											@source_length_max      = 8000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CLOB',
											@destination_nullable	= 1,
											@is_default				= 1
    -- date
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'date',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'DATE',
											@destination_nullable	= 1,
											@is_default				= 1

    -- datetime
	IF @destination_version LIKE '8%'
    BEGIN
	    exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
		    									@source_version			= @source_version,
			    								@source_type			= 'datetime',
				    							@source_nullable		= 1,
					    						@destination_dbms		= @destination_dbms,
						    					@destination_version	= @destination_version,
							    				@destination_type		= 'VARCHAR2',
								    			@destination_length     = 27,
									    		@destination_nullable	= 1,
										    	@is_default				= 1

    END
    ELSE
	BEGIN
	    exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
		    									@source_version			= @source_version,
			    								@source_type			= 'datetime',
				    							@source_nullable		= 1,
					    						@destination_dbms		= @destination_dbms,
						    					@destination_version	= @destination_version,
							    				@destination_type		= 'TIMESTAMP',
								    			@destination_length     = 3,
									    		@destination_nullable	= 1,
										    	@is_default				= 1
    END

    -- datetime2
	IF @destination_version LIKE '8%'
    BEGIN
	    exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
		    									@source_version			= @source_version,
			    								@source_type			= 'datetime2',
				    							@source_nullable		= 1,
					    						@destination_dbms		= @destination_dbms,
						    					@destination_version	= @destination_version,
							    				@destination_type		= 'VARCHAR2',
								    			@destination_length     = 27,
									    		@destination_nullable	= 1,
										    	@is_default				= 1

    END
    ELSE
	BEGIN
	    exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
		    									@source_version			= @source_version,
			    								@source_type			= 'datetime2',
				    							@source_nullable		= 1,
					    						@destination_dbms		= @destination_dbms,
						    					@destination_version	= @destination_version,
							    				@destination_type		= 'TIMESTAMP',
								    			@destination_length     = 7,
									    		@destination_nullable	= 1,
										    	@is_default				= 1
    END

    -- datetimeoffset
	IF @destination_version LIKE '8%'
    BEGIN
	    exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
		    									@source_version			= @source_version,
			    								@source_type			= 'datetimeoffset',
				    							@source_nullable		= 1,
					    						@destination_dbms		= @destination_dbms,
						    					@destination_version	= @destination_version,
							    				@destination_type		= 'VARCHAR2',
								    			@destination_length     = 34,
									    		@destination_nullable	= 1,
										    	@is_default				= 1

    END
    ELSE
	BEGIN
	    exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
		    									@source_version			= @source_version,
			    								@source_type			= 'datetimeoffset',
				    							@source_nullable		= 1,
					    						@destination_dbms		= @destination_dbms,
						    					@destination_version	= @destination_version,
							    				@destination_type		= 'TIMESTAMP (7) WITH TIME ZONE',
									    		@destination_nullable	= 1,
										    	@is_default				= 1
    END

	-- decimal
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'decimal',
											@source_precision_min   = 1,
											@source_precision_max   = 38,
											@source_scale_min       = 0,
											@source_scale_max       = 38,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NUMBER',
											@destination_precision  = -1,
											@destination_scale      = -1,
--											@destination_precision  = 38,
--											@destination_scale      = 38,
											@destination_nullable	= 1,
											@is_default				= 1

	-- double precision
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'double precision',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'FLOAT',
											@destination_nullable	= 1,
											@is_default				= 1

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

	-- geography
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'geography',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'BLOB',
											@destination_nullable	= 1,
											@is_default				= 1

	-- geometry
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'geometry',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'BLOB',
											@destination_nullable	= 1,
											@is_default				= 1

	-- hierarchyid
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'hierarchyid',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'BLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	-- image
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'image',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'BLOB',
											@destination_nullable	= 1,
											@is_default				= 1

	-- int
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'int',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NUMBER',
											@destination_precision  = 10,
											@destination_scale      = 0,
											@destination_nullable	= 1,
											@is_default				= 1

	-- money
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'money',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NUMBER',
											@destination_precision  = 19,
											@destination_scale      = 4,
											@destination_nullable	= 1,
											@is_default				= 1
	IF @destination_version LIKE '8%'
	BEGIN
		-- nchar
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nchar',
											@source_length_min      = 1,
											@source_length_max      = 1000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CHAR',
											@destination_length     = -1,
--											@destination_length     = 1000,
											@destination_nullable	= 1,
											@is_default				= 1
	
											
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nchar',
											@source_length_min      = 1001,
											@source_length_max      = 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	END
	ELSE
	BEGIN
		-- nchar
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nchar',
											@source_length_min      = 1,
											@source_length_max      = 1000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NCHAR',
											@destination_length     = -1,
--											@destination_length     = 1000,
											@destination_nullable	= 1,
											@is_default				= 1
											
	
											
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nchar',
											@source_length_min      = 1001,
											@source_length_max      = 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NCLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	END

	IF @destination_version LIKE '8%'
	BEGIN
		-- ntext
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'ntext',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CLOB',
											@destination_nullable	= 1,
											@is_default		= 1
	END
	ELSE
	BEGIN
		-- ntext
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'ntext',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NCLOB',
											@destination_nullable	= 1,
											@is_default		= 1
	END

	-- numeric
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'numeric',
											@source_precision_min   = 1,
											@source_precision_max   = 38,
											@source_scale_min       = 0,
											@source_scale_max       = 38,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NUMBER',
											@destination_precision  = -1,
											@destination_scale      = -1,
--											@destination_precision  = 38,
--											@destination_scale      = 38,
											@destination_nullable	= 1,
											@is_default				= 1
	IF @destination_version LIKE '8%'
	BEGIN
		-- nvarchar
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nvarchar',
											@source_length_min      = 1,
											@source_length_max      = 2000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'VARCHAR2',
											@destination_length     = -1,
--											@destination_length     = 2000,
											@destination_nullable	= 1,
											@is_default				= 1
											
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nvarchar',
											@source_length_min      = 2001,
											@source_length_max      = 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	END
	ELSE
	BEGIN

		-- nvarchar
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nvarchar',
											@source_length_min      = 1,
											@source_length_max      = 1000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NVARCHAR2',
											@destination_length     = -1,
--											@destination_length     = 2000,
											@destination_nullable	= 1,
											@is_default				= 1
											
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nvarchar',
											@source_length_min      = 1001,
											@source_length_max      = 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NCLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	END

	-- real
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'real',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'REAL',
											@destination_nullable	= 1,
											@is_default				= 1
											
	-- smalldatetime
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'smalldatetime',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'DATE',
											@destination_nullable	= 1,
											@is_default				= 1

	-- smallint
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'smallint',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NUMBER',
											@destination_precision  = 5,
											@destination_scale      = 0,
											@destination_nullable	= 1,
											@is_default				= 1

	-- smallmoney
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'smallmoney',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NUMBER',
											@destination_precision  = 10,
											@destination_scale      = 4,
											@destination_nullable	= 1,
											@is_default				= 1

    -- sql_variant
	IF @destination_version LIKE '8%'
	BEGIN
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'sql_variant',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	END
	ELSE
	BEGIN
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'sql_variant',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NCLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	END

    -- sysname
	IF @destination_version LIKE '8%'
	BEGIN
		-- sysname
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'sysname',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'VARCHAR2',
											@destination_length     = 128,
											@destination_nullable	= 1,
											@is_default				= 1
	END
	ELSE
	BEGIN
		-- sysname
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'sysname',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NVARCHAR2',
											@destination_length     = 128,
											@destination_nullable	= 1,
											@is_default				= 1
	END

	-- text
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'text',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CLOB',
											@destination_nullable	= 1,
											@is_default				= 1
    --time
	IF @destination_version LIKE '8%'
    BEGIN
	    exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
		    									@source_version			= @source_version,
			    								@source_type			= 'time',
				    							@source_nullable		= 1,
					    						@destination_dbms		= @destination_dbms,
						    					@destination_version	= @destination_version,
							    				@destination_type		= 'VARCHAR2',
								    			@destination_length     = 16,
									    		@destination_nullable	= 1,
										    	@is_default				= 1

    END
    ELSE
	BEGIN
	    exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
		    									@source_version			= @source_version,
			    								@source_type			= 'time',
				    							@source_nullable		= 1,
					    						@destination_dbms		= @destination_dbms,
						    					@destination_version	= @destination_version,
							    				@destination_type		= 'VARCHAR2',
								    			@destination_length     = 16,
									    		@destination_nullable	= 1,
										    	@is_default				= 1

/*
	    exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
		    									@source_version			= @source_version,
			    								@source_type			= 'time',
				    							@source_nullable		= 1,
					    						@destination_dbms		= @destination_dbms,
						    					@destination_version	= @destination_version,
							    				@destination_type		= 'TIMESTAMP',
								    			@destination_length     = 7,
									    		@destination_nullable	= 1,
										    	@is_default				= 1
*/
    END

	-- timestamp
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'timestamp',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'RAW',
											@destination_length     = 8,
											@destination_nullable	= 1,
											@is_default				= 1

	-- tinyint
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'tinyint',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NUMBER',
											@destination_precision  = 3,
											@destination_scale      = 0,
											@destination_nullable	= 1,
											@is_default				= 1

	-- uniqueidentifier
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'uniqueidentifier',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CHAR',
											@destination_length     = 38,
											@destination_nullable	= 1,
											@is_default				= 1

	-- varbinary
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'varbinary',
											@source_length_min      = 1,
											@source_length_max      = 2000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'RAW',
											@destination_length     = -1,
--											@destination_length     = 2000,
											@destination_nullable	= 1,
											@is_default				= 1

	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'varbinary',
											@source_length_min      = 2001,
											@source_length_max      = 8000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'BLOB',
											@destination_nullable	= 1,
											@is_default				= 1
											
	-- varchar
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'varchar',
											@source_length_min      = 1,
											@source_length_max      = 4000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'VARCHAR2',
											@destination_length     = -1,
--											@destination_length     = 4000,
											@destination_nullable	= 1,
											@is_default				= 1
											
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'varchar',
											@source_length_min      = 4001,
											@source_length_max      = 8000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	IF @destination_version LIKE '8%'
	BEGIN
		-- xml
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'xml',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	END
	ELSE
	BEGIN
		
		-- xml
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'xml',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NCLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	END

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

	-- varchar(max)
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'varchar(max)',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	IF @destination_version LIKE '8%'
	BEGIN
		-- nvarchar(max)
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nvarchar(max)',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	END
	ELSE
	BEGIN
		-- nvarchar(max)
		exec sys.sp_MSrepl_adddatatypemapping	@source_dbms		= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nvarchar(max)',
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'NCLOB',
											@destination_nullable	= 1,
											@is_default				= 1
	END
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