June 8, 2012

sp_setdefaultdatatypemapping (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_setdefaultdatatypemapping(int @mapping_id
, nvarchar @source_dbms
, varchar @source_version
, nvarchar @source_type
, bigint @source_length_min
, bigint @source_length_max
, bigint @source_precision_min
, bigint @source_precision_max
, int @source_scale_min
, int @source_scale_max
, bit @source_nullable
, nvarchar @destination_dbms
, varchar @destination_version
, nvarchar @destination_type
, bigint @destination_length
, bigint @destination_precision
, int @destination_scale
, bit @destination_nullable)

MetaData:

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

No comments:

Post a Comment

Total Pageviews