CODEWITHSUNDEEP
sqlsql-server
Niall O'Reilly
Niall O'Reilly

Reputation: 23

Arithmatic overflow error converting varchar to data type numeric (SQL)

I have an MS SQL 2017 database setup and I am sending RFID tags to a table and then querying the table later to see if a user exists on the system.

I am getting error:

'Arithmatic overflow error converting varchar to data type numeric'.

I have tried to change the 'rfid_id' type to VARCHAR(MAX) but no success.

My table:

CREATE TABLE worker_table ('worker_id INT IDENTITY(1,1) PRIMARY KEY, 
rfid_id VARCHAR(30) NOT NULL, 
worker_customer_name VARCHAR(30) NOT NULL,
site_id INTEGER NOT NULL REFERENCES site_table(site_id))

This works fine.

Data that I insert into the table for a given RFID:

INSERT INTO worker_table(rfid_id, worker_customer_name, site_id, worker_id 
VALUES ($'10103967375$', $'USER_1$', $'1$', $'1'))

This also works fine for most RFID tags.

However, some tags are different length e.g 330004531017 or 9600007487626. Some tags write in OK to the data base but some return the error.

Also if I query the data base with:

SELECT CASE WHEN EXISTS(SELECT * FROM worker_table WHERE rfid_id =9600007487626) THEN 100 ELSE 200 END)

This works fine for most tags but returns the same error for some.

PS I am currently sending rfid_id as a string from my PLC.

My knowledge is limited in SQL so any help would be greatly appreciated, please and thank you.

Upvotes: 2

Views: 54

Answers (2)

Suman
Suman

Reputation: 160

In SQL Server , default maximum precision of numeric datatype is 38 and in earlier version
which was supported only upto characters

Due the reason while you convert varchar to numeric overflow occurred

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Use single quotes, because your tag is a string:

SELECT (CASE WHEN EXISTS (SELECT 1
                          FROM worker_table
                          WHERE rfid_id = '9600007487626'
                         ) 
            THEN 100 ELSE 200
        END)

When you leave out the single quotes, the value is a number, and rfid_id is converted to a number -- hence the overflow.

Just use string comparisons.

Upvotes: 1

Related Questions