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:
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