- Microsoft SQL Server
- System tables
- Procedure
- Views
- Functions
|
sys.sp_MSmap_subscriber_type
Syntax
|
 |
 |
 |
|
create procedure sys.sp_MSmap_subscriber_type (
@user_type_id int,
@len smallint,
@prec int,
@scale int,
@schema_option varbinary(8),
@type nvarchar(258) output,
@schname nvarchar(140) output,
@maplen smallint =0 output
)
as
declare @typeout nvarchar(258)
declare @schema_option_lodword int
declare @schema_option_hidword int
declare @system_type_id int
select @maplen=0
select @schema_option_lodword = sys.fn_replgetbinary8lodword(@schema_option)
select @schema_option_hidword = sys.fn_replgetbinary8hidword(@schema_option)
select @system_type_id = system_type_id from sys.types where user_type_id = @user_type_id
-- This schema option implies that user defined types should be mapped ot base types.
if ((@schema_option_lodword & 0x00000020) = 0x00000020)
begin
select @type = type_name(system_type_id) from sys.types where user_type_id = @user_type_id
if @type is NULL
select @type = 'image'
-- don't append the schema owner qualification if it is mapped to base types
-- bug #414067
select @schname = N''
end
else
select @type = type_name(@user_type_id)
select @typeout = case @type
when 'binary' then 'varbinary'
when 'char' then 'varchar'
when 'nchar' then 'nvarchar'
when 'datetimn' then 'datetime'
when 'decimaln' then 'decimal'
when 'floatn' then 'float'
when 'intn' then 'int'
when 'moneyn' then 'money'
when 'numericn' then 'numeric'
when 'timestamp' then 'varbinary'
when 'bit' then 'tinyint'
else @type
END
-- Quote UDTs. Systems types are left alone as there are a number of places where we do
-- text based name comparisons for system types.
if exists (select * from sys.types where name = @typeout and is_user_defined = 1)
begin
select @typeout = quotename(@typeout)
end
-- HierarchyIdToVarBinaryMax
if @schema_option_hidword & 0x00000020 <> 0 and @system_type_id = 240 and @typeout='hierarchyid'
begin
if ((@schema_option_lodword & 0x20000000) <> 0)
select @type = 'image'
else
select @type = 'varbinary(max)'
select @schname = N''
return
end
-- SpatialTypesToVarBinaryMax
if @schema_option_hidword & 0x00000080 <> 0 and @system_type_id = 240 and (@typeout='geometry' or @typeout='geography')
begin
if ((@schema_option_lodword & 0x20000000) <> 0)
select @type = 'image'
else
select @type = 'varbinary(max)'
select @schname = N''
return
end
-- LargeUserDefinedTypesToVarBinaryMax
if (@len = -1 and
@schema_option_hidword & 0x00000010 <> 0 and
@system_type_id = 240)
begin
select @typeout = 'varbinary'
-- don't append the schema owner qualification if it is mapped to base types
select @schname = N''
end
-- append length or scale and precision if needed
if (@typeout = 'varbinary' or @typeout = 'varchar' or @typeout = 'nvarchar')
begin
if @len=0 or @len=-1
begin
-- if the following is true map max types to matching non max types is set
if ((@schema_option_lodword & 0x20000000) = 0x20000000)
begin
-- nvarchar(max) -> ntext
-- varchar(max) -> text
-- varbinary(max) -> image
if @typeout = 'nvarchar'
select @type = 'ntext'
if @typeout = 'varchar'
select @type = 'text'
if @typeout = 'varbinary'
select @type = 'image'
end
else
begin
select @type = @typeout + '(max)'
end
end
else
begin
select @type = @typeout + '(' + rtrim(convert(nchar, @len)) + ')'
end
return
end
if (@typeout = 'numeric' or @typeout = 'decimal')
begin
select @type = @typeout + '(' + rtrim(convert(nchar, @prec)) + ',' +
rtrim(convert(nchar, @scale)) + ')'
return
end
--Convert date, time, datetime2, datetimeoffset to character data
--for subscribers that do not support these types
--ConvertNewDateTimeTypesToStrings = 0x0000000200000000
if ((@schema_option_hidword & 0x00000002) = 0x00000002)
begin
if @typeout = 'date'
begin
select @type = 'nchar (10)'
select @maplen=10 * 2
return
end
if @typeout = 'time'
begin
select @type = 'nvarchar (16)'
select @maplen=16 * 2
return
end
if @typeout = 'datetime2'
begin
select @type = 'nvarchar (27)'
select @maplen=27 *2
return
end
if @typeout = 'datetimeoffset'
begin
select @type = 'nvarchar (34)'
select @maplen=34*2
return
end
end
-- now check for UDT, UDF and other special cases. Check the schema_options to see
-- if any of these special types should be transformed.
declare @schema_option_xml_to_ntext int
if (@typeout = 'xml' and ((@schema_option_lodword & 0x10000000) = 0x10000000)) -- @schema_option_xml_to_ntext
begin
select @typeout = 'ntext'
-- don't append the schema owner qualification if it is mapped to base types
-- bug #414067
select @schname = N''
end
select @type = @typeout
|
|
|
|
|
|
|
|
Last revision 2008RTM |
|
|
|
|
|
See also
News
|