sp_helpdatatypemap (Transact-SQL)

Returns information on the defined data type mappings between Microsoft SQL Server and non-SQL Server database management systems (DBMS). This stored procedure is executed at the Distributor on any database.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_helpdatatypemap [ @source_dbms = ] 'source_dbms' 
    [ , [ @source_version = ] 'source_version' ]
    [ , [ @source_type = ] 'source_type' ] 
    [ , [ @destination_dbms = ] 'destination_dbms' ]
    [ , [ @destination_version = ] 'destination_version' ]
    [ , [ @destination_type = ] 'destination_type' ]
    [ , [ @defaults_only = ] defaults_only ]

Arguments

  • [ @source_dbms= ] 'source_dbms'
    Is the name of the DBMS from which the data types are mapped. source_dbms is sysname, and can be one of the following values.

    Value Description

    MSSQLSERVER

    The source is a SQL Server database.

    ORACLE

    The source is an Oracle database.

  • [ @source_version= ] 'source_version'
    Is the product version of the source DBMS. source_versionis varchar(10), and if not specified, the data type mappings for all versions of the source DBMS are returned. Enables the result set to be filtered by the source version of the DBMS.
  • [ @source_type= ] 'source_type'
    Is the data type listed in the source DBMS. source_type is sysname, and if not specified, mappings for all data types in the source DBMS are returned. Enables the result set to be filtered by data type in the source DBMS.
  • [ @destination_dbms = ] 'destination_dbms'
    Is the name of the destination DBMS. destination_dbms is sysname, and can be one of the following values.

    Value Description

    MSSQLSERVER

    The destination is a SQL Server database.

    ORACLE

    The destination is an Oracle database.

    DB2

    The destination is an IBM DB2 database.

    SYBASE

    The destination is a Sybase database.

  • [ @destination_version= ] 'destination_version'
    Is the product version of the destination DBMS. destination_versionis varchar(10), and if not specified, mappings for all versions of the destination DBMS are returned. Enables the result set to be filtered by the destination version of the DBMS.
  • [ @destination_type= ] 'destination_type'
    Is the data type listed in the destination DBMS. destination_typeis sysname, and if not specified, mappings for all data types in the destination DBMS are returned. Enables the result set to be filtered by data type in the destination DBMS.
  • [ @defaults_only= ] defaults_only
    Is if only the default data type mappings are returned. defaults_only is bit, with a default of 0. 1 means that only the default data type mappings are returned. 0 means that the default and any user-defined data type mappings are returned.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name Description

mapping_id

Identifies a data type mapping.

source_dbms

Is the name and version number of the source DBMS.

source_type

Is the data type in the source DBMS.

destination_dbms

Is the name of the destination DBMS.

destination_type

Is the data type in the destination DBMS.

is_default

Is if the mapping is a default or an alternative mapping. A value of 0 indicates that this mapping is user-defined.

Remarks

sp_helpdatatypemap defines data type mappings both from non-SQL Server Publishers and from SQL Server Publishers to non-SQL Server Subscribers.

When the specified combination of source and destination DBMS is not supported, sp_helpdatatypemap returns an empty result set.

Permissions

Only members of the sysadmin fixed server role at the Distributor or members of the db_owner fixed database role on the distribution database can execute sp_helpdatatypemap.

See Also

Reference

sp_getdefaultdatatypemapping (Transact-SQL)
sp_setdefaultdatatypemapping (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance