ado.net and sql 2005

[Guest]

We have been testing a migration of our application from 2000 to 2005. All
seemed well until last week.

Our framework generates a set of business objects and maps them through a
DAL to a specific table/column. In Sql Server (we also support Oracle) our
columns definined by the developer as currency (internal type name) map to a
..Net 1.1 decimal type and a sql server table column of money. From my
understanding these columns have a default scale of 4.

In Sql 2000 (and oracle) when we perform a data adapter fill (sp_execute),
the decimal type is truncated down to 4 decimal places. Thus 34.3456432 gets
truncated down to 34.3456. This is fine with us because the excess precision
is a result of cost calculations and we have no interest in keeping the info.
The money type is stored correctly and our clients are happy.

YET... now in 2005 we get an exception passing the value. It complains
about exceeding the precision but I was under the assumption for a money type
that the precision was thirty something...

-------------------------------------------------------
Unable to apply changes to the DataSet (SqlException):The incoming tabular
data stream (TDS) remote procedure call (RPC) protocol stream is incorrect.
Parameter 5 ("@AREAAVGCST"): The supplied value is not a valid instance of
data type numeric. Check the source data for invalid values. An example of an
invalid value is data of numeric type with scale greater than precision

Here is the value: 5.9232811750188790881984289485

---------------------

What is going on? We have a client going live on 2005 and this makes no
sense. Are we supposed to check every decimal to see if it exceeds the
column precision?

Is there a database setting or something to turn on to fix this?

Thank you,
Russell

 

[Mary Chipman [MSFT]]

It's a difficult problem to diagnose without understanding what the
code is doing. See if this topic helps: Working with SqlTypes
http://msdn2.microsoft.com/en-us/library/ms172136.aspx.

--Mary

 

[Albert Pascual]

We have been testing a migration of our application from 2000 to 2005.
All
seemed well until last week.

Our framework generates a set of business objects and maps them through a
DAL to a specific table/column. In Sql Server (we also support Oracle)
our
columns definined by the developer as currency (internal type name) map to
a
.Net 1.1 decimal type and a sql server table column of money. From my
understanding these columns have a default scale of 4.

In Sql 2000 (and oracle) when we perform a data adapter fill (sp_execute),
the decimal type is truncated down to 4 decimal places. Thus 34.3456432
gets
truncated down to 34.3456. This is fine with us because the excess
precision
is a result of cost calculations and we have no interest in keeping the
info.
The money type is stored correctly and our clients are happy.

YET... now in 2005 we get an exception passing the value. It complains
about exceeding the precision but I was under the assumption for a money
type
that the precision was thirty something...

-------------------------------------------------------
Unable to apply changes to the DataSet (SqlException):The incoming tabular
data stream (TDS) remote procedure call (RPC) protocol stream is
incorrect.
Parameter 5 ("@AREAAVGCST"): The supplied value is not a valid instance of
data type numeric. Check the source data for invalid values. An example of
an
invalid value is data of numeric type with scale greater than precision

Here is the value: 5.9232811750188790881984289485

---------------------

What is going on? We have a client going live on 2005 and this makes no
sense. Are we supposed to check every decimal to see if it exceeds the
column precision?

Is there a database setting or something to turn on to fix this?

Thank you,
Russell

 

[Guest]

Hi Russell,

We are seeing similar problems in our 2005 migration.

Did you manage to find an explanation or solution to this problem in the end?

Cheers

 

[lliegeois]

We have the same issue...

And in the doc we found that the decimal type changed from 28 to 38 total precision...

We keep searching... if you have the solution don't hesitate ! we will do the same if we find

 

[Guest]

We have found that if we explicitly set the precision and scale properties of
the MONEY sqlparameters in the C# code, then the code functions as normal.

However, this is just a workaround and we still don't know what has changed
in SQL 2005 that is causing this error.

Any ideas?