Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_MSSQL_DB2_datatypemappings

  No additional text.


Syntax


-- Name:
--		sp_MSrepl_MSSQL_DB2_datatypemappings

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

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

-- Security:
--		Internal


CREATE PROCEDURE sys.sp_MSrepl_MSSQL_DB2_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		= 'DECIMAL',
											@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      = 254,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CHAR () FOR BIT DATA',
											@destination_length     = -1,
											@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      = 255,
											@source_length_max      = 8000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'VARCHAR () FOR BIT DATA',
											@destination_length     = -1,
											@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		= 'SMALLINT',
											@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      = 254,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'CHAR',
											@destination_length     = -1,
											@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      = 255,
											@source_length_max      = 8000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'VARCHAR',
											@destination_length     = -1,
											@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
	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_nullable	= 1,
											@is_default				= 1

    -- datetime2
/*
	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_nullable	= 1,
											@is_default				= 1
*/
	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		= 'VARCHAR',
											@destination_length 	= 27,
											@destination_nullable	= 1,
											@is_default				= 1


    -- datetimeoffset
/*
	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',
											@destination_nullable	= 1,
											@is_default				= 1
*/
	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		= 'VARCHAR',
											@destination_length 	= 34,
											@destination_nullable	= 1,
											@is_default				= 1

	-- decimal
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'decimal',
											@source_precision_min   = 1,
											@source_precision_max   = 31,
											@source_scale_min       = 0,
											@source_scale_max       = 31,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'DECIMAL',
											@destination_precision  = -1,
											@destination_scale      = -1,
											@destination_nullable	= 1,
											@is_default				= 1
	
	-- decimal
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'decimal',
											@source_precision_min   = 32,
											@source_precision_max   = 38,
											@source_scale_min       = 0,
											@source_scale_max       = 38,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_length     = 41,
											@destination_type		= 'VARCHAR',
											@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		= 'DOUBLE',
											@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		= 'VARCHAR () FOR BIT DATA',
											@destination_length     = 4000,
											@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		= 'VARCHAR () FOR BIT DATA',
											@destination_length     = 4000,
											@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		= 'VARCHAR () FOR BIT DATA',
											@destination_length     = 900,
											@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		= 'VARCHAR () FOR BIT DATA',
											@destination_length     = 0,
											@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		= 'INT',
											@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		= 'DECIMAL',
											@destination_precision  = 19,
											@destination_scale      = 4,
											@destination_nullable	= 1,
											@is_default				= 1

	-- nchar
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nchar',
											@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
	
	-- 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		= 'VARCHAR',
											@destination_length     = 0,
											@destination_nullable	= 1,
											@is_default				= 1

	-- numeric
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'numeric',
											@source_precision_min   = 1,
											@source_precision_max   = 31,
											@source_scale_min       = 0,
											@source_scale_max       = 31,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'DECIMAL',
											@destination_precision  = -1,
											@destination_scale      = -1,
											@destination_nullable	= 1,
											@is_default				= 1
	
	-- numeric
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'numeric',
											@source_precision_min   = 32,
											@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		= 'VARCHAR',
											@destination_length  = 41,
											@destination_nullable	= 1,
											@is_default				= 1

	-- nvarchar
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'nvarchar',
											@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

	-- 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		= 'TIMESTAMP',
											@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		= 'SMALLINT',
											@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		= 'DECIMAL',
											@destination_precision  = 10,
											@destination_scale      = 4,
											@destination_nullable	= 1,
											@is_default				= 1

	-- sql_variant
	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		= 'VARCHAR',
											@destination_length		= 4000,
											@destination_nullable	= 1,
											@is_default				= 1

	-- 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		= 'VARCHAR',
											@destination_length     = 128,
											@destination_nullable	= 1,
											@is_default				= 1

	-- 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		= 'VARCHAR',
											@destination_length     = 0,
											@destination_nullable	= 1,
											@is_default				= 1

    -- time
/*
	exec sys.sp_MSrepl_adddatatypemapping	@source_dbms			= @source_dbms,
											@source_version			= @source_version,
											@source_type			= 'time',
											@source_length_min      = 0,
											@source_length_max      = 0,
											@source_scale_min       = 0,
											@source_scale_max       = 0,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'TIME',
											@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		= 'VARCHAR',
											@destination_length		= 16,
											@destination_nullable	= 1,
											@is_default				= 1

	-- 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		= 'CHAR () FOR BIT DATA',
											@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		= 'SMALLINT',
											@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      = 8000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'VARCHAR () FOR BIT DATA',
											@destination_length     = -1,
											@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      = 8000,
											@source_nullable		= 1,
											@destination_dbms		= @destination_dbms,
											@destination_version	= @destination_version,
											@destination_type		= 'VARCHAR',
											@destination_length     = -1,
											@destination_nullable	= 1,
											@is_default				= 1

	-- 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		= 'VARCHAR',
											@destination_length     = 0,
											@destination_nullable	= 1,
											@is_default				= 1

	-- 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		= 'VARCHAR () FOR BIT DATA',
											@destination_length     = 0,
											@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		= 'VARCHAR',
											@destination_length     = 0,
											@destination_nullable	= 1,
											@is_default				= 1

	-- 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		= 'VARCHAR',
											@destination_length     = 0,
											@destination_nullable	= 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