Microsoft KB Archive/252439

= PRB: Use of Math Operations with DTS and Numeric Data Types =

Article ID: 252439

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q252439



SYMPTOMS
When you try to use ActiveX transformations with Microsoft SQL Server Data Transformation Services (DTS) where the source column is a numeric data type, the following error message can occur when you perform mathematical operations:

Error Code: 0

Error Source: Microsoft VBScript runtime error

Error Description: Type mismatch



CAUSE
Microsoft Visual Basic® Scripting Edition (VBScript) does not support the numeric data type and is unable to handle the mathematical operation of the two operands.



WORKAROUND
To work around this problem, use the Cast function to convert the unsupported data type into a data type that a script written in VBScript can support.

You can use the following T-SQL script and VBScript to demonstrate the behavior: CREATE TABLE dtstest (   Rate1   numeric(27,23),    Rate2   numeric(27,23) ) go

INSERT INTO dtstest VALUES (0.081625, 0.081625) go Create a DTS transformation between dtstest and a new table. In the transformation, select VBScript ActiveX transformation, and the use the following code snippet to perform the transformation: Function Main Dim Rate REM The following statement allows VBScript to perform the REM expression against a numeric data type. REM Rate = CDbl(DTSSource("Rate1")) / CDbl(DTSSource("Rate2"))

REM The following statement causes the error because VBScript REM is not able to handle the numeric data type. Replace the REM following statement with the preceding statement. Rate = DTSSource("Rate1") / DTSSource("Rate2")

DTSDestination("Rate1") = Rate DTSDestination("Rate2") = DTSSource("Rate2") Main = DTSTransformStat_OK End Function

Keywords: kbprb KB252439

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.