Decimal delimiter in TSQL

Hello again,

another problem occurs when importing some data into a table. Steps I do:

1. Import CSV file into dataset (VB.NET)
2. Use dataset.getxml method to retrieve XML string.
3. Call stored procedure that selects the fields of XML parameter

The parameter is passed as XML, the content of the XML parameter is attached and the stored proc can be viewed in the code section. The problem is that the numbers are wrong formatted, TSQL sees the point as decimal delimiter. E.g. the value 2.946 is imported as 2,95. This shouldn't happen (image of regional settings attached). Of course I could replace the point, but the productive system is an english version, so that's no option for me.
Btw. all software products I use in development are german versions.

rs.GIF
getxml.txt
LVL 9
Stephan_SchrandtAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Hallo Stephan,

There is no choice. MSSQL is considering a point always as a decimal delimiter. You need to remove thousands delimiters, and use a decimal point, as soon as you are handling numeric values as strings. That is, your XML stream has to be cleaned up.

Grüße aus Südbaden!
0
 
tigin44Commented:
English settings treat . as decimal separator so the result is normal. you should manage the point in your code somewhere  before importing to the sql server.
0
 
Stephan_SchrandtAuthor Commented:
Yes, but I have a german version of SQL Server and german regional settings (development environment).  Does SQL Server always treat point as decimal seperator ignoring regional settings?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No matter which locale (regional setting) you use, and whether it is a German or US English version of MSSQL, they behave the same. Only difference is for default codepage used for collations.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.