BUG: DTS UI Incorrectly Maps Tinyint to Decimal When You Transfer Data to DB2 Using HIS OLEDB Provider for DB2 (300187)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)

This article was previously published under Q300187

BUG #: 354255 (SHILOH_BUGS)

SYMPTOMS

The Data Transformation Services (DTS) Import/Export UI incorrectly maps the tinyint data type on the source to decimal(3,0) on the destination DB2 server if you use the Host Integration Server (HIS) OLEDB provider for DB2.

CAUSE

When you export or replicate data to heterogeneous datasources, data type mapping between the source and destination fields is performed. If an exact static match is not found, the provider dynamically queries the destination to obtain a mapping.

If the SQL Server source table contains a tinyint field, the tinyint field should be mapped to the smallint data type with a precision 3 in the DB2 destination table. An improper data type is being chosen as a result of a failure to find an exact static match for the data type and precision.

A list of the specified mappings for data types in SQL Server to DB2 is found in the msdb..Msdatatype_mappings table.

WORKAROUND

Edit the table schema so that the destination schema reflects the smallint data type instead of a decimal.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 2000.

MORE INFORMATION

The OLEDB provider may also report the following error message if the values for the package collection or default schema are not correct in the Microsoft Data Link file (UDL):

Error Source: Microsoft DB2 OleDB provider
Error Description:One or more errors has occurred during the processing of command.

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated native error and SQL State.SQLSTATE:42501,SQLCODE;-551

The DTS package fails to execute and similar error messages are reported in the failure. To resolve the error, make sure that the package collection and schema values are appropriate.

Modification Type:	Major	Last Reviewed:	10/16/2003
Keywords:	kbBug kbpending KB300187