Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Error converting data type numeric to numeric
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

griffcj
Starting Member

USA
9 Posts

Posted - 05/27/2004 :  15:56:30  Show Profile  Send griffcj a Yahoo! Message  Reply with Quote
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'
AS
BEGIN

DECLARE @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
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 05/27/2004 :  19:27:39  Show Profile  Visit timmy's Homepage  Reply with Quote
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
Go to Top of Page

griffcj
Starting Member

USA
9 Posts

Posted - 05/28/2004 :  10:43:43  Show Profile  Send griffcj a Yahoo! Message  Reply with Quote
That is the error I am getting when I run it in the Analyzer.
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 05/28/2004 :  10:51:07  Show Profile  Visit mfemenel's Homepage  Reply with Quote
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"
Go to Top of Page

griffcj
Starting Member

USA
9 Posts

Posted - 05/28/2004 :  10:56:39  Show Profile  Send griffcj a Yahoo! Message  Reply with Quote
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 0
Arithmetic 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)
Go to Top of Page

mtomeo
Starting Member

USA
30 Posts

Posted - 05/28/2004 :  10:56:44  Show Profile  Reply with Quote
Silly thought, but could it be:

@MEETING_PHONE NUMERIC(9) = 2155551212

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

Go to Top of Page

griffcj
Starting Member

USA
9 Posts

Posted - 05/28/2004 :  11:18:37  Show Profile  Send griffcj a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

renante
Starting Member

Philippines
1 Posts

Posted - 06/20/2004 :  22:51:33  Show Profile  Visit renante's Homepage  Send renante an AOL message  Send renante a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/20/2004 :  23:34:59  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

Mr.Xyz
Starting Member

India
5 Posts

Posted - 06/21/2004 :  09:25:54  Show Profile  Reply with Quote
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.
Go to Top of Page

radrian
Starting Member

Afghanistan
1 Posts

Posted - 02/15/2006 :  17:53:24  Show Profile  Reply with Quote
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.

Rob

Always happy to help :-)
Go to Top of Page

sChar
Starting Member

1 Posts

Posted - 11/14/2007 :  10:25:21  Show Profile  Reply with Quote
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.
Go to Top of Page

Arun Prasad
Starting Member

India
1 Posts

Posted - 02/23/2009 :  05:10:52  Show Profile  Reply with Quote
Hi thanks a lot!!! The last piece of information helped me stop a nasty bug!!!
Go to Top of Page

BigJimSlade
Starting Member

USA
1 Posts

Posted - 11/12/2009 :  20:22:21  Show Profile  Reply with Quote
Ditto thanks to SChar
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000