Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
griffcj
Starting Member
9 Posts |
Posted - 2004-05-27 : 15:56:30
|
Below is the code I have have created for a stored procedure. I am passing values into the procedure through Cold Fusion in my web application, but for creation purposes, I put values directly into the variables. When I run the proc, I recieve the error "Error converting data type numeric to numeric". The table has the same properties as the parameters in the proc.Does anyone see anything wrong with the code listed below or have an idea as to where to look for the problem. Any help would be greatly appreciated.I am using SQL2K on W2K3.Thanks.---------------------------------------------------------------ALTER PROCEDURE sp_orderOnSiteCompletion @AMERICLIC_ID VARCHAR(20) = 'EIGPA0000005', @PAYOR_ID NUMERIC(9) = 100024, @REFERRER_ID NUMERIC(9) = 499, @CLAIM_NUMBER VARCHAR(50) = NULL, @ORDER_STATUS VARCHAR(20) = 'Open', @ITEM_DESCRIPTION VARCHAR(75) = 'Interpretation', @MEETING_REFERENCE VARCHAR(30) = NULL, @MEETING_CONTACT VARCHAR(30) = 'John Smith', @MEETING_PHONE NUMERIC(9) = 2155551212, @MEETING_ADJUSTER VARCHAR(50) = NULL, @MEETING_NONENGLISH VARCHAR(50) = NULL, @MEETING_DATE DATETIME = 'May 1 2004', @MEETING_TIME DATETIME = '10:00 AM', @MEETING_LANGUAGE VARCHAR(50) = 'Spanish', @MEETING_DURATION NUMERIC(9) = 2, @MEETING_ADDR1 VARCHAR(50) = '123 Test Drive', @MEETING_ADDR2 VARCHAR(50) = NULL, @MEETING_CITY VARCHAR(50) = 'Philadelphia', @MEETING_STATE VARCHAR(2) = 'PA', @MEETING_ZIP NUMERIC(9) = 19087, @MEETING_NATURE VARCHAR(30) = NULL, @MEETING_PARTIES VARCHAR(100) = NULL, @MEETING_COMMENTS VARCHAR(500) = NULL, @CREATED_BY VARCHAR(30) = 'Cory Griffiths', @CREATED_DATE DATETIME = 'May 27 2004'ASBEGINDECLARE @nextID NUMERIC(9)SELECT @nextID = (SELECT IsNull(Max(sales_order_id + 1), '0') AS maxOrderNum FROM tbl_sales_order)INSERT INTO tbl_sales_order( SALES_ORDER_ID, AMERICLIC_ID, PAYOR_ID, REFERRER_ID, CLAIM_NUMBER, ORDER_STATUS, ITEM_DESCRIPTION, MEETING_REFERENCE, MEETING_CONTACT, MEETING_PHONE, MEETING_ADJUSTER, MEETING_NONENGLISH, MEETING_DATE, MEETING_TIME, MEETING_LANGUAGE, MEETING_DURATION, MEETING_ADDR1, MEETING_ADDR2, MEETING_CITY, MEETING_STATE, MEETING_ZIP, MEETING_NATURE, MEETING_PARTIES, MEETING_COMMENTS, CREATED_BY, CREATED_DATE)VALUES( @nextID, @AMERICLIC_ID, @PAYOR_ID, @REFERRER_ID, @CLAIM_NUMBER, @ORDER_STATUS, @ITEM_DESCRIPTION, @MEETING_REFERENCE, @MEETING_CONTACT, @MEETING_PHONE, @MEETING_ADJUSTER, @MEETING_NONENGLISH, @MEETING_DATE, @MEETING_TIME, @MEETING_LANGUAGE, @MEETING_DURATION, @MEETING_ADDR1, @MEETING_ADDR2, @MEETING_CITY, @MEETING_STATE, @MEETING_ZIP, @MEETING_NATURE, @MEETING_PARTIES, @MEETING_COMMENTS, @CREATED_BY, @CREATED_DATE)END |
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-05-27 : 19:27:39
|
That error message looks like it's coming from your CF database drivers. You may need to check that the CF data types are being parsed correctly by the db driver. This is exactly the type of error you get from ADO in VB when you try and send the wrong type of value.Have you tested the procedure in Query Analyser / ISQL? This will normally alert you of any bugs within the SP itself. Once this is done, you can try calling from your client app.HTH,Tim |
|
|
griffcj
Starting Member
9 Posts |
Posted - 2004-05-28 : 10:43:43
|
That is the error I am getting when I run it in the Analyzer. |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2004-05-28 : 10:51:07
|
Does it give you a line number that it's dying on?I'm wondering about these lines:DECLARE @nextID NUMERIC(9)SELECT @nextID = (SELECT IsNull(Max(sales_order_id + 1), '0') AS maxOrderNum FROM tbl_sales_order)I'd also like to know what value that max(sales_order_id) statement returns and what data type that column is.Mike"oh, that monkey is going to pay" |
|
|
griffcj
Starting Member
9 Posts |
Posted - 2004-05-28 : 10:56:39
|
The "sales_order_id" field is a numeric field in the DB. If I run just the MaxID query it returns 100005 to me. This morning, I tried using the CAST function, and when I run the proc, this is the message I'm getting now:Server: Msg 8115, Level 16, State 8, Procedure sp_orderOnSiteCompletion, Line 0Arithmetic overflow error converting numeric to data type numeric.----------------DECLARE @nextID NUMERIC(9)SELECT @nextID = (SELECT CAST(IsNull(Max(sales_order_id + 1), '0') AS NUMERIC(9)) AS maxOrderNum FROM tbl_sales_order) |
|
|
mtomeo
Starting Member
30 Posts |
Posted - 2004-05-28 : 10:56:44
|
Silly thought, but could it be:@MEETING_PHONE NUMERIC(9) = 2155551212I count 10 numbers there...just reaching, maybe it just drops one of the numbers and moves on its merry way. I would make sure your table and SP have the same datatypes and sizes. Also, is there a reason you use NUMERIC instead of INT? Storage? |
|
|
griffcj
Starting Member
9 Posts |
Posted - 2004-05-28 : 11:18:37
|
When I take the values that I have as the defaults, the insert statement works perfectly. It's only when I try and do it through the proc.And no, there really is no reason for numeric compared to int for the phone field. A couple projects back I used numeric and have just continued using it. |
|
|
renante
Starting Member
1 Post |
Posted - 2004-06-20 : 22:51:33
|
This problem occurs only if you are in the network environment and you enabled Internet Connection Firewall of your default connection.Solution:Disable the Internet Connection Firewall.Hope this will help. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-20 : 23:34:59
|
If you get the error when you run it in query analyser can you post the table create statement so we can run it and see what happens.Or you can do it yourself.Create a temp table from your table definition - all fields nullable and drop fields from the insert statement until you find the offending column. Try removing the nextid calculation too in case that's causing a problem (I take it sales_order_id is numeric(9)).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Mr.Xyz
Starting Member
5 Posts |
Posted - 2004-06-21 : 09:25:54
|
this @MEETING_PHONE more than 9 numbers i think this is giving error. i tried it myself creating a table same error...i corrected it., but i am getting String or binary data would be truncated.The statement has been terminated. |
|
|
radrian
Starting Member
1 Post |
Posted - 2006-02-15 : 17:53:24
|
Well after crushing my forehead on the screen with this myself, I decided to spend some time on it. Here are my findings and how I fixed the problem. I found it unlikely that there really was an arithematic overflow given my stuff worked previously. In my case, I was dealing with a table and insert statement that worked fine, then I changed some stuff and it quit. The "stuff" I changed was from a varchar data type to a numeric type. I changed the table by first deleting the column, saving the table then inserting a new column with the same name and giving it the new data type. Then I saved again. I specified the column like so "MyNewColumn" numeric(18,18) nullable. Sure enough I got an error when trying to insert "1.234567887654321", which makes no sense considering there are only 16 digits there. If however, I made the column definition "MyNewColumn" numeric(18,17) nullable it worked! The definition of numeric is the first number defines the number of digits in TOTAL, the second is how many of them are to the left of the decimal. Thus when inserting the number 123xxxxxx.123123 I may be ok with the total digits, but they are not withing the precision(scale) that I specified. Anyway, I adjusted my settings to match my precision and was off and running. It would be great it Microsoft were to put a more descriptive message to this error so that we would know our precision is messed up or something more direct. Anyway, these were my findings anyway. Hope this helps someone.RobAlways happy to help :-) |
|
|
sChar
Starting Member
1 Post |
Posted - 2007-11-14 : 10:25:21
|
This thread helped me solve the same problem.Here is the short cut if some one reads this : Check to see any numeric fields in the table. if there is a numeric (5,2)(Total 5 digits and in which two are after the decimal) that means the max number it can take is 999.00 (or 999 but not more than that.- not 999.111 not 1000.00). check the data you are sending in.hope this helps.And yes. Thanks to the original contributers of this thread. That helped a lot. |
|
|
Arun Prasad
Starting Member
1 Post |
Posted - 2009-02-23 : 05:10:52
|
Hi thanks a lot!!! The last piece of information helped me stop a nasty bug!!! |
|
|
BigJimSlade
Starting Member
1 Post |
Posted - 2009-11-12 : 20:22:21
|
Ditto thanks to SChar |
|
|
|
|
|
|
|