FIX: Replication: Problems Mapping Characters to DB2 OLEDB Subscribers

• Microsoft SQL Server version 7.0

SYMPTOMS

While replicating to subscribers running DB2 using the Microsoft OLEDB provider, certain datatypes are mapped incorrectly and nullability is not preserved. Also, when replicating to DB2 subscribers on MVS systems, the distribution agent fails with this error:

SQLCODE: -601 SQLSTATE: 42710
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.

SQLCODE: -302 SQLSTATE: 22001
The value of the input variable or parameter is invalid or too large for the target column or target value.

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

   File name   Platform
   --------------------

   s70722i.exe  Intel
   s70722a.exe  Alpha 

STATUS

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

MORE INFORMATION

Specific symptoms encountered due to this bug are:

• If the publisher column was defined with NOT NULL, the subscriber table was created to allow NULLs.



• VARCHAR datatypes (variable length) are mapped to CHAR (fixed length) datatypes on the subscriber.



• The script file that contains the CREATE TABLE SQL statement being generated by the replication engine contains TAB characters that the DB2 subscriber on MVS was unable to handle. This results in the error with SQLCODE: -601 and SQLSTATE: 42710



• Problems with binding in the OLEDB provider where if the source type is a CHAR datatype, then the scale of the destination column is supplied incorrectly in bytes instead of characters. The DB2 subscriber on MVS does strict checking on the parameter column length to verify that it is less or equal to the defined column length in the table. This results in the error with SQLCODE: -302 and SQLSTATE: 22001.

Additional query words:

Keywords : kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug