I'm getting this error:
System.Data.SqlClient.SqlException: A floating point exception occurred in the user process. Current transaction is canceled. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at ...etc etc
when updating one particular table, but not when updating another using nearly the same code.
I've searched and searched on the inet, but with no joy.
Microsoft have several different manifestations of it, under variousdifferent circumstances, but the solutions all seem to involveinstalling SP4, which by the looks of it is a massive procedure thatyou have to go to university to be competent to do.
In any case, according to Enterprise Manager, I already have SP4included. On the other hand, the one file of mine I checked the date ofagainst MS's hotfix file list was 2002 not 2003, so who knows?
My Service Manager is v 8.00.760, and it's the Development Edition (ie it's all running on the one machine).
According to the info on sqlDataAdaptor.update, thesqlError that gets returned as part of the sqlException containsfields Class, which gives the seriousness of the error, and State,which identifies the exact error. I get Class=16 (user-fixable)and State=1, which we have to go to SQL Books Online to discover themeaning of. SQL BO probably sounded like a good idea to W.Gates,but it took me half an hour to get anywhere near finding out what that'1' meant. When I did, it was (guess): 'A floating pointexception occurred in the user process. Current transaction iscanceled.' !!!
Can anyone please tell me what exactly might be causing the error?
Graham Rounce
What is your create table statement like is float one of your data types? Another thing most T-SQL quantitative functions are in float but we usually don't use float as data type for rounding problems. Post your stored proc and create table statement and I may be able to help you.|||That would be very terrific. Actually, the update command was made by Visual Studio and looks like this:
'SqlUpdateCommand2
'
Me.SqlUpdateCommand2.CommandText= "UPDATE prices SET price_type = @.price_type, price_code_number =@.price_code_numbe" & _
"r, price_code = @.price_code, price_description = @.price_description, price_rrp =" & _
" @.price_rrp, price_icibuysatxpercentdiscount = @.price_icibuysatxpercentdiscount," & _
" price_icibuysatx = @.price_icibuysatx, price_markupper = @.price_markupper, price" & _
"_absmargin = @.price_absmargin, price_up_icibuysatxpercentdiscount = @.price_up_rh" & _
"lbuysatxpercentdiscount, price_up_sellatxdiscounttofullprice = @.price_up_sellatx" & _
"discounttofullprice, priceoff_rrp = @.priceoff_rrp, priceoff_percofnewbussellingp" & _
"rice = @.priceoff_percofnewbussellingprice, priceoff_icibuysatxpercentdiscount = " & _
"@.priceoff_icibuysatxpercentdiscount, priceoff_icibuysatx = @.priceoff_icibuysatx," & _
" priceoff_markupper = @.priceoff_markupper, priceoff_absmargin = @.priceoff_absmar" & _
"gin, pricehot_rrp = @.pricehot_rrp, pricehot_percofnewbussellingprice = @.pricehot" & _
"_percofnewbussellingprice, pricehot_icibuysatxpercentdiscount = @.pricehot_icibuy" & _
"satxpercentdiscount, pricehot_icibuysatx = @.pricehot_icibuysatx, pricehot_markup" & _
"per = @.pricehot_markupper, pricehot_absmargin = @.pricehot_absmargin, price828_rr" & _
"p = @.price828_rrp, price828_percofnewbussellingprice = @.price828_percofnewbussel" & _
"lingprice, price828_icibuysatxpercentdiscount = @.price828_icibuysatxpercentdisco" & _
"unt, price828_icibuysatx = @.price828_icibuysatx, price828_markupper = @.price828_" & _
"markupper, price828_absmargin = @.price828_absmargin, price_installtime = @.price_" & _
"installtime, price_quotetext = @.price_quotetext, price_defaultnote_1 = @.price_de" & _
"faultnote_1, price_defaultnote_2 = @.price_defaultnote_2, priceoff_calcrrp = @.pri" & _
"ceoff_calcrrp, pricehot_calcrrp = @.pricehot_calcrrp, price828_calcrrp = @.price82" & _
"8_calcrrp, priceoff_calccost = @.priceoff_calccost, pricehot_calccost = @.pricehot" & _
"_calccost, price828_calccost = @.price828_calccost, priceoff_calcsellprice = @.pri" & _
"ceoff_calcsellprice, pricehot_calcsellprice = @.pricehot_calcsellprice, price828_" & _
"calcsellprice = @.price828_calcsellprice, priceoff_calcmargin = @.priceoff_calcmar" & _
"gin, pricehot_calcmargin = @.pricehot_calcmargin, price828_calcmargin = @.price828" & _
"_calcmargin, priceoff_calcmarginper = @.priceoff_calcmarginper, pricehot_calcmarg" & _
"inper = @.pricehot_calcmarginper, price828_calcmarginper = @.price828_calcmarginpe" & _
"r, price_calccost = @.price_calccost, price_calcsellprice = @.price_calcsellprice," & _
" price_calcmargin = @.price_calcmargin, price_calcmarginper = @.price_calcmarginpe" & _
"r, price_up_calccost = @.price_up_calccost, price_up_calcsellprice = @.price_up_ca" & _
"lcsellprice, price_up_calcmargin = @.price_up_calcmargin, price_up_calcmarginper " & _
"= @.price_up_calcmarginper, price_hw_mxcode = @.price_hw_mxcode, price_order = @.pr" & _
"ice_order, price_mxmandatory = @.price_mxmandatory, price_variable = @.price_varia" & _
"ble, price_notes = @.price_notes WHERE (price_ID = @.Original_price_ID) AND (price" & _
"828_absmargin = @.Original_price828_absmargin OR @.Original_price828_absmargin IS " & _
"NULL AND price828_absmargin IS NULL) AND (price828_calccost = @.Original_price828" & _
"_calccost OR @.Original_price828_calccost IS NULL AND price828_calccost IS NULL) " & _
"AND (price828_calcmargin = @.Original_price828_calcmargin OR @.Original_price828_c" & _
"alcmargin IS NULL AND price828_calcmargin IS NULL) AND (price828_calcmarginper =" & _
" @.Original_price828_calcmarginper OR @.Original_price828_calcmarginper IS NULL AN" & _
"D price828_calcmarginper IS NULL) AND (price828_calcrrp = @.Original_price828_cal" & _
"crrp OR @.Original_price828_calcrrp IS NULL AND price828_calcrrp IS NULL) AND (pr" & _
"ice828_calcsellprice = @.Original_price828_calcsellprice OR @.Original_price828_ca" & _
"lcsellprice IS NULL AND price828_calcsellprice IS NULL) AND (price828_markupper " & _
"= @.Original_price828_markupper OR @.Original_price828_markupper IS NULL AND price" & _
"828_markupper IS NULL) AND (price828_percofnewbussellingprice = @.Original_price8" & _
"28_percofnewbussellingprice OR @.Original_price828_percofnewbussellingprice IS NU" & _
"LL AND price828_percofnewbussellingprice IS NULL) AND (price828_icibuysatx = @.Or" & _
"iginal_price828_icibuysatx OR @.Original_price828_icibuysatx IS NULL AND price828" & _
"_icibuysatx IS NULL) AND (price828_icibuysatxpercentdiscount = @.Original_price82" & _
"8_icibuysatxpercentdiscount OR @.Original_price828_icibuysatxpercentdiscount IS N" & _
"ULL AND price828_icibuysatxpercentdiscount IS NULL) AND (price828_rrp = @.Origina" & _
"l_price828_rrp OR @.Original_price828_rrp IS NULL AND price828_rrp IS NULL) AND (" & _
"price_absmargin = @.Original_price_absmargin OR @.Original_price_absmargin IS NULL" & _
" AND price_absmargin IS NULL) AND (price_calccost = @.Original_price_calccost OR " & _
"@.Original_price_calccost IS NULL AND price_calccost IS NULL) AND (price_calcmarg" & _
"in = @.Original_price_calcmargin OR @.Original_price_calcmargin IS NULL AND price_" & _
"calcmargin IS NULL) AND (price_calcmarginper = @.Original_price_calcmarginper OR " & _
"@.Original_price_calcmarginper IS NULL AND price_calcmarginper IS NULL) AND (pric" & _
"e_calcsellprice = @.Original_price_calcsellprice OR @.Original_price_calcsellprice" & _
" IS NULL AND price_calcsellprice IS NULL) AND (price_code = @.Original_price_code" & _
" OR @.Original_price_code IS NULL AND price_code IS NULL) AND (price_code_number " & _
"= @.Original_price_code_number OR @.Original_price_code_number IS NULL AND price_c" & _
"ode_number IS NULL) AND (price_defaultnote_1 = @.Original_price_defaultnote_1 OR " & _
"@.Original_price_defaultnote_1 IS NULL AND price_defaultnote_1 IS NULL) AND (pric" & _
"e_defaultnote_2 = @.Original_price_defaultnote_2 OR @.Original_price_defaultnote_2" & _
" IS NULL AND price_defaultnote_2 IS NULL) AND (price_description = @.Original_pri" & _
"ce_description OR @.Original_price_description IS NULL AND price_description IS N" & _
"ULL) AND (price_hw_mxcode = @.Original_price_hw_mxcode OR @.Original_price_hw_mxco" & _
"de IS NULL AND price_hw_mxcode IS NULL) AND (price_installtime = @.Original_price" & _
"_installtime OR @.Original_price_installtime IS NULL AND price_installtime IS NUL" & _
"L) AND (price_markupper = @.Original_price_markupper OR @.Original_price_markupper" & _
" IS NULL AND price_markupper IS NULL) AND (price_mxmandatory = @.Original_price_m" & _
"xmandatory) AND (price_notes = @.Original_price_notes OR @.Original_price_notes IS" & _
" NULL AND price_notes IS NULL) AND (price_order = @.Original_price_order OR @.Orig" & _
"inal_price_order IS NULL AND price_order IS NULL) AND (price_icibuysatx = @.Origi" & _
"nal_price_icibuysatx OR @.Original_price_icibuysatx IS NULL AND price_icibuysatx " & _
"IS NULL) AND (price_icibuysatxpercentdiscount = @.Original_price_icibuysatxpercen" & _
"tdiscount OR @.Original_price_icibuysatxpercentdiscount IS NULL AND price_icibuys" & _
"atxpercentdiscount IS NULL) AND (price_rrp = @.Original_price_rrp OR @.Original_pr" & _
"ice_rrp IS NULL AND price_rrp IS NULL) AND (price_type = @.Original_price_type OR" & _
" @.Original_price_type IS NULL AND price_type IS NULL) AND (price_up_calccost = @." & _
"Original_price_up_calccost OR @.Original_price_up_calccost IS NULL AND price_up_c" & _
"alccost IS NULL) AND (price_up_calcmargin = @.Original_price_up_calcmargin OR @.Or" & _
"iginal_price_up_calcmargin IS NULL AND price_up_calcmargin IS NULL) AND (price_u" & _
"p_calcmarginper = @.Original_price_up_calcmarginper OR @.Original_price_up_calcmar" & _
"ginper IS NULL AND price_up_calcmarginper IS NULL) AND (price_up_calcsellprice =" & _
" @.Original_price_up_calcsellprice OR @.Original_price_up_calcsellprice IS NULL AN" & _
"D price_up_calcsellprice IS NULL) AND (price_up_icibuysatxpercentdiscount = @.Ori" & _
"ginal_price_up_icibuysatxpercentdiscount OR @.Original_price_up_icibuysatxpercent" & _
"discount IS NULL AND price_up_icibuysatxpercentdiscount IS NULL) AND (price_up_s" & _
"ellatxdiscounttofullprice = @.Original_price_up_sellatxdiscounttofullprice OR @.Or" & _
"iginal_price_up_sellatxdiscounttofullprice IS NULL AND price_up_sellatxdiscountt" & _
"ofullprice IS NULL) AND (price_variable = @.Original_price_variable) AND (priceho" & _
"t_absmargin = @.Original_pricehot_absmargin OR @.Original_pricehot_absmargin IS NU" & _
"LL AND pricehot_absmargin IS NULL) AND (pricehot_calccost = @.Original_pricehot_c" & _
"alccost OR @.Original_pricehot_calccost IS NULL AND pricehot_calccost IS NULL) AN" & _
"D (pricehot_calcmargin = @.Original_pricehot_calcmargin OR @.Original_pricehot_cal" & _
"cmargin IS NULL AND pricehot_calcmargin IS NULL) AND (pricehot_calcmarginper = @." & _
"Original_pricehot_calcmarginper OR @.Original_pricehot_calcmarginper IS NULL AND " & _
"pricehot_calcmarginper IS NULL) AND (pricehot_calcrrp = @.Original_pricehot_calcr" & _
"rp OR @.Original_pricehot_calcrrp IS NULL AND pricehot_calcrrp IS NULL) AND (pric" & _
"ehot_calcsellprice = @.Original_pricehot_calcsellprice OR @.Original_pricehot_calc" & _
"sellprice IS NULL AND pricehot_calcsellprice IS NULL) AND (pricehot_markupper = " & _
"@.Original_pricehot_markupper OR @.Original_pricehot_markupper IS NULL AND priceho" & _
"t_markupper IS NULL) AND (pricehot_percofnewbussellingprice = @.Original_pricehot" & _
"_percofnewbussellingprice OR @.Original_pricehot_percofnewbussellingprice IS NULL" & _
" AND pricehot_percofnewbussellingprice IS NULL) AND (pricehot_icibuysatx = @.Orig" & _
"inal_pricehot_icibuysatx OR @.Original_pricehot_icibuysatx IS NULL AND pricehot_r" & _
"hlbuysatx IS NULL) AND (pricehot_icibuysatxpercentdiscount = @.Original_pricehot_" & _
"icibuysatxpercentdiscount OR @.Original_pricehot_icibuysatxpercentdiscount IS NUL" & _
"L AND pricehot_icibuysatxpercentdiscount IS NULL) AND (pricehot_rrp = @.Original_" & _
"pricehot_rrp OR @.Original_pricehot_rrp IS NULL AND pricehot_rrp IS NULL) AND (pr" & _
"iceoff_absmargin = @.Original_priceoff_absmargin OR @.Original_priceoff_absmargin " & _
"IS NULL AND priceoff_absmargin IS NULL) AND (priceoff_calccost = @.Original_price" & _
"off_calccost OR @.Original_priceoff_calccost IS NULL AND priceoff_calccost IS NUL" & _
"L) AND (priceoff_calcmargin = @.Original_priceoff_calcmargin OR @.Original_priceof" & _
"f_calcmargin IS NULL AND priceoff_calcmargin IS NULL) AND (priceoff_calcmarginpe" & _
"r = @.Original_priceoff_calcmarginper OR @.Original_priceoff_calcmarginper IS NULL" & _
" AND priceoff_calcmarginper IS NULL) AND (priceoff_calcrrp = @.Original_priceoff_" & _
"calcrrp OR @.Original_priceoff_calcrrp IS NULL AND priceoff_calcrrp IS NULL) AND " & _
"(priceoff_calcsellprice = @.Original_priceoff_calcsellprice OR @.Original_priceoff" & _
"_calcsellprice IS NULL AND priceoff_calcsellprice IS NULL) AND (priceoff_markupp" & _
"er = @.Original_priceoff_markupper OR @.Original_priceoff_markupper IS NULL AND pr" & _
"iceoff_markupper IS NULL) AND (priceoff_percofnewbussellingprice = @.Original_pri" & _
"ceoff_percofnewbussellingprice OR @.Original_priceoff_percofnewbussellingprice IS" & _
" NULL AND priceoff_percofnewbussellingprice IS NULL) AND (priceoff_icibuysatx = " & _
"@.Original_priceoff_icibuysatx OR @.Original_priceoff_icibuysatx IS NULL AND price" & _
"off_icibuysatx IS NULL) AND (priceoff_icibuysatxpercentdiscount = @.Original_pric" & _
"eoff_icibuysatxpercentdiscount OR @.Original_priceoff_icibuysatxpercentdiscount I" & _
"S NULL AND priceoff_icibuysatxpercentdiscount IS NULL) AND (priceoff_rrp = @.Orig" & _
"inal_priceoff_rrp OR @.Original_priceoff_rrp IS NULL AND priceoff_rrp IS NULL); S" & _
"ELECT price_ID, price_type, price_code_number, price_code, price_description, pr" & _
"ice_rrp, price_icibuysatxpercentdiscount, price_icibuysatx, price_markupper, pri" & _
"ce_absmargin, price_up_icibuysatxpercentdiscount, price_up_sellatxdiscounttofull" & _
"price, priceoff_rrp, priceoff_percofnewbussellingprice, priceoff_icibuysatxperce" & _
"ntdiscount, priceoff_icibuysatx, priceoff_markupper, priceoff_absmargin, priceho" & _
"t_rrp, pricehot_percofnewbussellingprice, pricehot_icibuysatxpercentdiscount, pr" & _
"icehot_icibuysatx, pricehot_markupper, pricehot_absmargin, price828_rrp, price82" & _
"8_percofnewbussellingprice, price828_icibuysatxpercentdiscount, price828_icibuys" & _
"atx, price828_markupper, price828_absmargin, price_installtime, price_quotetext," & _
" price_defaultnote_1, price_defaultnote_2, priceoff_calcrrp, pricehot_calcrrp, p" & _
"rice828_calcrrp, priceoff_calccost, pricehot_calccost, price828_calccost, priceo" & _
"ff_calcsellprice, pricehot_calcsellprice, price828_calcsellprice, priceoff_calcm" & _
"argin, pricehot_calcmargin, price828_calcmargin, priceoff_calcmarginper, priceho" & _
"t_calcmarginper, price828_calcmarginper, price_calccost, price_calcsellprice, pr" & _
"ice_calcmargin, price_calcmarginper, price_up_calccost, price_up_calcsellprice, " & _
"price_up_calcmargin, price_up_calcmarginper, price_hw_mxcode, price_order, price" & _
"_mxmandatory, price_variable, price_notes FROM prices WHERE (price_ID = @.price_I" & _
"D) ORDER BY price_description"
Pretty lengthy?*!$!
The Create statement I don't have, as the database was converted fromAccess. Other tables from it work ok, though, after they had theprimary key put back in. The data types are a mixture ofmoney & real - some of those were empty, and I did tryputting 0's in them prior to the update in case it was that, but Ithought it shouldn't really be necessary and it had no effect. Also, there are some ints and nvarchars and bits, and one each ofvarchar and ntext.
Is that enough? It would be, as I said, extremely terrific if you could help.
Thanks,
Graham Rounce
|||
There is little difference between Float and Real in SQL Server because the synonym for Real is Float. Try to change that to Decimal or Numeric and I have problem with your ISNULL because it replaces NULL values with 0. Try the links below to see how to deal with NULL values and see if you can use a CASE statement. One more thing COUNT (*) is the only aggregate function in SQL Server that calculates NULL values but you don't have to use ISNULL all th time. Post again if the problem continues and the SQL Server BOL (books online) is the best in the business but you will do better with Boolean searches. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm
http://www.akadia.com/services/dealing_with_null_values.html
|||
Hiagain, and thanks for the reply. Having slept on it, I thought I'd tryreducing the size of the query by not including variouscolumns when configuring the DataAdaptor in Visual Studio. Iun-included half of them, and it worked fine. Then Iprogressively re-included blocks of columns until it stopped workingagain. Below is the column list. The ones marked '?'are the ones which, when included, cause the error to happen (and when re-un-included stop it happening again!).
They don't look very different to other columns in the table, and I'mthinking it was just the sheer size of the generated query thatdefeated the server.
Unless you disagree, this looks like a bug? Of course, onewouldn't expect to be able to use infinite-sized tables, but I'd expect a relevant warning, not an inexplicable error?
Thanks,
Graham Rounce
int 4 included
nvarchar 50 included
int 4 included
nvarchar 50 included
nvarchar 255 included
money 8 included
real 4 included
money 8 included
real 4 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
money 8 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
money 8 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
money 8 included
real 4 included
ntext 16 included
nvarchar 50 included
nvarchar 50 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
real 4 ?
real 4 ?
real 4 ?
money 8 ?
money 8 ?
money 8 ?
real 4 not included
money 8 not included
money 8 not included
money 8 not included
real 4 not included
nvarchar 20 included
int 4 included
bit 1 included
bit 1 included
varchar 8000 included
|||It is not the size of your query but change all the Real and Money to Decimal or Numeric. The numeric data in the Real column is over flowing, and I know there are existing issues with money rounding to less than a dollar going back to 1999. In Decimal and Numeric remember to set the precission and scale. Post again and be patient. Hope this helps.|||Hi again, and thanks. Just one more q if you don'tmind. Can I just change those types in 'Design Table' in theEnterprise Manager? I mean, it looks like I can, but will thecontents still be ok? I'm using the table at the momentwithout about a third of the columns as they didn't really need to bethere anyway, but it seems like it would be good practice to sort itout.
Can I just go in and change the types? It can't really be as easy as that? (Smiley thing)
Graham Rounce
|||I don't mind at all just open the BOL (books online) search pane and enter ALTER table, the first answer covered all you need. Hope this helps.|||It looks like Alter Table does everything except make the tea! (Andthat could be in there somewhere, too). I'm quite happy to changethe Types in Enterprise Manager, as it's only a one-offconversion. Is that likely to cause any problems, as long as Ichange the prog to suit?
Thanks again,
|||I don't think it will cause any problem and I am glad to see you are close to a solution. Hope this helps.|||Yes indeed. Thanks very much.
