Type Mismatch when Adding Matching Type Decimal Fields
Author 
Message 
CR #1 / 5

Type Mismatch when Adding Matching Type Decimal Fields
Okay, I'm having a really basic problem that I just can't believe could actually a bug. My source table has two decimal columns both defined Precision=7, Scale=2 and my target table has one decimal column also with Precision=7, Scale=2. The following line fails on a "Type Mismatch" error during a simple ActiveX transformation using VBScript: DTSDestination("Total_Amount") = DTSSource("Amount_1") + DTSSource("Amount_2") I'm simply adding two decimal fields  this is not a complex operation here. I even changed the target to Precision=9, Scale=2 to acommodate a larger decimal, but it didn't help. I'm running SQL Server 2000 with SP2. Any ideas? Thanks very much. Chuck

Fri, 31 Dec 2004 20:31:22 GMT 


Steve Janse #2 / 5

Type Mismatch when Adding Matching Type Decimal Fields
Chuck, Do you have error checking for NULL values in the source columns and error checking for overflow conditions?  Steve Jansen Byte Interactive
Quote: > Okay, I'm having a really basic problem that I just can't believe could > actually a bug. My source table has two decimal columns both defined > Precision=7, Scale=2 and my target table has one decimal column also with > Precision=7, Scale=2. The following line fails on a "Type Mismatch" error > during a simple ActiveX transformation using VBScript: > DTSDestination("Total_Amount") = DTSSource("Amount_1") + > DTSSource("Amount_2") > I'm simply adding two decimal fields  this is not a complex operation > here. I even changed the target to Precision=9, Scale=2 to acommodate a > larger decimal, but it didn't help. > I'm running SQL Server 2000 with SP2. Any ideas? Thanks very much. > Chuck

Fri, 31 Dec 2004 23:59:38 GMT 


CR #3 / 5

Type Mismatch when Adding Matching Type Decimal Fields
I don't, but in a previous step I convert all NULLS to zero. Also, incoming fields are defined decimal(7,2) so should not overflow  expecially after I changed target field to decimal(9.2) Since this is a dollar field, I changed it to SMALLMONEY from DECIMAL(7,2) and this solved the problem. SMALLMONEY is defined as DECIMAL(10,4) so I imagine I might have achieved the same results with regular DECIMAL(10,4) though I didn't try it. I don't know why a money field has 4 places to the right of the decimal, but I guess I'll just have to deal with that. Thanks for your reply. Chuck
Quote: > Chuck, > Do you have error checking for NULL values in the source columns and error > checking for overflow conditions? >  > Steve Jansen > Byte Interactive
> > Okay, I'm having a really basic problem that I just can't believe could > > actually a bug. My source table has two decimal columns both defined > > Precision=7, Scale=2 and my target table has one decimal column also with > > Precision=7, Scale=2. The following line fails on a "Type Mismatch" error > > during a simple ActiveX transformation using VBScript: > > DTSDestination("Total_Amount") = DTSSource("Amount_1") + > > DTSSource("Amount_2") > > I'm simply adding two decimal fields  this is not a complex operation > > here. I even changed the target to Precision=9, Scale=2 to acommodate a > > larger decimal, but it didn't help. > > I'm running SQL Server 2000 with SP2. Any ideas? Thanks very much. > > Chuck

Sat, 01 Jan 2005 01:37:03 GMT 


Steve Janse #4 / 5

Type Mismatch when Adding Matching Type Decimal Fields
Chuck, According to BOL, the MONEY type I would guess that the MONEY data type as having " accuracy to a tenthousandth of a monetary unit." Thus, the scale of 4 is required to give accuracy to 0.001 monetary units. Undoubtedly, this accuracy is of high importance to those performing complex financial calculations (e.g., stock prices), and to those displaying/computing financial information in monetary units other than US dollars (e.g, Yen, Pounds, Francs, etc)  remember SQL Server supports cultural locales other than US English. Steve Jansen Byte Interactive
Quote: > I don't, but in a previous step I convert all NULLS to zero. Also, incoming > fields are defined decimal(7,2) so should not overflow  expecially after I > changed target field to decimal(9.2) > Since this is a dollar field, I changed it to SMALLMONEY from DECIMAL(7,2) > and this solved the problem. SMALLMONEY is defined as DECIMAL(10,4) so I > imagine I might have achieved the same results with regular DECIMAL(10,4) > though I didn't try it. I don't know why a money field has 4 places to the > right of the decimal, but I guess I'll just have to deal with that. Thanks > for your reply. > Chuck
> > Chuck, > > Do you have error checking for NULL values in the source columns and error > > checking for overflow conditions? > >  > > Steve Jansen > > Byte Interactive
> > > Okay, I'm having a really basic problem that I just can't believe could > > > actually a bug. My source table has two decimal columns both defined > > > Precision=7, Scale=2 and my target table has one decimal column also > with > > > Precision=7, Scale=2. The following line fails on a "Type Mismatch" > error > > > during a simple ActiveX transformation using VBScript: > > > DTSDestination("Total_Amount") = DTSSource("Amount_1") + > > > DTSSource("Amount_2") > > > I'm simply adding two decimal fields  this is not a complex operation > > > here. I even changed the target to Precision=9, Scale=2 to acommodate a > > > larger decimal, but it didn't help. > > > I'm running SQL Server 2000 with SP2. Any ideas? Thanks very much. > > > Chuck

Sat, 01 Jan 2005 01:58:57 GMT 


CR #5 / 5

Type Mismatch when Adding Matching Type Decimal Fields
Thanks for the info!
Quote: > Chuck, > According to BOL, the MONEY type I would guess that the MONEY data type as > having " accuracy to a tenthousandth of a monetary unit." Thus, the scale > of 4 is required to give accuracy to 0.001 monetary units. > Undoubtedly, this accuracy is of high importance to those performing complex > financial calculations (e.g., stock prices), and to those > displaying/computing financial information in monetary units other than US > dollars (e.g, Yen, Pounds, Francs, etc)  remember SQL Server supports > cultural locales other than US English. > Steve Jansen > Byte Interactive
> > I don't, but in a previous step I convert all NULLS to zero. Also, > incoming > > fields are defined decimal(7,2) so should not overflow  expecially after > I > > changed target field to decimal(9.2) > > Since this is a dollar field, I changed it to SMALLMONEY from DECIMAL(7,2) > > and this solved the problem. SMALLMONEY is defined as DECIMAL(10,4) so I > > imagine I might have achieved the same results with regular DECIMAL(10,4) > > though I didn't try it. I don't know why a money field has 4 places to > the > > right of the decimal, but I guess I'll just have to deal with that. > Thanks > > for your reply. > > Chuck
message
> > > Chuck, > > > Do you have error checking for NULL values in the source columns and > error > > > checking for overflow conditions? > > >  > > > Steve Jansen > > > Byte Interactive
> > > > Okay, I'm having a really basic problem that I just can't believe > could > > > > actually a bug. My source table has two decimal columns both defined > > > > Precision=7, Scale=2 and my target table has one decimal column also > > with > > > > Precision=7, Scale=2. The following line fails on a "Type Mismatch" > > error > > > > during a simple ActiveX transformation using VBScript: > > > > DTSDestination("Total_Amount") = DTSSource("Amount_1") + > > > > DTSSource("Amount_2") > > > > I'm simply adding two decimal fields  this is not a complex > operation > > > > here. I even changed the target to Precision=9, Scale=2 to acommodate > a > > > > larger decimal, but it didn't help. > > > > I'm running SQL Server 2000 with SP2. Any ideas? Thanks very much. > > > > Chuck

Sat, 01 Jan 2005 04:46:37 GMT 


