ado.net and sql 2005

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
Fri May 05 13:58:53 CDT 2006

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

On Wed, 3 May 2006 13:10:02 -0700, Russell Gainford
<RussellGainford@discussions.microsoft.com> wrote:

>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

Albert
Thu May 11 17:55:11 CDT 2006


"Russell Gainford" <RussellGainford@discussions.microsoft.com> wrote in
message news:4D03DCF7-EE9E-4FDD-A4A5-4571414514BF@microsoft.com...
> 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
>

Dune
Tue May 23 17:02:02 CDT 2006

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

"Russell Gainford" wrote:

> 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
>

lliegeois
Wed May 24 09:16:21 CDT 2006

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

Dune
Thu May 25 18:21:01 CDT 2006

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?

"Russell Gainford" wrote:

> 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
>