SQL-Ex blog
Новости сайта «Упражнения SQL», статьи и переводы
Как исправить ошибку «Символьные или двоичные данные могут быть усечены»
Сначала давайте посмотрим на ошибку: создадим таблицу с небольшими полями, а затем попытаемся вставить больше данных, чем они могут вместить.
Машина Baby длиннее, чем 20 символов, поэтому при выполнении оператора INSERT получаем ошибку:
Это засада, поскольку у нас нет идей относительно того, какое поле вызвало проблемы! Это особенно ужасно, когда вы пытаетесь вставить множество строк.
Чтобы пофиксить ошибку, включите флаг трассировки 460
Флаг трассировки 460 был введен в SQL Server Sevice Pack 2, Cummulative Update 6, и в SQL Server 2017. (Вы можете найти и загрузить последние обновления с SQLServerUpdates.com.) Вы можете включить флаг на уровне запроса, например:
Теперь, если выполнить запрос, он покажет вам, какой столбец усекается, а также какая строка. В нашем случае мы имеем только одну строку, но в реальной жизни много полезней будет знать, какая строка вызвала ошибку:
Вы можете включить этот флаг трассировки как на уровне запроса (в нашем примере выше), так и на уровне сервера:
Я любитель включения этого флага трассировки на время отладки и изучения, но как только обнаруживаю источник проблем, выключаю его, снова выполнив команду:
В нашем случае, как только мы идентифицировали избыточную длину машины Baby, необходимо либо изменить название машины, либо изменить тип данных в нашей таблице, чтобы сделать размер столбца больше. Можно также предварительно обрабатывать данные, явно отсекая избыточные символы. Мастерская по разборке данных, если хотите.
Не оставляйте этот флаг включенным
По крайней мере, имеется связанный с этим один баг в SQL Server 2017 CU13: табличные переменные будут выбрасывать ошибки, говорящие, что их содержимое усекается, даже если никакие данные не вставляются в них.
Вот простой скрипт, чтобы проверить, пофиксили ли это поведение:
SQL Server 2017 CU13 всё еще сообщает об усечении строки, даже если строка не вставляется:
Переключение с табличной переменной на временную таблицу приводит к ожидаемому поведению:
Это замечательный пример, почему не следует использовать флаги трассировки по умолчанию. Конечно, они могут пофиксить проблемы, но они также могу вызвать непредсказуемое или нежелательное поведение. (И, вообще, я не фанат табличных переменных.)
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
ошибка, строка или двоичные данные будут обрезаны при попытке вставить
Я запускаю файл data.bat со следующими строками:
Содержимое файла data.sql:
Есть еще 8 похожих строк для добавления записей.
Когда я запускаю это с start > run > cmd > c:\data.bat Я получаю сообщение об ошибке:
Строка или двоичные данные будут проигнорированы
Проверьте структуру таблицы для таблицы клиентов. Я думаю, вы обнаружите, что длина одного или нескольких полей НЕ достаточно велика для хранения данных, которые вы пытаетесь вставить. Например, если поле Phone является полем varchar (8), и вы пытаетесь ввести в него 11 символов, вы получите эту ошибку.
У меня была эта проблема, хотя длина данных была меньше, чем длина поля. Оказалось, что проблема заключалась в том, что была другая таблица журналов (для контрольного журнала), заполненная триггером на главной таблице, где размер столбца также нужно было изменить.
В одном из INSERT операторов вы пытаетесь вставить слишком длинную строку в строку ( varchar или nvarchar ) столбца.
Если неясно, кто INSERT является нарушителем, просто взглянув на сценарий, вы можете сосчитать строки, которые появляются перед сообщением об ошибке. Полученный номер плюс один дает вам номер выписки. В вашем случае это, кажется, вторая вставка, которая выдает ошибку.
Некоторые из ваших данных не могут поместиться в столбец вашей базы данных (маленький). Нелегко найти то, что не так. Если вы используете C # и Linq2Sql, вы можете перечислить поле, которое будет усечено:
Сначала создайте вспомогательный класс:
Затем подготовьте упаковщик для SubmitChanges:
Подготовьте глобальный обработчик исключений и сведения об усечении журнала:
Наконец, используйте код:
Просто хочу внести дополнительную информацию: у меня была та же проблема, и это было из-за того, что поле было недостаточно большим для входящих данных, и этот поток помог мне решить эту проблему (верхний ответ разъясняет все это).
НО очень важно знать, каковы возможные причины, которые могут вызвать это.
В моем случае я создавал таблицу с таким полем:
Поэтому поле «Период» имеет длину ноль и приводит к сбою операций вставки. Я изменил его на «XXXXXX», то есть длину входящих данных, и теперь он работает правильно (потому что поле теперь имеет длину 6).
Я надеюсь, что это поможет любому с тем же вопросом 🙂
Другая ситуация, в которой вы можете получить эту ошибку:
У меня была та же ошибка, и причина была в том, что в операторе INSERT, который получил данные из UNION, порядок столбцов отличался от исходной таблицы. Если вы измените порядок в # table3 на a, b, c, вы исправите ошибку.
на сервере sql вы можете использовать SET ANSI_WARNINGS OFF следующим образом:
Я была такая же проблема. Длина моей колонки была слишком короткой.
Что вы можете сделать, это либо увеличить длину, либо сократить текст, который вы хотите поместить в базу данных.
Также эта проблема возникала на поверхности веб-приложения. В конце концов выяснилось, что это же сообщение об ошибке исходит из оператора обновления SQL в конкретной таблице.
Наконец, затем выяснилось, что определение столбца в соответствующих таблицах истории не отображало длину столбцов исходной таблицы nvarchar в некоторых конкретных случаях.
У меня была такая же проблема, даже после увеличения размера проблемных столбцов в таблице.
tl; dr: Длина соответствующих столбцов в соответствующих типах таблиц также может потребоваться увеличить.
В моем случае ошибка исходила от службы экспорта данных в Microsoft Dynamics CRM, которая позволяет синхронизировать данные CRM с БД SQL Server или Azure SQL DB.
После продолжительного расследования я пришел к выводу, что служба экспорта данных должна использовать табличные параметры :
Вы можете использовать табличные параметры для отправки нескольких строк данных в инструкцию Transact-SQL или подпрограмму, такую как хранимая процедура или функция, без создания временной таблицы или множества параметров.
Как вы можете видеть из документации выше, типы таблиц используются для создания процедуры приема данных:
К сожалению, нет никакого способа изменить тип таблицы, поэтому он должен быть удален и воссоздан полностью. Поскольку в моей таблице более 300 полей (😱), я создал запрос, чтобы облегчить создание соответствующего типа таблицы на основе определения столбцов таблицы (просто замените [table_name] его именем таблицы):
После обновления типа таблицы служба экспорта данных снова заработала! 🙂
Почему «строка или двоичные данные не будут усечены» более описательной ошибкой?
есть ли причина, по которой ошибка не выглядит больше так?
Это сделало бы намного проще найти и исправить значение, если не саму структуру таблицы. Если просмотр данных таблицы является проблемой безопасности, то, возможно, что-то общее, например, указание длины попытанного значения и имени неудачного столбца?
7 ответов
добавлено:
на самом деле похоже, что есть еще один запрос на эту же функцию (хотя и плохо названную), которая была выдающейся с момента разработки Yukon в 2005 году, за которую я бы рекомендовал людям голосовать а также:
обновление 2016
похоже, Microsoft пыталась удалить доказательства истинного возраста этой ошибки. Справедливо. Найдите старый сайт сохранен здесь.
Не найдя приемлемого ответа нигде, я придумал следующее:
вы должны закончить с чем-то вроде
Это создаст таблицу под названием MyTempTable в вашей БД, которую вы можете сравнить со своей целевой структурой таблицы, чтобы увидеть, где они отличаются, т. е. вы можете сравнить столбцы в обеих таблицах.
EDIT: вы можете сравнить типы данных и размеры столбцов каждого столбца в исходной таблице и MyTempTable, чтобы увидеть, где они отличаются.Все имена столбцов в новой таблице будут такими же, как и старые, и типы и размеры данных будут одинаковыми, за исключением того, где находится столбец-нарушитель. Другими словами, с помощью этого запроса SQL автоматически создаст столбцы, достаточно большие для обработки максимально возможной записи из исходной таблицы
отвечая на DUP, который закрылся, поэтому вместо этого отвечает здесь. Этот шаблон можно использовать, если он несколько сложный, но он может быть полезен, когда нетривиально изменить приложение или настроить профилировщик, чтобы увидеть, что происходит. Иногда вам просто нужно, чтобы ошибка распространялась на само приложение, чтобы вы могли видеть прямо из приложения правильное и полезное сообщение об ошибке.
в этих случаях быстрый ввод в БД с помощью этого решения сэкономит вам много времени. Сохраните его как шаблон, и сделать быстрые изменения в нем, чтобы решить эту проблему на любой таблице.
проблема
ужасная бесполезная ошибка
пример показывает только 2 столбца, где он может переполниться, но представьте, если бы это было 20 столбцов или 40.
error, string or binary data would be truncated when trying to insert
I am running data.bat file with the following lines:
The contents of the data.sql file is:
There are 8 more similar lines for adding records.
17 Answers 17
Whenever you see the message.
string or binary data would be truncated
Think to yourself. The field is NOT big enough to hold my data.
Check the table structure for the customers table. I think you’ll find that the length of one or more fields is NOT big enough to hold the data you are trying to insert. For example, if the Phone field is a varchar(8) field, and you try to put 11 characters in to it, you will get this error.
I had this issue although data length was shorter than the field length. It turned out that the problem was having another log table (for audit trail), filled by a trigger on the main table, where the column size also had to be changed.
In one of the INSERT statements you are attempting to insert a too long string into a string ( varchar or nvarchar ) column.
If it’s not obvious which INSERT is the offender by a mere look at the script, you could count the lines that occur before the error message. The obtained number plus one gives you the statement number. In your case it seems to be the second INSERT that produces the error.
Just want to contribute with additional information: I had the same issue and it was because of the field wasn’t big enough for the incoming data and this thread helped me to solve it (the top answer clarifies it all).
BUT it is very important to know what are the possible reasons that may cause it.
In my case i was creating the table with a field like this:
Therefore the field «Period» had a length of Zero and causing the Insert operations to fail. I changed it to «XXXXXX» that is the length of the incoming data and it now worked properly (because field now had a lentgh of 6).
I hope this help anyone with same issue 🙂
Some of your data cannot fit into your database column (small). It is not easy to find what is wrong. If you use C# and Linq2Sql, you can list the field which would be truncated:
First create helper class:
Then prepare the wrapper for SubmitChanges:
Prepare global exception handler and log truncation details:
Finally use the code:
Another situation in which you can get this error is the following:
I had the same error and the reason was that in an INSERT statement that received data from an UNION, the order of the columns was different from the original table. If you change the order in #table3 to a, b, c, you will fix the error.
on sql server you can use SET ANSI_WARNINGS OFF like this:
I had the same issue. The length of my column was too short.
What you can do is either increase the length or shorten the text you want to put in the database.
Also had this problem occurring on the web application surface. Eventually found out that the same error message comes from the SQL update statement in the specific table.
Finally then figured out that the column definition in the relating history table(s) did not map the original table column length of nvarchar types in some specific cases.
I had the same problem, even after increasing the size of the problematic columns in the table.
tl;dr: The length of the matching columns in corresponding Table Types may also need to be increased.
In my case, the error was coming from the Data Export service in Microsoft Dynamics CRM, which allows CRM data to be synced to an SQL Server DB or Azure SQL DB.
After a lengthy investigation, I concluded that the Data Export service must be using Table-Valued Parameters:
You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
As you can see in the documentation above, Table Types are used to create the data ingestion procedure:
Unfortunately, there is no way to alter a Table Type, so it has to be dropped & recreated entirely. Since my table has over 300 fields (😱), I created a query to facilitate the creation of the corresponding Table Type based on the table’s columns definition (just replace [table_name] with your table’s name):
After updating the Table Type, the Data Export service started functioning properly once again! 🙂
String or binary data would be truncated что за ошибка
Вопрос
Здравствуйте, у меня на SCCM 2012 SP1 возникает следующая ошибка
*** [22001][8152][Microsoft][SQL Server Native Client 11.0][SQL Server]String or binary data would be truncated. : dINSTALLED_SOFTWARE_DATA
Как я понял? на SCCM сервер приходит отчет об инвентаризации програмного обеспечения, и сервер пытается эти данные записать в таблицу dINSTALLED_SOFTWARE_DATA на сиквел сервере. Но то ли в этом отчете есть длинные строчки, то ли целиком файл для sql большой, и сиквел не принимает данные от SCCM сервера
Я запустил на SQL сервере profiler, но он выдает ту же самую ошибку String or binary data would be truncated. Но что имено нужно обрезать, не говорит.
Можно ли узнать, что конкретно не нравится сиквелу во входящих данных?
Я в профайлере включил, в дополнение к стандартным данным, вывод всех ошибок.
Ответы
Для будущих поколений:
Источник появления второго инстанса ОС при инвентаризации WMI-класса Installed_Software пока неизвестен, скорее всего это какое-то расхождение в 32- и 64-хбитных ветках реестра HKLM\Software\Microsoft\Windows NT\CurrentVersion.
Правильное решение в этом случае: перезалить машину чистой проверенной заливкой или отключить инвентаризацию этого класса.
нужно включить в Profiler отображение SP: STMTStarting ещё.
так как SQL: StmtStarting показывает только выполнение хранимой процедуры (например, dbo.dInstalled_Software_Data или dbo.pInstalled_Software_Data), а процедура, как оказалось, пишет не в одну таблицу, а в несколько разных, и SP: STMTStarting, как раз, показывает, какая таблица не может принять данные (в моем случае, это оказалась совершенно левая таблица).
Правда, не удалось разобраться, как узнать, какое поле при этом нужно увеличивать. Пришлось увеличить все nvarchar. Благо полей оказалось не так много.
Все ответы
Достаточно в профайлере добавить 2 события:
SQL: BatchStarting и Exception
Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated.
The statement has been terminated.
Вот этот SQL:BatchStarting содержит данные вида
и таких данных несколько экранов. Просто я не совсем в курсе этой ошибки. Она значит, что значение в каком-то столбце превышает допустимую длину переменной, или это может значить, что стоит null, а null запрещен, или несовпадает формат даты времени, или вот эти несколько страниц данных нельзя запихивать в сиквел одновременно?
По поводу первого, скорее всего нет, так как я смотрел, самая большая строчка 180 символов, а переменная nvarchar(255).
По поводу остального, не знаю как проверить.
Да, в одной такой записи, после exec.dbo стоят и другие таблицы из той же базы. И exeption звучит имено так
String or binary data would be truncated.
А про номер ошибки я могу узнать только из логов SCCM. Вряд ли он сам знает, что это за ошибка. Скорее всего ему эти данные дает SQL. А вот где они записаны, не понятно. В тех логах которые я включил, про 8152 ничего нет.
После этого exeption идут другие, так как запись в эту таблицу (dINSTALLED_SOFTWARE_DATA) не произошла, начинают сыпаться ошибки про таблицу содержащую суммарные данные и т.д. (Там кстати ошибка вполне конкретная: Cannot insert the value NULL into column ‘RevisionID’, table ‘CM_OPT.dbo.INSTALLED_SOFTWARE_HIST’; column does not allow nulls. INSERT fails.) Но это из-за того, что не произошла вставка первоночальных данных.
но там 15 столбцов, какой именно столбец не подходит?
И как я понял нельзя руками менять длинну переменной?
но там 15 столбцов, какой именно столбец не подходит?
И как я понял нельзя руками менять длинну переменной?
1) какой столбец вам придётся определить самостоятельно, проанализировав входные данные и размерность полей таблицы
2) что вы имеете в виду?
1) данные я проанализировал, они меньше длинны переменной. Ну, насколько это возможно, там несколько мегабайт данных, может быть что-то и пропустил.
А нет ли возможности определить это автоматически?
2) я могу в таблице выделить любой столбец, нажать изменить и поменять тип переменной, нпример, вместо nvarchar(255) поставить nvarchar(511). Но где-то было написано, что так лучше не делать.
1) данные я проанализировал, они меньше длинны переменной. Ну, насколько это возможно, там несколько мегабайт данных, может быть что-то и пропустил.
А нет ли возможности определить это автоматически?
2) я могу в таблице выделить любой столбец, нажать изменить и поменять тип переменной, нпример, вместо nvarchar(255) поставить nvarchar(511). Но где-то было написано, что так лучше не делать.
ошибка, которую вы получаете связана с размером полей, а не переменных.
Ткните, пожалуйста, где можно почитать про изменение полей в таблице.
Если нажать кнопку изменить на столбце, то я вижу название столбца, его data type, и может быть он null или нет.
Data type для столбца я изменить не могу, так как в этом случае будет пересоздана вся таблица, а в её свойствах запрещено пересоздание.
Altering a Column Definition
Спасибо, но не помогло. Ошибка осталась.
Да, действительно, ошибка осталась, но блоков данных стало приниматься больше. То есть SCCM передает данные блоками, и раньше сиквел падал на третьем блоке, а теперь на седьмом.
Но это как иголку в стоге сена 🙁 несколько сотен баз и половина полей в них не хочет увеличиваться.
Про set ansi_warnings
насколько я понял, это относится к INSERT и UPDATE. А в моем случае EXEC.
насколько я понял, это относится к INSERT и UPDATE. А в моем случае EXEC.
Сам по себе exec ничего не вставляет, вставка осуществляется через команду INSERT, поэтому вам достаточно в процедуру добавить эту настройку и все вставки пройдут без ошибок, НО(!) имейте в виду, что вы потеряете часть данных т.к. они усекутся под размер ваших полей.
И всё-таки не думаю, что так уж сложно найти в какое поле не могут быть записаны данные, профайлер в руки и несколько минут вашего времени должны помочь найти причину!
вот, я и не пойму как это сделать. Несколько сот классов инвентаризации. Можно по очереди включать их, потом дожидаться, когда на всех клиентах пройдет инвентаризация и проверять, что нет ошибок. В один блок данных попадает около трех таблиц, по 10-15 полей в каждой. это несколько недель, а не минут.
А если потом появится новый клиент со специфическим ПО или драйвером и нужно заново проверять все триста полей.
Спасибо, будем искать специалиста.
Но всё равно спасибо за помощь.
нужно включить в Profiler отображение SP: STMTStarting ещё.
так как SQL: StmtStarting показывает только выполнение хранимой процедуры (например, dbo.dInstalled_Software_Data или dbo.pInstalled_Software_Data), а процедура, как оказалось, пишет не в одну таблицу, а в несколько разных, и SP: STMTStarting, как раз, показывает, какая таблица не может принять данные (в моем случае, это оказалась совершенно левая таблица).
Правда, не удалось разобраться, как узнать, какое поле при этом нужно увеличивать. Пришлось увеличить все nvarchar. Благо полей оказалось не так много.
Для будущих поколений:
Источник появления второго инстанса ОС при инвентаризации WMI-класса Installed_Software пока неизвестен, скорее всего это какое-то расхождение в 32- и 64-хбитных ветках реестра HKLM\Software\Microsoft\Windows NT\CurrentVersion.
Правильное решение в этом случае: перезалить машину чистой проверенной заливкой или отключить инвентаризацию этого класса.
Неужели я такой счастливчик, что единственный словил этот баг.
А не подскажете как зарегистрировать можно?
А не подскажете как зарегистрировать можно?
В R2 точно не поправлено, т.к. не зарегистрировано как баг. Sad but true.
Большое спасибо, у меня руки так и не дошли зарегистрировать баг.
Правильное решение в этом случае: перезалить машину чистой проверенной заливкой или отключить инвентаризацию этого класса.
Попробовал на паре(20) машин, данные обновились:
1. Делаем коллекцию из старых машин(старые данные HardwareInventory):
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan
Если есть контракт премьер-поддержки, то Вы можете попросту попросить разрешение у меня, открыв заявку. В свою очередь я Вам буду настоятельно рекомендовать перезалить машины проверенным образом, как я писал выше.
Если честно, если Вы поменяете базу самостоятельно, с вероятностью 95% проблем не будет, а инженер поддержки этого попросту не заметит, если Вы сами ему не расскажете. 🙂
P.S. Фикс отложили на следующий релиз SCCM, который выйдет в 2015 году.
Источник появления второго инстанса ОС при инвентаризации WMI-класса Installed_Software пока неизвестен, скорее всего это какое-то расхождение в 32- и 64-хбитных ветках реестра HKLM\Software\Microsoft\Windows NT\CurrentVersion.
Правильное решение в этом случае: перезалить машину чистой проверенной заливкой или отключить инвентаризацию этого класса.
А какой тут у меня инстанс на скриншоте, Подскажите.