June 4, 2008 at 5:57 am
After installing SP3 on my Win XP PRO pc the regional settings seem to be ignored by the transform data task in a DTS
The story thus far.
Before SP3 the string " 259,97" from a txt-file was loaded as 259 units and 97 cents which is correct as Regional settings state a "comma" as decimal symbol and a "period" as Digit Grouping Symbol.
After installation of SP3 the same string is loaded as 25997. When I replace the comma by a period it loads correctly and a conversion of " 1.111.259,97" results in Microsoft VBScript runtime error : Type mismatch: 'cdbl'.
As the regional settings have not changed I presume they were ignored.
When I uninstalled WIN XP SP3 the amounts were loaded correct again, reinstalled it and the (decimal) comma is ignored again.
I'm sure I could easily find workarounds using mid() or replace()(with or without a staging table) but as I have hundreds of Transform Data Tasks which still run correctly on the server I don't see that as a valid option.
the input file looks like this :
STPP$£NOPO$£PRBA$£COMM$£
200803$£M0011127$£ 259,97$£ 44,19$£
where $£ is the separator.
the active-x script looks like this
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("account_yyyymm") = DTSSource("STPP")
DTSDestination("policy_no") = DTSSource("NOPO")
DTSDestination("premium") = cdbl(DTSSource("PRBA"))
DTSDestination("commission") =cdbl( DTSSource("COMM"))
Main = DTSTransformStat_OK
End Function
The premium and commission field in the destination table ar defined as decimal(15,2)
Using Google gave me no explanation (maybe my search-criteria were wrong).
Has anyone got any clue what goes wrong?
Is there somewhere else, apart from the regional settings, where one could specify decimal symbol and Digit Grouping Symbol for the SQL Server Enterprise Manager (version 8.0)?
I'm not sure this is the right forum to post, should I perhaps post the same in the Microsoft Technet Win XP SP3 forum?
Thanks
Marc
June 5, 2008 at 6:51 am
Meanwhile I found the answer on the release notes of WIN XP SP3 (http://download.microsoft.com/download/c/d/8/cd8cc719-7d5a-40d3-a802-e4057aa8c631/relnotes.htm)
Scripting
If you change the regional and language options to a different setting from those used when the service pack was installed, certain region-specific aspects of Visual Basic Scripting Edition (VBScript) (such as the DateValue() function) and Jscript (such as the toLocaleString method) will not function correctly. For example, such scripts will output date, currency, number, and time data in the incorrect format.
For more detailed information, including information about an available hotfix, see http://go.microsoft.com/fwlink/?LinkId=116903.
The proposed workaround means that I would have to change all my VBScripts in all my DTS-packages.
I think I'll wait for the promised hotfix instead
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply