Arithmetic overflow error converting varchar to data type numeric

SQL Arithmetic Overflow Error Converting Varchar to Data Type Numeric

The Arithmetic Overflow Error occurs when converting a numeric or decimal value into a varchar data type, either implicitly or explicitly, and the length of the varchar variable or column is not long enough to hold the decimal or numeric value.

Example Query :

Sql arithmetic overflow error converting varchar to data type numeric

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

I do have databases whose size are more than 1024 GB and so my query below fed on the PS script is not able to bring out the total file size values , when this part of the query is run i get that «the Arithmetic overflow error converting varchar to data type numeric» error

is there an way to avoid using an different data type .

select @@servername as ServerName,
DB_NAME(mf.database_id) as [db_name],
type_desc,
str(convert(dec(14,2),size) / 128,14,2) as DATABASE_SIZE_MB,
str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00) as DATABASE_SIZE_GB
from sys.master_files as mf
INNER JOIN sys.databases as da ON da.database_id = mf.database_id

Is this statement causes error?

str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00) as DATABASE_SIZE_GB

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

A Fan of SSIS, SSRS and SSAS

What about this:

Hi Eben Raja Earnest,

This error occurs when converting a numeric or decimal value into a varchar data type, either implicitly or explicitly, and the length of the varchar variable or column is not long enough to hold the decimal or numeric value.

We are glad to hear that you have solved your issue ,please kindly mark the helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Источник

Sql arithmetic overflow error converting varchar to data type numeric

Вопрос

I do have databases whose size are more than 1024 GB and so my query below fed on the PS script is not able to bring out the total file size values , when this part of the query is run i get that «the Arithmetic overflow error converting varchar to data type numeric» error

is there an way to avoid using an different data type .

select @@servername as ServerName,
DB_NAME(mf.database_id) as [db_name],
type_desc,
str(convert(dec(14,2),size) / 128,14,2) as DATABASE_SIZE_MB,
str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00) as DATABASE_SIZE_GB
from sys.master_files as mf
INNER JOIN sys.databases as da ON da.database_id = mf.database_id

Все ответы

Is this statement causes error?

str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00) as DATABASE_SIZE_GB

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

A Fan of SSIS, SSRS and SSAS

What about this:

Hi Eben Raja Earnest,

This error occurs when converting a numeric or decimal value into a varchar data type, either implicitly or explicitly, and the length of the varchar variable or column is not long enough to hold the decimal or numeric value.

We are glad to hear that you have solved your issue ,please kindly mark the helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Источник

Ошибка SQL: Arithmetic overflow error converting numeric to data type numeric

По запросу «Arithmetic overflow error converting numeric to data type numeric» есть куча обсуждений и очень мало записано решений. Мой случай, вероятно, частный, но кого-то подтолкнет в направлении «куда копать».

SQL 2016 Standard
Платформа 8.3.18

При выполнении запросов к БД типа:

Когда, количество строк >

10 000 000, выдавалась ошибка:

Решение

Добавлено преобразование типа Число к конкретной длине. Баг перестал воспроизводиться.

Коллеги, если сталкивались с ошибкой, в комментариях опишите причину сбоя, и, если знают — другие способы устранения.

Специальные предложения

int до 2 147 483 647, не думаю, что такое кол-во записей в регистре.

Скорее всего, это игры 1С/MSSQL с precison numeric. Например, рассматривает 1 как Numeric(7,0), пытаясь к нему преобразовать результат СУММА(1).

P.S.
В конкретном примере использование «Сумма», по-моему, неоправдано, ибо Количество(*), скорее-всего выполнится верно.
( а если есть еще индекс по одному из измерений и делать COUNT(OUR_FIELD), то и full index scan не так страшен).

Советы по использованию «Выразить», наверное, относятся к случаю, когда вместо литерала суммируют что-то более осмысленное.

Лезть в профайлер и смотреть во что 1С превращает 1, неохота.

Для подсчета кол-ва записей лучше юзать:
Выбрать Количество(1) Из РегистрНакопления.ТоварыКПоступлению

ну а если нужна сумма, то можно применить такой костыль:
Выбрать Сумма(0.001) Из РегистрНакопления.ТоварыКПоступлению
получите сумму в тысячах )

(4) мысль автора комментария по-моему осталась не законченной.
Заметил и что?
Если решил, то как?
Если знаете решение — почему не поделиться с сообществом?
Кто знает о вашем достижении?

Если по существу — то вы молодец.

(6) наверное, мой навык гугления не такой как ваш.
Видел эту статью.
Там SQL древний описан и я не стал ее читать (зря).

Ничего страшного, если решение будет записано еще раз с указанием более свежей версии SQL.

Обновление 25.08.21 11:18

См. также

Реструктуризация базы в 1С: для чего требуется и о назначении в целом

В статье расскажем о том, как в 1С:Управление торговлей происходит реструктуризация таблиц информационной базы и почему это требуется производить регулярно.

07.12.2022 1102 Koder_Line 4

Копии баз данных и размер БД. Проблемы и пути решения

Столкнулся с проблемой быстрого роста объема БД. Статья о том, как решал эту проблему.

30.11.2022 1069 DrMih 5

Работа с файлом *.dt формата

В статье расскажем о том, что такое *.dt формат, для чего он применяется, как создать *.dt файл, куда и как загружать*.dt файл, а также дадим некоторые советы по работе с *.dt файлами.

29.11.2022 1362 Koder_Line 5

Регистрация в центре лицензирования не выполнена

Каждый пользователь программ 1С сталкивался с ситуацией, когда при входе в программу 1С появляется всплывающая картинка с информацией, что «Регистрация конфигурации в центре лицензирования не выполнена», которая появляется и мешает нам работать. При этом такая ситуация может возникнуть в любой конфигурации 1С.

28.09.2022 1417 Koder_Line 2

Настройка отказоустойчивого кластера 1C + PostgreSQL (etcd+patroni+haproxy) на Centos 8

Настройка отказоустойчивого кластера PostgreSQL для сервера приложений 1С на операционной системе Centos 8.

22.08.2022 2635 user1332168 10

Workaround me в 1С/MS SQL и не только, системный подход к созданию костылей

Workaround свидетельствует о невозможности решить проблему «правильным путем» и вызывает чувство стыда. Но практика показывает, что способность решать проблемы через workaround является порой единственным способом решить проблему в разумное время. А победителей, как говорят, не судят, так почему бы не создавать workaround по системе?

15.08.2022 1080 1CUnlimited 0

Ошибка Dump в 1С

В данной статье будет рассмотрено представление ошибки Dump в 1С, будет проведена её диагностика, а также определено, как устранить данную ошибку и продолжить дальнейшую корректную работу системы 1С. Также будет представлена общая информация об ошибке Memorydump, для более глубокого её понимания.

15.07.2022 1428 Koder_Line 3

Режимы запуска системы 1С:Предприятие

Существует несколько путей установки режимов запуска 1С:Предприятие. Рассмотрим запуски системы в режиме «1С:Предприятие» и в режиме Конфигуратора. Проговорим предварительно, что одновременное использование нескольких режимов не допускается для того, чтобы указать параметры командной строки.

12.07.2022 1812 Koder_Line 1

Оптимизация высоконагруженных конфигураций: история маленькой победы, или советы тем, кто столкнулся с проблемой впервые и не знает, что делать

Пост будет больше интересен руководителям отделов ИТ сопровождения или проектным менеджерам, перед которыми будет стоять задача решения проблемы деградации производительности баз данных 1С. Пост для тех, кому эта тема нова, нет особого опыта, и с ходу непонятно, с чего начать.

24.05.2022 3702 avolsed 15

Ошибка формата потока расширения

Восстановление базы данных 1С с ошибкой «Ошибка формата потока» с «полетевшим» расширением, когда все остальные методы уже испробованы.

19.05.2022 1244 yupi71 9

Пропадающие файлы на томе в 1С: КА 2.5

На протяжении месяца пропадали файлы: прикрепленные изображения, документы в ЭДО. КА 2.5, актуальная редакция на поддержке. Этого не описано НИГДЕ и если бы я нашел такую тему, у меня мы было гораздо меньше проблем.

05.04.2022 1177 mlashko 4

Ошибка загрузки большого архива 1Cv8.dt в PostgresSQL на платформе 1С 8.3.19

1С для платформы 8.3.19 ускорили загрузку dt-файлов за счет разбивки на несколько фоновых заданий. В итоге словили ошибку блокировки при загрузке в СУБД PostgresSQL большого 1cv8.dt-файла размером 25 Gb «ERROR: canceling statement due to lock timeout». Напишу, как в итоге загрузили этот dt-файл.

30.01.2022 8090 sapervodichka 51

SAMBA для 1С

Представлен необходимый минимум настройки SAMBA для работы файловых баз 1С через общий ресурс.

24.12.2021 4288 compil7 6

Регламентное задание по завершению сеансов пользователей 1С

Завершить работу пользователей в 1С ночью. Регламентное завершение работы.

06.12.2021 2447 Swamt 20

Базовые приемы работы с кластером 1С при помощи БСП

В данной публикации я рассматриваю базовые приемы работы с кластером серверных баз 1С, используя типовые типовые возможности библиотеки стандартных подсистем (БСП).

Источник

How to Fix Arithmetic overflow error converting numeric to data type numeric in SQL Server? Example

This error comes when you try to store an out-of-range floating point value into a numeric variable. For example, if your NUMERIC or DECIMAL variable is defined as NUMERIC(5,2) then the maximum value it can hold is 999.99, if you try to store something like 1000.00 then it will throw «Arithmetic overflow error converting numeric to data type numeric». One of the common reasons for this error is the ignorance and misunderstanding of the NUMERIC data type. For example, many SQL Server DBAs and developers think that a NUMERIC(5,2) variable can hold a 7 digit floating point number where 5 digits are before the decimal and 2 digits are after the decimal. This is wrong.

A NUMERIC(5,2) means, the total number of digits in the value cannot exceed 5 and decimal precision is 2 digits i.e. the maximum possible value is 999.99.

Another thing SQL programmers don’t know and remember about NUMERIC or DECIMAL data types in Microsoft SQL Server is that it doesn’t throw this error if you specify more digits than permitted after the decimal point, instead, it does rounding for example if you store 100.999 then it will store 101.00 after rounding.

Here is an SQL query to prove these points:

Output
Arithmetic overflow error converting numeric to data type numeric.

Explanation:

Arithmetic overflow error converting numeric to data type numeric in SQL Server Examples

Here are some more SQL queries which will confirm the behavior of the NUMERIC variable and its range:

That’s all about «Arithmetic overflow error converting numeric to data type numeric in SQL Server». You can see that cause of the error is usually out-of-range value for the defined NUMERIC type. Just check the source of value and correct or increase the precision level of your column.

Always remember that NUMERIC(5,2) means a total of 5 digits with 2 digits after the decimal point, and the maximum value it can hold is 999.99. Beware of rounding due to more additional digits after the decimal point, which can also cause «Arithmetic overflow error converting numeric to data type numeric» in Microsoft SQL Server.

Источник

When you specify two filter conditions with AND, it’s up to the SQL Server to decide the order in which to apply the conditions.

Consider this code:

create table test ( data varchar(10), kind varchar(10) )

insert into test ( data, kind ) values ( '1.1', 'ABC' )

insert into test ( data, kind ) values ( '9.9', 'ABC' )

insert into test ( data, kind ) values ( '222', 'XYZ' )

insert into test ( data, kind ) values ( 'zrk', 'GHI' )

select * from test

where convert(decimal(4,2),data) > 5

and kind='ABC'

drop table test

The first time I ran this, I got this error:

Arithmetic overflow error converting numeric to data type numeric.

Why? Because SQL Server decided to check the conversion first, and then check the kind. When it got to the third row, it tried to convert ‘222’ to decimal(4,2) and failed, since it is larger than 99.99.

Next I commented out the third insertion, and ran the query again.

This time, I got this error:

Error converting data type varchar to numeric.

This time, SQL Server got to the last row and tried to convert ‘zrk’ to a number and failed, since it is not a number.

Then I un-commented out that third line, returning the code to its original form, and ran it again.

This time, the query worked, and it returned the one correct row with the value 9.9.

Why? For whatever mysterious reason, SQL Server came up with a different execution plan this time. It decided to check the ‘ABC’ filter first, and therefore never saw the other two values that would lead to errors.

How can you ensure that SQL Server would always apply the ‘ABC’ filter first? The query could be rewritten:

select * from

( select * from test

where kind='ABC' ) x

where convert(decimal(4,2),data) > 5

While developing data processes in SQL Server, under certain circumstances, you might get the error message: error converting varchar to numeric. This error is similar with the conversion error you might get when you are trying to convert a varchar to float, etc.

Read on to find out the reason for getting this error message and how you can easily resolve it within just a minute.

The Numeric Data Type in SQL Server

Prior to discuss how you can reproduce and resolve the issue, it is important that you first understand the numeric data type in SQL Server. As described in the relevant MS Docs article, the numeric data type has fixed precision and scale, and it has equivalent functionality with the decimal data type.

Arguments

The numeric data type takes two arguments, that is precision and scale. The syntax is numeric(precision, scale).

Precision defines the maximum number of decimal digits (in both sides of the number) and its value range is between 1 and 38.

Scale, defines the number of decimal digit that will be stored to the right of the decimal point. Its value can range between 1 and the value specified for precision.

Here’s an example of a numeric data type value in SQL Server:

DECLARE @numValue NUMERIC(10,2);
SET @numValue=123456.7890

SELECT @numValue as NumValue;
GO

The number returned by the above T-SQL query is: 123456.7890

In the above example I specified as precision 10 and as scale 2.

So, even though I specified 123456.7890 as the numeric value, it was indirectly converted to a numeric(10,2) value and that’s why it returned the value 123456.79

---

Learn more tips like this! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Enroll from $14.99

---

Reproducing the Conversion Error

Great. Now, let’s reproduce the conversion error by trying to convert a “problematic” varchar value to numeric.

You can find this example below:

DECLARE @valueToConvert VARCHAR(50);
SET @valueToConvert='1123,456.7890';

SELECT CAST(@valueToConvert AS NUMERIC(10,2)) as ConvertedNumber;
GO

When you execute the above T-SQL code, you will get the below exact error message:

Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

How to Resolve the Conversion Error

As you might have observed in the above example, the @valueToConvert variable, besides the dot (.), it also contains a comma (,).

Therefore, at the time of its conversion to the numeric data type, the comma is considered an illegal character for the destination data type (numeric) and that’s why you get the error message.

In order to resolve the conversion error, you just need to remove the comma (,) from the varchar value that you want to convert to numeric.

Note: At this point, you also need to make sure that the varchar value to be converted, is the actual number you wish to convert to the numeric data type. Also, you need to make sure that you only use the decimal symbol, in this case the dot (.), and not any digit grouping symbols, etc.

So, if we remove the comma from the above example, we can see that the conversion is successful.

DECLARE @valueToConvert VARCHAR(50);
SET @valueToConvert='1123456.7890';

SELECT CAST(@valueToConvert AS NUMERIC(10,2)) as ConvertedNumber;
GO

Output:

In general, when converting varchar values to numbers (i.e. decimal, numeric, etc.), you need to be careful in order for your varchar value, not contain any digit grouping symbols (i.e. a comma) or any other characters that do not have a meaning as a number.

Check our Online Courses

• SQL Server 2022: What’s New – New and Enhanced Features [New]
• Data Management for Beginners – Main Principles
• Introduction to Azure Database for MySQL
• Working with Python on Windows and SQL Server Databases
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
• A Guide on How to Start and Monetize a Successful Blog
• Data Management for Beginners – Main Principles

Read Also

Feel free to check our other relevant articles on SQL Server troubleshooting:

• Error converting data type varchar to float
• SQL Server 2022: What’s New – New and Enhanced Features (Course Preview)
• SQLServerAgent could not be started (reason: Unable to connect to server ‘(local)’; SQLServerAgent cannot start)
• ORDER BY items must appear in the select list if SELECT DISTINCT is specified
• There is no SQL Server Failover Cluster Available to Join
• There is insufficient system memory in resource pool ‘internal’ to run this query.
• There is not enough space on the disk. (mscorlib)
• A network-related or instance-specific error occurred while establishing a connection to SQL Server
• Introduction to Azure Database for MySQL (Course Preview)
• [Resolved] Operand type clash: int is incompatible with uniqueidentifier
• The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered – How to Resolve it
• SQL Server replication requires the actual server name to make a connection to the server – How to Resolve it
• Issue Adding Node to a SQL Server Failover Cluster – Greyed Out Service Account – How to Resolve
• Resolve SQL Server CTE Error – Incorrect syntax near ‘)’.
• SQL Server is Terminating Because of Fatal Exception 80000003 – How to Troubleshoot
• An existing History Table cannot be specified with LEDGER=ON – How to Resolve
• … all SQL Server troubleshooting articles

Featured Database Productivity Tools

Snippets Generator: Create and modify T-SQL snippets for use in SQL Management Studio, fast, easy and efficiently.

Learn more

Dynamic SQL Generator: Convert static T-SQL code to dynamic and vice versa, easily and fast.

Learn more

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check our eBooks!

Rate this article: (3 votes, average: 4.33 out of 5)

Loading…

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

How to resolve the error: Error converting varchar to numeric in SQL Server

Click to Tweet

Views: 18,881