Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmap_subscriber_type

  No additional text.


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

  sp_addmergearticle (Procedure)
sp_MSdrop_rlcore (Procedure)
sp_MSmakebatchinsertproc (Procedure)
sp_MSmakebatchupdateproc (Procedure)
sp_MSmakeconflictinsertproc (Procedure)
sp_MSmakeinsertproc (Procedure)
sp_MSmakeupdateproc (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