GetSchemaTable / Money fields in SQL Server

[Scott]

I'm using the GetSchemaTable method of SqlDataReader to determine the
precision / scale of decimal columns returned from a SQL Server stored
procedure. If a column coming back is defined as decimal, NumericPrecision
and NumericScale work as expected. However, if the column is defined as
money, NumericPrecision returns a value of 19, and NumericScale returns 255.
I expected NumericScale to be 4 for money values (which would match the
number of decimal places that SQL Server uses for that data type).

Can anyone explain why NumericScale is 255 for money types instead of 4? Is
this a bug or a feature? Is there anything I can do to make NumericScale
represent the correct number of digits for money values?

Thanks in advance for your help!

Scott

 

[Kevin Yu [MSFT]]

Hi Scott,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you're getting 255 as scale value for
money fields in the schema table. If there is any misunderstanding, please
feel free to let me know.

Based on my research, this is a known issue. Since the scale for money
field in SQL Server is always 4, we don't need to get this info from the
schema table. We can directly use this value. I'm sorry for the
inconvenience.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Scott]

Hi Kevin,

Your understanding of my issue was correct. My concern was with the scale
value for SQL Server money fields. We have some code that spins through a
SQLDataReader and creates an ADODB recordset for use in our older VB6
applications. As part of the recordset creation process, we need to get the
proper precision and scale for decimal and money fields. We can work around
this with some conditional logic, but it just seems strange that scale
returns an incorrect value of 255 when precision returns a correct value of
19.

I haven't had a chance to play with ADO.NET 2.0 yet. I'm hoping it will
correct this issue so that we can eventually take out the "workaround" code
that we're implementing right now. Again, it's not that big of a deal. It
just clutters up our code a bit.

Thank you very much for responding to my issue. I greatly appreciate it.

Scott

 

[Kevin Yu [MSFT]]

You're welcome, Scott.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."