Microsoft KB Archive/252439

From BetaArchive Wiki
Knowledge Base

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

Article ID: 252439

Article Last Modified on 10/31/2003


  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition

This article was previously published under Q252439


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


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.


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)

INSERT INTO dtstest VALUES (0.081625, 0.081625)

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