Microsoft KB Archive/252439

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Knowledge Base


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