Flat File Provider and Decimal/Numeric data

Last week I received the following message from a colleague:

 

From: <Intentionally_Removed>
Sent: Tuesday, November 13, 2007 2:30 PM
To: <Intentionally_Removed>
Subject: Flat File Provider and Decimal/Numeric data

Hi All,

I hope someone of you can help me in explaining the following behavior to customer J. Although I think it is by design, I don’t have a valid explanation for it.

When creating a simple table on SQL Server 2005 (of course SP2 + CU4) and then exporting the data with the Flat File Provider decimal/numeric values are missing in the output file the leading 0 before the decimal point when the specific value is smaller than 1.

e.g. 0.123 in SQL Server is ‘converted’ to .123

Repro:

e.g. create the following table:

create table test (c1 int Primary key, c2 decimal(10,2),c3 float)

go

insert test values (1,0.34,0.123)

insert test values (2,1.34,1.123)

insert test values (3,0.987,0.0123)

Then export with Export functionality of SQL Server Management Studio to file and look at it e.g. with notepad:

1,.34,0.123

2,1.34,1.123

3,.99,0.0123

Anyone who could give me some details here?

Thanks a lot,

<Intentionally_Removed>

 

I looked into the source code of the class that implements this data conversion logic (CDataConvert) and confirmed that we were intentionally removing the leading zeroes. So we can say this behavior is By Design.

 

Until next post!