|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ado.net and sql 2005seemed 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 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 <RussellGainf***@discussions.microsoft.com> wrote: Show quote >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
Show quote
"Russell Gainford" <RussellGainf***@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 > 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 Show quote "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 > 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 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? Show quote "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 >  |
|||||||||||||||||||||||
Other interesting topics