Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_setdefaultdatatypemapping

  No additional text.


Syntax


-- Name:
--		sp_setdefaultdatatypemapping

-- Description:
--		Mark data type mapping as default

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

-- Security:
--		public

-- Notes:
--		Data type mapping must already exist to be marked
--		as the default


CREATE PROCEDURE sys.sp_setdefaultdatatypemapping
(
	@mapping_id				int = NULL,
	@source_dbms			sysname = NULL,
	@source_version			varchar(10) = NULL,
	@source_type			sysname = NULL,
	@source_length_min		bigint = NULL,
	@source_length_max		bigint = NULL,
	@source_precision_min	bigint = NULL,
	@source_precision_max	bigint = NULL,
	@source_scale_min		int = NULL,
	@source_scale_max		int = NULL,
	@source_nullable		bit = NULL,
	@destination_dbms		sysname = NULL,
	@destination_version	varchar(10) = NULL,
	@destination_type		sysname = NULL,
	@destination_length		bigint = NULL,
	@destination_precision	bigint = NULL,
	@destination_scale		int = NULL,
	@destination_nullable	bit = NULL
)
AS
BEGIN
	DECLARE @retcode				int
	DECLARE @src_dbms_id			int
	DECLARE @src_datatype_id		int
	DECLARE @dest_dbms_id			int
	DECLARE @dest_datatype_id		int
	DECLARE @map_id					int
	DECLARE @datatype_mapping_id	int
	
    SET NOCOUNT ON

	-- Prepare dbms for case insensitive searches
	SET @source_dbms		= UPPER(@source_dbms)
	SET @destination_dbms	= UPPER(@destination_dbms)

	set @retcode = 0

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

	-- Check parameters
	IF (@mapping_id IS NULL)
	BEGIN
		-- Require source dbms
		IF (@source_dbms IS NULL) OR (@source_dbms = N'')
		BEGIN
			RAISERROR(7801, 16, -1, 128, N'@source_dbms')
			RETURN (1)
		END

		-- Require source type
		IF (@source_type IS NULL) OR (@source_type = N'')
		BEGIN
			RAISERROR(7801, 16, -1, 128, N'@source_type')
			RETURN (1)
		END

		-- Require destination dbms
		IF (@destination_dbms IS NULL) OR (@destination_dbms = N'')
		BEGIN
			RAISERROR(7801, 16, -1, 128, N'@destination_dbms')
			RETURN (1)
		END

		-- Require destination type
		IF (@destination_type IS NULL) OR (@destination_type = N'')
		BEGIN
			RAISERROR(7801, 16, -1, 128, N'@destination_type')
			RETURN (1)
		END

		EXEC @retcode = sys.sp_MSrepl_getdbmsinfo
						@dbms			= @source_dbms,
						@version		= @source_version,
						@type			= @source_type,
						@dbms_id		= @src_dbms_id OUTPUT,
						@datatype_id	= @src_datatype_id OUTPUT

		IF @retcode <> 0 OR @@ERROR <> 0
		BEGIN
			RETURN (@retcode)
		END

		EXEC @retcode = sys.sp_MSrepl_getdbmsinfo
						@dbms			= @destination_dbms,
						@version		= @destination_version,
						@type			= @destination_type,
						@dbms_id		= @dest_dbms_id OUTPUT,
						@datatype_id	= @dest_datatype_id OUTPUT

		IF @retcode <> 0 OR @@ERROR <> 0
		BEGIN
			RETURN (@retcode)
		END
	END

	BEGIN TRAN
	SAVE TRAN setdefaultdatatypemapping

	IF (@mapping_id IS NULL)
	BEGIN
		-- Get source mapping
		SELECT	@map_id					= map.map_id,
				@datatype_mapping_id	= dm.datatype_mapping_id
		FROM	msdb.dbo.MSdbms_map map,
				msdb.dbo.MSdbms_datatype_mapping dm
		WHERE	map.map_id			= dm.map_id
		  AND	map.src_dbms_id		= @src_dbms_id
		  AND	map.dest_dbms_id	= @dest_dbms_id
		  AND	map.src_datatype_id	= @src_datatype_id
		  AND	map.src_len_min		= @source_length_min
		  AND	map.src_len_max		= @source_length_max
		  AND	map.src_prec_min	= @source_precision_min
		  AND	map.src_prec_max	= @source_precision_max
		  AND	map.src_scale_min	= @source_scale_min
		  AND	map.src_scale_max	= @source_scale_max
		  AND	map.src_nullable	= @source_nullable
		  AND	dm.dest_datatype_id	= @dest_datatype_id
		  AND	dm.dest_length		= @destination_length
		  AND	dm.dest_precision	= @destination_precision
		  AND	dm.dest_scale		= @destination_scale
		  AND	dm.dest_nullable	= @destination_nullable

		IF @map_id IS NULL OR @datatype_mapping_id IS NULL OR @@ERROR <> 0
		BEGIN
			RAISERROR (21656, 16, -1, @source_type)

			IF @@TRANCOUNT > 0
			BEGIN
				ROLLBACK TRANSACTION setdefaultdatatypemapping
				COMMIT TRAN
			END
			RETURN (1)
		END
	END
	ELSE
	BEGIN
		-- Set datatype mapping id
		SET @datatype_mapping_id = @mapping_id
		
		-- Get map id for specific data type mapping id
		SELECT	@map_id = dm.map_id
		FROM	msdb.dbo.MSdbms_datatype_mapping dm
		WHERE	datatype_mapping_id = @datatype_mapping_id

		IF @map_id IS NULL OR @datatype_mapping_id IS NULL OR @@ERROR <> 0
		BEGIN
			DECLARE @msg nvarchar(50)
			SELECT @msg = CONVERT(nvarchar(50), @mapping_id)
			RAISERROR (21656, 16, -1, @msg)

			IF @@TRANCOUNT > 0
			BEGIN
				ROLLBACK TRANSACTION setdefaultdatatypemapping
				COMMIT TRAN
			END
			RETURN (1)
		END
	END
	
	-- Set default
	EXEC @retcode = sys.sp_MSrepl_setdefaultdatatype
					@map_id					= @map_id,
					@datatype_mapping_id	= @datatype_mapping_id

	IF @retcode <> 0 OR @@ERROR <> 0
	BEGIN
		IF @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRANSACTION setdefaultdatatypemapping
			COMMIT TRAN
		END
		RETURN (@retcode)
	END
	
	COMMIT TRAN			
	RETURN (@retcode)		
END

 
Last revision 2008RTM
See also

  sp_MSdrop_replcom (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