Ms sql server символьные или двоичные данные могут быть усечены: Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть вторая / Хабр

sql-server — Как исправить ошибку «Строковые или двоичные данные будут усечены» в SQL Server

Я не могу добавить данные в свою базу данных.

Я пробовал использовать кавычки и апострофы, но все равно не могу найти ошибку.

insert into cliente(name, email, phone, adress, postalCode)
values ('Filipe', '[email protected]', 912345678, 'Red Street', '1234-567')

ОШИБКА :

«Строка или двоичные данные будут проигнорированы»

Я надеюсь, что смогу исправить это быстро.

1

Filipe Fernandes

8 Янв 2019 в 14:21

2 ответа

Лучший ответ

Вы должны проверить столбцы и их типы данных. Один из столбцов меньше данных, которые вы пытаетесь вставить.

Так, например, если электронная почта является VARCHAR (10), это сломается, потому что ввод превышает максимальное количество символов 10.

2

SQL_M
8 Янв 2019 в 11:23

Ниже приведена хранимая процедура, которая может найти точное имя столбца и его данные, превышающие предел ширины столбца.

--DROP PROCEDURE usp_String_or_binary_data_truncated
--GO
CREATE PROCEDURE usp_String_or_binary_data_truncated
@String VARCHAR(MAX)
AS

DECLARE @VARCHAR AS VARCHAR(MAX)
DECLARE @Xml AS XML
DECLARE @TCount AS INT
SET @String= REPLACE(REPLACE(REPLACE(REPLACE(@String,'''','')
             ,'[',''),']',''),CHAR(13) + CHAR(10),'')
SET @Xml = CAST(('<a>'+REPLACE(@String,'(','</a><a>')
           +'</a>') AS XML)

SELECT @TCount=COUNT(*)
FROM @Xml.nodes('A') AS FN(A)

;WITH CTE AS
     (SELECT
     (CASE
     WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))>0)
     THEN 1
     WHEN CHARINDEX('VALUES',A.value('.', 'varchar(max)'))>0
     THEN 2
     WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))=0
     AND CHARINDEX('VALUES',A.value('.', 'varchar(max)'))=0)
     AND @TCount=2  THEN 2
     WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))=0
     AND CHARINDEX('VALUES',A.value('.', 'varchar(max)'))=0)
     AND @TCount=3  THEN 3
     END) AS[Batch Number],
     REPLACE(REPLACE(A.value('.', 'varchar(max)')
     ,'INSERT 

Как усечь журнал транзакций MSSQL Server?

Журнал транзакций во всех версиях Microsoft SQL Server  имеет тенденцию расти со временем. Иногда он может заполнить все свободное место на сервере. Чтобы избежать этого, в SQL Server есть операция усечения журнала транзакций.

Журналы транзакций SQL Server и модель восстановления базы данных

Журналы транзакций используются для записи всех транзакций перед передачей данных в файл базы данных. Файлы журнала транзакций необходимы для отката базы данных до предыдущего состояния. Как правило, в журнале хранится порядковый номер, тип изменения, выполненные операции и т. д. Скорее всего, вы можете использовать SQL Server и без журнала транзакций, но тогда в в случае сбоя возможна потеря базы данных.

Журнал транзакций состоит из небольших логических элементов, называемых VLF (Virtual Log File). Вы можете узнать их количество, выполнив следующий запрос в контексте базы данных SQL Server:

DBCC LOGINFO

Количество возвращаемых строк указывает на то, сколько виртуальных файлов сегментировано в журнале. В поле «Status» отображается текущее состояние сегмента. Значение 0 означает, что сегмент в настоящее время не занят и может использоваться. 2 означает, что сегмент используется. Если свободных сегментов нет и в настройках базы данных SQL Server разрешено увеличение журнала транзакций, он будет увеличен и будут созданы новые VLF. Если размер журнала транзакций фиксирован или на диске недостаточно места, все операции по изменению структуры базы данных или ее содержимого станут недоступными. Скорее всего, вы получите ошибку: Журнал транзакций для базы данных переполнен.

Файлы журнала усекаются автоматически, в зависимости от модели восстановления, используемой в настройках SQL Server:

• Простая модель восстановления – файлы журнала автоматически обрезаются после достижения контрольной точки (самый простой вариант, требующий администрирования базы данных). При использовании модели простого восстановления журнал транзакций очищается сразу после завершения транзакции. В этом режиме вы можете откатить вашу базу данных только до времени полного резервного копирования базы данных.
• Модель полного восстановления – журнал транзакций не будет очищен, пока не будет завершено резервное копирование журнала транзакций. Этот режим обеспечивает наилучшую возможность восстановления данных после сбоя. В полном режиме журнал транзакций (LDF) может увеличиваться (поскольку изменения базы данных накапливаются в этом журнале). В модели полного восстановления все транзакции SQL записываются в файлы журнала на диске и сохраняются там до создания резервной копии. Хранение журналов позволяет вам при необходимости вернуться к более ранней копии базы данных, и вы можете выполнить восстановление для каждой транзакции.
• С неполным протоколированием – этот режим позволяет сократить занимаемое пространство используя минимальные настройки ведения журнала. Если журнал был поврежден или с момента создания последней резервной копии журналов выполнялись операции с неполным протоколированием, все изменения после этого резервного копирования необходимо внести повторно. Иначе результаты работы потеряны не будут.

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

Как обрезать журналы транзакций на MS SQL Server?

Мы получили вот такое сообщение:

Microsoft OLE DB Provider for SQL Server: Журнал транзакций для базы данных “buh” заполнен. Чтобы обнаружить причину, по которой место в журнале не может быть повторно использовано, обратитесь к столбцу log_reuse_wait_desc таблицы
sys. databases HRESULT=80040E14, SQLStvr: Error state=2, Severity=11,native=9002, line=1

Это означает, что на диске, где хранится журнал транзакций SQL, недостаточно места, и SQL не может записать новые данные транзакции. В этом случае вы можете обрезать файлы журналов SQL вручную (с помощью  графического интерфейса Management Studio).

Чтобы обрезать журналы транзакций SQL, запустите SQL Server Management Studio (SSMS), выберите нужную базу данных, щелкните ее правой кнопкой мыши и выберите «Свойства» в контекстном меню. Перейдите в Параметры и переключите модель восстановления базы .

Меняем модель восстановления с Полной на Простую

Затем в главном меню перейдите в раздел «Задачи» -> «Сжать» -> «Файлы» .

В поле Тип файла выберите Журнал, в поле Имя файла укажите имя файла журнала. В поле «Операции сжатия» выберите « Реорганизовать страницы, перед тем как освободить неиспользуемое пространство» , установите нужный размер файла и нажмите «OK».

Нажимаем «OK». После завершения операции обязательно измените режим восстановления базы данных обратно на Полный.

Это только один из способов быстрого уменьшения размера логов. Не самый лучший, но очень простой и эффективный.

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

Лабораторная работа № 1 Использование утилиты ssms для работы с сервером Microsoft sql Server 2008

Цель
работы:
получить навыки работы с утилитой SSMS,
обеспечивающей доступ к базам данных
сервера Microsoft SQL Server.

Продолжительность
работы —
4 ч.

Теоретические сведения

Microsoft
SQL Server, или SQL Server, — одна из наиболее
мощных СУБД, имеющая клиент-серверную
архитектуру. Особенностью СУБД является
возможность ее работы только в
операционных системах Microsoft Windows, при
этом клиентская часть может взаимодействовать
с СУБД, работая под управлением Microsoft
Windows и других операционных систем.

В
составе системы имеются средства
создания баз данных, работы с информацией
баз данных, переноса данных из других
систем и в другие системы, резервного
копирования и восстановления данных,
реляционная подсистема для анализа,
оптимизации и выполнения запросов
клиентов, система безопасности для
управления правами доступа к объектам
базы данных и т.п.

Для
правильного проектирования баз данных
необходимо знание типов данных, которые
могут использоваться для полей таблиц
в базе. В табл.1представлены типы данных в Microsoft SQL
Server с разбивкой на группы по видам. В
табл.2 приведены некоторые характеристики
СУБД.

SQL
Server имеет входной язык Transact-SQL (T-SQL), в
котором помимо базовых SQL-операторов
предусмотрены программные конструкции
— параметры, переменные и логические
структуры (IF, WHILE и т.д.). В своей основе
язык T-SQL совместим со стандартом ANSI
SQL-92.

Таблица 1

Типы данных субд Microsoft sql Server

Окончание

Таблица 2

Создание ограничений в SQL Server 2005(Урок 3)

Создание ограничений

Перед тем как начать работать с таблицами следует ограничить вводимые в них данные в целях обеспечения так называемой целостности данных, т. е. ограничить возникновение в базе данных некорректных или противоречивых данных вследствие добавления, изменения или удаления какой- либо записи, например, ввод отрицательной цены или количества товара. Существует четыре типа целостности данных: доменная, сущностная, ссылочная и пользовательская (или бизнес-правила). Рассмотрим основные инструменты, предоставляемые в SQL Server для их реализации.

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

Использование проверочных ограничений

Ограничения на проверку используются для ограничения данных, принимаемых полем, даже если они имеют корректный тип. Например, поле Zip (почтовый индекс) имеет тип nchar(5), т.е. чисто теоретически оно может принимать буквы. Это может стать проблемой, поскольку не существует почтовых индексов с буквами. Рассмотрим, как создать ограничение на проверку, запрещающее вводить в это поле буквы.

1. В контекстном меню папки «Ограничения» таблицы Customer выберите команду «Создать ограничение».
2. В открывшемся окне «Проверочные ограничения» заполните следующие поля:
   • Имя: CK_Zip
   • Выражение: ([zip] like ‘[0-9][0-9][0-9][0-9][0-9]’). Данное выражение описывает ограничение, принимающее пять символов, которыми могут быть только цифры от 0 до 9.
   • Описание: Ограничение на значения почтового индекса
3. Щелкните на кнопке «Закрыть» и закройте конструктор таблиц (он был открыт, когда вы начали создавать ограничение) с сохранением изменений.

Задание для самостоятельной работы: Создайте ограничения для полей InStock таблицы Product и Qty, Price таблицы OrdItem, запрещающие ввод в них отрицательных значений. В данном случае выражение проверки будет иметь вид (Имя поля > 0) для полей Qty, Price и (Instock>=0) для столбца InStock.

Использование значений по умолчанию

Установка для полей значений по умолчанию это отличный способ избавить пользователя от излишней работы, если значения этих полей во всех записях, как правило, принимают одни и те же значения. Так в таблице заказов Order вполне логично определить по умолчанию значение поля OrdDate (дата заказа) в виде текущей даты. В этом случае при добавлении записи о новом заказе в случае пропуска этого поля оно будет автоматически заполняться значением системной даты. Для создания такого свойства выполните следующие шаги:

1. Раскройте папку «Столбцы» таблицы Order и в контекстном меню поля «OrdDate» выберите команду «Изменить».
2. В свойстве столбца «Значение или привязка по умолчанию» введите getdate(). Эта функция T-SQL возвращает текущую системную дату.
3. Щелкните на кнопке Сохранить и выйдите из конструктора таблиц.

Задание для самостоятельной работы: Установите для поля InStock (количество единиц продукта на складе) таблицы Product в качестве значения по умолчанию ноль.

Обеспечение сущностной целостности. Обеспечение гарантии уникальности записей в таблицах и предотвращение их дублирования. Основными инструментами обеспечения целостности сущностей являются первичные ключи и ограничения уникальности.

Создание первичных ключей

Первичный ключ используется для обеспечения гарантии уникальности каждой записи в таблице. Он состоит из одного (простой ключ) или нескольких (составной ключ) столбцов с гарантированно уникальными значениями для каждой записи таблицы. Если пользователь попытается ввести в поля первичного ключа дублирующее значение будет сгенерирована ошибка и модификация данных будет отменена.

В качестве примера создадим первичный ключ для таблицы Customer. В данном случае идеальным кандидатом на роль первичного ключа выступает столбец IdCust, поскольку значения, содержащиеся в нем, являются уникальными по определению (для него установлено свойство identity). Следует отметить, что в качестве первичного ключа могут быть взяты и реальные атрибуты клиента, например, ИНН, номер страхового свидетельства, серия и номер паспорта вместе взятые (пример составного ключа), но использование различных разновидностей, так называемых, суррогатных ключей (identity, uniqueidentifier) обеспечивает большую степень сущностной целостности (поскольку реальные атрибуты могут все же со временем измениться) и является распространенной практикой. Для создания первичного ключа в таблице Customer выполните следующие шаги:

1. В контекстном меню таблицы Customer выберите команду «Проект».
2. В окне конструктора таблиц щелкните правой кнопкой мыши на поле IdCust и выберите команду «Задать первичный ключ» или нажмите кнопку на панели инструментов. Обратите внимание на то, что слева от поля IdCust теперь отображается значок ключа, указывающий, что поле является первичным ключом.
3. Закройте конструктор таблиц с сохранением изменений

Задание для самостоятельной работы: Аналогичным образом создайте первичные ключи для остальных таблиц в соответствие с ниже приведенной таблицей.

Использование ограничений на уникальность

Между ограничениями первичного ключа и ограничениями на уникальность существует два отличия. Первое состоит в том, что первичные ключи используются вместе с внешними ключами для обеспечения целостности ссылок (рассматривается в следующем разделе). Второе отличие заключается в том, что ограничения на уникальность позволяют вставлять в его поля пустые значения (null), чего нельзя делать с первичными ключами. Во всем остальном они служат одной цели – обеспечить уникальность данных, вставляемых в поле. Ограничение на уникальность следует использовать в тех случаях, когда нужно гарантировать, что дублирующие значения не будут добавляться в поле, не являющееся частью первичного ключа, в частности, все потенциальные ключи должны быть организованы в виде ограничений уникальности. Хорошим примером такого поля, требующего ограничение на уникальность, является поле ИНН или серия и номер паспорта, поскольку эти поля должны быть уникальными у каждого человека. Такого идеального кандидата на роль уникального ограничения в нашей таблице Customer нет. Поэтому создадим его по полю Phone, которое также повторяться у разных клиентов не должно.

1. Для открытия конструктора таблиц в контекстном меню таблицы Customer выберите команду «Проект». На панели инструментов нажмите на кнопку «Управление индексами и ключами» .
2. В открывшемся окне «Индексы и ключи» щелкните кнопку «Добавить» и введите следующие параметры для нового уникального ключа:
   • Столбцы: Phone
   • Тип: Уникальный ключ
   • (Имя): CK_Phone

    

    

3. Закройте конструктор таблиц с сохранением изменений.

Задание для самостоятельной работы: Аналогичным образом создайте ограничение уникальности по полю CityName таблицы City, чтобы обеспечить отсутствие в справочнике городов с одинаковыми названиями, а также по полю Description таблицы Product, чтобы иметь возможность отличить один товар от другого.

Обеспечение целостности ссылок

Сейчас в базе данных Sales имеются пять таблиц, которые тесно взаимосвязаны между собой и соответственно данные содержащиеся в них должны быть согласованы и непротиворечивы. Например, в таблице Order не должно быть записей о заказах для клиента, данные о котором отсутствуют в таблице Customer. Чтобы гарантировать отсутствия в базе данных таких записей необходимо обеспечить целостность ссылок.

Суть обеспечения целостности ссылок очевидна из названия: данные в одной таблице, ссылающиеся на данные из другой таблицы, защищены от некорректного обновления. В терминологии SQL Server это называется декларативной ссылочной целостностью и достигается путем связывания первичного ключа одной из таблиц с внешним ключом другой таблицы (создается так называемое ограничение внешнего ключа).

Внешний ключ используется в комбинации с первичным для связывания двух таблиц по общему столбцу (столбцам). К примеру, можно связать таблицы Customer и Order по столбцу IdCust, который присутствует в обеих таблицах. Поскольку поле IdCust таблицы Customer является его первичным ключом можно использовать поле IdCust таблицы Order в качестве внешнего ключа, который свяжет эти две таблицы. После организации такого ограничения будет невозможно добавить запись в таблицу Order, если в таблице Customer нет записи с соответствующим значением IdCust. Кроме того, при отсутствии каскадирования (рассматривается в следующем разделе) невозможно удалить запись из таблицы Customer при наличии связанных с ней записей в таблице Order, поскольку нельзя оставлять заказ без информации о клиенте. Для создания описанного ограничения внешнего ключа в Management Studio выполните следующие шаги:

1. В контекстном меню папки «Ключи» таблицы Order выберите команду «Создать внешний ключ…».

    

    

2. В открывшемся окне «Отношения внешнего ключа» заполните следующие поля:
   • (Имя): FK_Order_Customer
   • Спецификация таблиц и столбцов: Для заполнения данного блока щелкните на кнопке с многоточием и в появившемся окне «Таблицы и столбцы» в качестве таблицы первичного ключа выберите Customer, а полей связи — IdCust.

    

    

3. Закройте все открывшиеся окна с сохранением изменений.

Использование каскадной ссылочной целостности

При наличии ограничения внешнего ключа с параметрами по умолчанию вы не можете удалить запись или изменить значение первичного ключа главной таблицы в случае наличия связанных записей в подчиненной таблице (в которой организовано ограничение внешнего ключа). Однако это поведение можно изменить, используя каскадную ссылочную целостность.

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

Задание для самостоятельной работы: Создайте ограничение внешнего ключа FK_OrdItem_Order в таблице OrderItem для связи таблиц Order и OrderItem по полю IdOrd. При этом настройте правило каскадного удаления, установив в качестве параметра «Спецификация INSERT и UPDATEУдалить правило» значение «Каскадом», что приведет к автоматическому удалению всех товаров из заказа при удалении самого заказа.

Использование диаграмм баз данных

Диаграммы базы данных представляют собой графическое отображение схемы (целиком или частично) базы данных с таблицами и столбцами, а также связей между ними. Создадим диаграмму базы данных:

1. В контекстном меню папки «Диаграммы базы данных» выберите команду «Создать диаграмму базы данных».
2. В диалоговом окне «Добавление таблиц» выберите все таблицы и нажмите на кнопку «Добавить».
3. Добавив таблицы, щелкните на кнопке «Закрыть» и вы увидите созданную диаграмму базы данных (на рисунке представлен окончательный вид диаграммы: некоторые связи у вас могут отсутствовать).

Используя диаграмму базы данных ограничения внешнего ключа можно создавать значительно быстрее: лишь перетаскивая поля из одной таблицы в другую. В качестве примера создадим внешний ключ в таблице Customer по полю IdCity для связи с таблицей City:

1. Выделите в таблице City поле IdCity и, не отпуская кнопку мыши, перетащите его на поле IdCity таблицы Customer.
2. В диалоговых окнах «Таблицы и столбцы» и «Отношение внешнего ключа» примите настройки по умолчанию.
3. Сохраните диаграмму базы данных под именем ILM.
4. Расположите таблицы в канонической форме (главные таблицы выше подчиненных) в соответствии с вышеприведенным рисунком.

Задание для самостоятельной работы: Аналогичным образом создайте связь между таблицами Product и OrdItem по полю IdProduct. Окончательный список связей между таблицами со всеми их характеристиками представлен в следующей таблице:

Задание для самостоятельной работы: После настройки всех ограничений можно наполнить таблицы данными. Для этого в контекстном меню таблицы выберите команду «Открыть таблицу» и появившейся в рабочей области вкладке введите новые записи, заполняя все необходимые столбцы. В процессе внесения данных проверьте работоспособность всех созданных ранее ограничений:

• Ограничений проверки: попробуйте ввести в поле Zip (почтовый индекс) таблицы Customer нечисловые значения, а в поля InStock таблицы Product и Qty, Price таблицы OrdItem — отрицательные.
• Значений по умолчанию: убедитесь, что при пропуске полей OrdDate и InStock таблиц Order и Product для них устанавливаются значения по умолчанию в виде текущей системной даты и нуля соответственно.
• Ограничений первичного и уникального ключа: попробуйте ввести в таблицы записи с дублирующими значениями первичного или уникального ключа.
• Ограничений внешнего ключа: попробуйте ввести несогласованные данные в связанные таблицы, например, заказ для несуществующего клиента или удалить запись из любой главной таблицы при наличии связанных записей в подчиненной при отсутствии правил каскадирования.
• Правил каскадирования: убедитесь, что при удалении записи из таблицы Order все связанные записи из таблицы OrdItem удаляются автоматически.
  

  Еще записи по теме

Просто о Transact-SQL | System Engineering

SQL (Structured Query Language) — это универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных (язык структурированных запросов).

SQL в его исходном виде является информационно-логическим языком, а не языком программирования, но вместе SQL предусматривает возможность его процедурных расширений, с учётом которых язык уже вполне может рассматриваться в качестве языка программирования.

В настоящее время широко распространенны следующие спецификации SQL:

В данной статье будет рассмотрена спецификация Transact-SQL, которая используется серверами Microsoft SQL. А так как база у всех спецификаций SQL одинаковая, то большинство команд и сценариев с легкостью переносятся на другие типы SQL.

Transact-SQL — это процедурное расширение языка SQL компаний Microsoft. SQL был расширен такими дополнительными возможностями как:

• управляющие операторы,
• локальные и глобальные переменные,
• различные дополнительные функции для обработки строк, дат, математики и т.п.,
• поддержка аутентификации Microsoft Windows

Язык Transact-SQL является ключом к использованию SQL Server. Все приложения, взаимодействующие с экземпляром SQL Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL.

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

Итак, чтобы создать базу данных и заполнить ее значениями, необходимо открыть консоль выполнения команд и запросов SQL сервера и выполнить следующий сценарий:

-- Создание базы данных
USE master
CREATE DATABASE TestDatabase
GO
-- Создание таблиц
USE TestDatabase
CREATE TABLE Users (UserID int PRIMARY KEY, UserName nvarchar(40),
 UserSurname nvarchar(40), DepartmentID int, PositionID int)
CREATE TABLE Departments (DepartmentID int PRIMARY KEY,
 DepartmentName nvarchar(40))
CREATE TABLE Positions (PositionID int PRIMARY KEY,
 PositionName nvarchar(40), BaseSalary money)
CREATE TABLE [Local Customers] (CustomerID int PRIMARY KEY,
 CustomerName nvarchar(40), CustomerAddress nvarchar(255))
CREATE TABLE [Local Orders] (OrderID int PRIMARY KEY,
 CustomerID int, UserID int, [Description] text)
GO
-- Заполнение таблиц
USE TestDatabase
INSERT Users VALUES (1, 'Ivan', 'Petrov', 1, 1)
INSERT Users VALUES (2, 'Ivan', 'Sidorov', 1, 2)
INSERT Users VALUES (3, 'Petr', 'Ivanov', 1, 2)
INSERT Users VALUES (4, 'Nikolay', 'Petrov', 1, 3)
INSERT Users VALUES (5, 'Nikolay', 'Ivanov', 2, 1)
INSERT Users VALUES (6, 'Sergey', 'Sidorov', 2, 3)
INSERT Users VALUES (7, 'Andrey', 'Bukin', 2, 2)
INSERT Users VALUES (8, 'Viktor', 'Rybakov', 4, 1)
INSERT Departments VALUES (1, 'Production')
INSERT Departments VALUES (2, 'Distribution')
INSERT Departments VALUES (3, 'Purchasing')
INSERT Positions VALUES (1, 'Manager', 1000)
INSERT Positions VALUES (2, 'Senior analyst', 650)
INSERT [Local Customers] VALUES (1, 'Alex Company', '606443, Russia, Bor, Lenina str., 15')
INSERT [Local Customers] VALUES (2, 'Potrovka', '115516, Moscow, Promyshlennaya str., 1')
INSERT [Local Orders] VALUES (1, 1, 1, 'Special parts')
GO

Примечание. В Microsoft SQL Server 2000 запросы выполняются в приложении Query Analyzer. В Microsoft SQL Server 2005 запросы выполняются в SQL Server Management Studio.

В результате работы сценария на SQL сервере будет создана база данных TestDatabase с пятью пользовательскими таблицами: Users, Departments, Positions, Local Customers, Local Orders.

Директивы сценария — это специфические команды, которые используются только в MS SQL. Эти команды помогают серверу определять правила работы со скриптом и транзакциями. Типичные представители: GO — сигнализирует SQL-серверу об окончании сценария, EXEC (или EXECUTE) — выполняет процедуру или скалярную функцию.

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

• — — строковый комментарий исключает из выполнения только одну строку, перед которой стоят два минуса.
• /* */ — блоковый комментарий исключает из выполнения целый блок команд, заключенный в указанную конструкцию.

Как и в языках программирования, в SQL существуют различные типы данных для хранения переменных:

• Числа — для хранения числовых переменных (int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).
• Даты — для хранения даты и времени (datetime, smalldatetime).
• Символы — для хранения символьных данных (char, nchar, varchar, nvarchar).
• Двоичные — для хранения бинарных данных (binary, varbinary, bit).
• Большеобъемные — типы данных для хранения больших бинарных данных (text, ntext, image).
• Специальные — указатели (cursor), 16-байтовое шестнадцатиричное число, которое используется для GUID (uniqueidentifier), штамп изменения строки (timestamp), версия строки (rowversion), таблицы (table).

Примечание. Для использования русских символов (не ASCII кодировки) испольюзуются типы данных с приставкой «n» (nchar, nvarchar, ntext), которые кодируют символы двумя байтами. Иначе говоря, для работы с Unicode используются типы данных с «n».

Примечание. Для данных переменной длины используются типы данных с приставкой «var». Типы данных без приставки «var» имеют фиксированную длину области памяти, неиспользованная часть которой заполняется пробелами или нулями.

Идентификаторы — это специальные символы, которые используются с переменными для идентифицирования их типа или для группировки слов в переменную. Типы идентификаторов:

• @ — идентификатор локальной переменной (пользовательской).
• @@ — идентификатор глобальной переменной (встроенной).
• # — идентификатор локальной таблицы или процедуры.
• ## — идентификатор глобальной таблицы или процедуры.
• [ ] — идентификатор группировки слов в переменную.

Переменные используются в сценариях и для хранения временных данных. Чтобы работать с переменной, ее нужно объявить, притом объявление должно быть осуществлено в той транзакции, в которой выполняется команда, использующая эту переменную. Иначе говоря, после завершения транзакции, то есть после команды GO, переменная уничтожается.

Объявление переменной выполняется командой DECLARE, задание значения переменной осуществляется либо командой SET, либо SELECT:

USE TestDatabase
-- Объявление переменных
DECLARE @EmpID int, @EmpName varchar(40)
-- Задание значения переменной @EmpID
SET @EmpID = 1
-- Задание значения переменной @EmpName
SELECT @EmpName = UserName FROM Users WHERE UserID = @EmpID
-- Вывод переменной @EmpName в результат запроса
SELECT @EmpName AS [Employee Name]
GO

Примечание. В этом примере используется группировка слов в переменную — конструкция [Employee Name] воспринимается как одна переменная, так как слова заключены в квадратные скобки.

Операторы — это специальные команды, предназначенные для выполнения простых операций над переменными:

• Арифметические операторы: «*» — умножить, «/» — делить, «%» — модуль от деления, «+» — сложить , «-» — вычесть, «()» — скобки.
• Операторы сравнения: «=» — равно, «>» — больше, «<» — меньше, «>=» — больше или равно, «<=» меньше или равно, «<>» — не равно.
• Операторы соединения: «+» — соединение строк.
• Логические операторы: «AND» — и, «OR» — или , «NOT» — не.

Спецификация Transact-SQl значительно расширяет стандартные возможности SQL благодаря встроенным функциям:

• Агрегативные функции- функции, которые работают с коллекциями значений и выдают одно значение. Типичные представители: AVG — среднее значение колонки, SUM — сумма колонки, MAX — максимальное значение колонки, COUNT — количество элементов колонки.
• Скалярные функции- это функции, которые возвращают одно значение, работая со скалярными данными или вообще без входных данных. Типичные представители: DATEDIFF — разница между датами, ABS — модуль числа, DB_NAME — имя базы данных, USER_NAME — имя текущего пользователя, LEFT — часть строки слева.
• Функции-указатели- функции, которые используются как ссылки на другие данные. Типичные представители: OPENXML — указатель на источник данных в виде XML-структуры, OPENQUERY — указатель на источник данных в виде другого запроса.

Примечание. Полный список функций можно найти в справке к SQL серверу.

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

Пример:

USE TestDatabase
-- Использование агрегативной функции для подсчета средней зарплаты
SELECT AVG(BaseSalary) AS [Average salary] FROM Positions
GO
-- Использование скалярной функции для получения имени базы данных
SELECT DB_NAME() AS [Database name]
GO
-- Использование скалярной функции для получения имени текущего пользователя
DECLARE @MyUser char(30)
SET @MyUser = USER_NAME()
SELECT 'The current user''s database username is: '+ @MyUser
GO
-- Использование функции-указателя для получения данных с другого сервера
SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM owner.titles')
GO

Выражение — это комбинация символов и операторов, которая получает на вход скалярную величину, а на выходе дает другую величину или исполняет какое-то действие. В Transact-SQL выражения делятся на 3 типа: DDL, DCL и DML.

• DDL (Data Definition Language)- используются для создания объектов в базе данных. Основные представители данного класса: CREATE — создание объектов, ALTER — изменение объектов, DROP — удаление объектов.
• DCL (Data Control Language)- предназначены для назначения прав на объекты базы данных. Основные представители данного класса: GRANT — разрешение на объект, DENY — запрет на объект, REVOKE — отмена разрешений и запретов на объект.
• DML (Data Manipulation Language)- используются для запросов и изменения данных. Основные представители данного класса: SELECT — выборка данных, INSERT — вставка данных, UPDATE — изменение данных, DELETE — удаление данных.

Пример:

USE TestDatabase
-- Использование DDL
CREATE TABLE TempUsers (UserID int, UserName nvarchar(40), DepartmentID int)
GO
-- Использование DCL
GRANT SELECT ON Users TO public
GO
-- Использование DML
SELECT UserID, UserName + ' ' + UserSurname AS [User Full Name] FROM Users
GO
-- Использование DDL
DROP TABLE TempUsers
GO

В Transact-SQL существуют специальные команды, которые позволяют управлять потоком выполнения сценария, прерывая его или направляя в нужную логику.

• Блок группировки — структура, объединяющая список выражений в один логический блок (BEGIN … END).
• Блок условия — структура, проверяющая выполнения определенного услов

SQL SERVER — быстрое решение проблемы «Строковые или двоичные данные будут усечены» с использованием хранимой процедуры

SQL SERVER — быстрое решение проблемы «строковые или двоичные данные будут усечены» с использованием хранимой процедуры

3 января 2014 года Мухаммад Имран

Строковые или двоичные данные будут усечены (номер ошибки 8152) — очень распространенная ошибка. Обычно это происходит, когда мы пытаемся вставить в столбец строкового типа данных (varchar, nvarchar, char, nchar) какие-либо данные, размер которых превышает размер столбца.Поэтому вам нужно проверить размер данных по отношению к ширине столбца, определить, какой столбец создает проблему, и исправить ее. Это очень просто, если вы имеете дело с меньшим количеством столбцов в таблице. Но это становится кошмаром, если вы имеете дело с инертным запросом с огромным количеством столбцов, и вам нужно проверять один за другим столбец. Я получил этот запрос от одного из моих читателей блога Г-н Рам Кумар , спрашивающий, есть ли ярлык для решения этой проблемы и предоставления имени столбца вместе с проблемами создания данных.Я начал искать решение, но не нашел подходящего. Итак, я начал разрабатывать это решение.
Прежде чем приступить к решению, я хотел бы создать образец, демонстрирующий проблему.

ОБРАЗЕЦ:

--Этот сценарий совместим с SQL Server 2005 и выше.
--DROP TABLE tbl_sample
--ИДТИ
СОЗДАТЬ ТАБЛИЦУ tbl_sample
(
 [ID] INT,
 [ИМЯ] VARCHAR (10),
)
ИДТИ
INSERT INTO tbl_sample VALUES (1, 'Боб Джек Кризи')
ИДТИ
INSERT INTO tbl_sample ([ID], [NAME]) VALUES (2, 'Frank Richard Wedge')
ИДТИ
--ВЫХОД
 

Msg 8152, Level 16, State 14, Line 1
Строковые или двоичные данные будут усечены.
Выписка прекращена.
Msg 8152, Level 16, State 14, Line 2
Строковые или двоичные данные будут усечены.
Выписка прекращена.

РЕШЕНИЕ:
Ниже приводится хранимая процедура, которая может найти точное имя столбца и его данные, превышающие предел ширины столбца.

- ПРОЦЕДУРА УДАЛЕНИЯ usp_String_or_binary_data_truncated
--ИДТИ
СОЗДАТЬ ПРОЦЕДУРУ usp_String_or_binary_data_truncated
@String VARCHAR (МАКС)
В КАЧЕСТВЕ

ОБЪЯВИТЬ @VARCHAR КАК VARCHAR (MAX)
ЗАЯВИТЬ @Xml КАК XML
ЗАЯВИТЬ @TCount AS INT
SET @ String = REPLACE (REPLACE (REPLACE (REPLACE (@String, '' '', '')
             , '[', ''), ']', ''), CHAR (13) + CHAR (10), '')
УСТАН @Xml = CAST (('' + REPLACE (@String, '(', ' ')
           + '') как XML)

ВЫБРАТЬ @ TCount = COUNT (*)
ОТ @Xml.узлы ('A') AS FN (A)

; С CTE AS
     (ВЫБРАТЬ
     (КЕЙС
КОГДА (CHARINDEX ('ВСТАВИТЬ В', A.value ('.', 'Varchar (max)'))> 0)
     ТО 1
     КОГДА CHARINDEX ('ЗНАЧЕНИЯ', A.value ('.', 'Varchar (max)'))> 0
     ТОГДА 2
КОГДА (CHARINDEX ('INSERT INTO', A.value ('.', 'Varchar (max)')) = 0
     И CHARINDEX ('ЗНАЧЕНИЯ', A.value ('.', 'Varchar (max)')) = 0)
     И @ TCount = 2 ТО 2
КОГДА (CHARINDEX ('INSERT INTO', A.value ('.', 'Varchar (max)')) = 0
     И CHARINDEX ('ЗНАЧЕНИЯ', А.значение ('.', 'varchar (max)')) = 0)
     И @ TCount = 3 ТО 3
     КОНЕЦ) КАК [Номер партии],
     ЗАМЕНИТЬ (ЗАМЕНИТЬ (A.value ('.', 'Varchar (max)')
     , 'INSERT INTO', ''), 'VALUES', '') AS [столбец]
     ОТ @ Xml.nodes ('A') КАК FN (A))

, [CTE2] AS
(
    ВЫБРАТЬ
[Серийный номер],
CAST ('' + REPLACE ([Столбец], ',', '')
    + '' Как XML)
    AS [Название столбца и данные]
    ОТ [CTE]
)
, [CTE3] AS
(
    ВЫБЕРИТЕ [номер партии],
ROW_NUMBER () БОЛЕЕ (РАЗДЕЛЕНИЕ ПО [номер пакета]
    ЗАКАЗАТЬ ПО [Номер партии] DESC) КАК [Номер строки],
    Трещина.a.value ('.', 'VARCHAR (MAX)') AS [Имя столбца и данные]
ОТ [CTE2]
CROSS APPLY [Имя столбца и данные] .nodes ('/ M') Разделить (A))

ВЫБРАТЬ
 ISNULL (B. [Имя столбца и данные], C.name) AS [Имя столбца]
, A. [Имя столбца и данные] AS [Данные столбца]
, C.max_length как [длина столбца]
, ДЛИНА ДАННЫХ (A. [Имя столбца и данные])
AS [длина данных столбца]

ОТ [CTE3] A
LEFT JOIN [CTE3] B
НА A. [Номер партии] = 2 И Б. [Номер партии] = 3
AND A. [Номер строки] = B. [Номер строки]
LEFT JOIN sys.столбцы C
ВКЛ C.object_id = (
ВЫБЕРИТЕ object_ID (LTRIM (RTRIM ([Имя столбца и данные])))
FROM [CTE3], ГДЕ [номер партии] = 1
)
И (C.name = B. [Имя столбца и данные]
ИЛИ (C.column_id = A. [Номер строки]
И A. [Номер партии] <> 1))
ГДЕ a. [Номер партии] <> 1
И ДЛИНА ДАННЫХ (A. [Имя столбца и данные])> C.max_length
И C.system_type_id IN (167,175,231,239)
И C.max_length> 0

ИДТИ

 

ПРИМЕР:
Теперь вам просто нужно заменить все одинарные кавычки вашей вставки в запрос на двойные кавычки и передать ее в хранимую процедуру.
Ниже приводится образец.

EXEC usp_String_or_binary_data_truncated 'INSERT INTO tbl_sample VALUES (1,' 'Bob Jack Creasey' ')'
ИДТИ
EXEC usp_String_or_binary_data_truncated 'INSERT INTO tbl_sample ([ID], [NAME]) VALUES (2,' 'Frank Richard Wedge' ')'
ИДТИ
--ВЫХОД
 

Как вы можете видеть выше, он вернул только имена столбцов, размеры данных которых превышают предел ширины столбца.
Дайте мне знать, если вы столкнетесь с подобной ситуацией, и разрешите ее по-другому.

Как это:

Нравится Загрузка …

Помощник по SQL Server

 Сервер: Сообщение 8152, уровень 16, состояние 9, строка 1
Строка или двоичные данные будут проигнорированы.Заявление было прекращено. 

Для иллюстрации предположим, что у вас есть следующая таблица:

 СОЗДАТЬ ТАБЛИЦУ [dbo].[Ученики] (
    [StudentID] INT,
    [Имя] VARCHAR (10),
    [LastName] VARCHAR (10)
) 

Выполнение следующего оператора INSERT приведет к появлению этого сообщения об ошибке:

ВСТАВИТЬ В [dbo]. [Студенты] ([StudentID], [FirstName], [LastName])
ЦЕННОСТИ (12345, 'Румпельштильцхен', '')
 

 Msg 8152, уровень 16, состояние 9, строка 1
Строка или двоичные данные будут проигнорированы.
Заявление было прекращено.

Поскольку столбец [FirstName] принимает только 10 символов, оператор INSERT завершится ошибкой, потому что длина вставляемого значения превышает 10 символов.

Ошибка также может возникать при уменьшении длины столбца VARCHAR или CHAR в таблице, которая уже содержит данные, и новая длина столбца недостаточна для размещения самого длинного значения в столбце.

 СОЗДАТЬ ТАБЛИЦУ [dbo]. [Студенты] (
    [StudentID] INT,
    [Имя] VARCHAR (20),
    [LastName] VARCHAR (20)
)

ВСТАВИТЬ В [dbo].[Студенты] ([StudentID], [FirstName], [LastName])
ЦЕННОСТИ (12345, 'Румпельштильцхен', '')

ALTER TABLE [dbo]. [Student] ALTER COLUMN [FirstName] VARCHAR (10)
 

 Msg 8152, уровень 16, состояние 9, строка 1
Строка или двоичные данные будут проигнорированы.
Заявление было прекращено. 

Решение / Обход:

Чтобы предотвратить появление этой ошибки при вставке данных в таблицу или при обновлении существующих данных в таблице, всегда убедитесь, что строковое значение, которое вы пытаетесь вставить в таблицу, соответствует указанному вами столбцу.Если вам действительно нужно вставить данные в таблицу, единственный вариант — увеличить длину столбца VARCHAR / CHAR с помощью команды ALTER TABLE:

 ALTER TABLE [dbo]. [Student] ALTER COLUMN [FirstName] VARCHAR (50) 

Если ошибка вызвана уменьшением длины столбца, сначала проверьте максимальную длину столбца и используйте ее в качестве новой длины столбца. Чтобы определить максимальную длину определенного столбца, вы можете сделать следующее:

 ВЫБРАТЬ МАКС. (LEN ([Имя])) ОТ [dbo].[Студенты] 

Это вернет длину самого длинного [FirstName]. Затем используйте это значение как новую длину столбца, если вам нужно уменьшить его длину.

Автоматическое усечение данных в SQL Server 2019

В этой статье мы рассмотрим улучшение усечения SQL в SQL Server 2019.

Вставка и обновление данных — обычная и обычная задача для разработчиков и администраторов баз данных, а также из приложения.Источник данных может быть в нескольких формах, как если бы прямая вставка с использованием T-SQL, хранимых процедур, функций, импорта данных из плоских файлов, пакетов SSIS и т. Д.

Иногда мы получаем неверные данные с точки зрения ограничений на количество символов, превышающих установленный предел в столбце. Например, если мы хотим вставить объемные данные в таблицу базы данных с помощью оператора вставки, мы получим неверные данные с длиной символа 11 в столбец имени сотрудника, в то время как наш существующий столбец (Employee_name) допускает только 10 символов, поэтому как будет себя вести SQL Server ? это вызовет ошибку усечения SQL.В этом случае оператор Insert завершится ошибкой. Обычно мы называем это тихим усечением и происходит, когда мы пытаемся вставить строковые данные (varchar, nvarchar, char, nchar) в размер столбца, превышающий размер.

Если мы имеем дело с огромным объемом данных с большим количеством столбцов, при возникновении какой-либо ошибки становится трудно определить, какой столбец, данные вызвали проблему. Однако для нас важно разобраться в данных и выявить неверные данные, исправить их, чтобы импортировать данные.Мы можем использовать профилировщик или расширенные события для устранения неполадок при вставке, обновлении данных, но, опять же, это ресурс и требует много времени. Мы также можем использовать настраиваемую хранимую процедуру для проверки длины данных перед вставкой в ​​таблицу, но это дополнительные накладные расходы для нас. Более того, если мы получаем частые ошибки усечения SQL, в этом случае может быть сложно устранить неполадки с помощью профилировщика и расширенных событий. Это проблема, которую разработчики и администратор баз данных использовали для освещения на другом форуме, чтобы исправить ошибку, связанную с тихим усечением данных, чтобы ее можно было быстро исправить.

Давайте сначала создадим образец базы данных, таблицу и вставим в нее фиктивные данные.

Пример 1

Как мы видим выше, мы получаем сообщение об ошибке усечения SQL: «Строковые или двоичные данные будут усечены.’

Поэтому, прежде чем мы двинемся дальше, позвольте мне объяснить, почему произошла эта ошибка. В запросе ниже мы проверяем длину строки, которую хотим вставить в нашу рабочую нагрузку.

Используя запрос ниже, мы можем проверить, что столбец Name в нашей таблице DemoSQL2019 допускает только 10 символов.

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

Пример 2

Давайте посмотрим на комплексные данные:

Здесь мы можем заметить, что в таблице много столбцов, и в нашем примере мы пытаемся вставить несколько записей.Теперь у нас есть то же сообщение об ошибке усечения SQL, но мы не получили никакого представления о том, какая строка вызывает проблему. Из-за большого количества инструкций вставки решить эту проблему будет сложно. Так было до SQL Server 2017. Теперь в разделе ниже давайте посмотрим, как SQL Server 2019 решает эту проблему.

Поведение SQL Server 2019 при усечении данных

Обязательное условие

Прежде чем мы рассмотрим решение SQL Server 2019 для решения проблемы тихого усечения данных, у нас должны быть следующие предварительные условия:

В моей предыдущей статье мы узнали, что предварительная версия SQL Server 2019 (SQL Server vNext CTP 2.0), запущенный недавно, и вы можете установить его в версии для Windows, выполнив обзор и установку SQL Server 2019.

Теперь давайте посмотрим на поведение SQL Server 2019 в связи с этой проблемой усечения SQL. Мы можем видеть в свойствах базы данных, что уровень совместимости базы данных установлен на 150, что является новым уровнем совместимости для SQL Server 2019.

Если уровень совместимости базы данных отличается от 150, мы можем изменить его, используя запрос ниже.

Теперь давайте запустим запрос из примера 1 в этой базе данных SQL Server 2019 с уровнем совместимости 150.

Мы получили аналогичную ошибку «Строковые или двоичные данные будут обрезаны». Итак, есть ли у нас такое же поведение в SQL Server 2019?

Нет, SQL Server 2019 предоставляет более полезную информацию для устранения проблемы. Мы получаем сообщение ниже, если строка или двоичные данные усекаются.

Строковые или двоичные данные будут усечены в таблице «%. * Ls», столбец «%. * Ls». Усеченное значение: «%. * Ls».

Это сообщение об ошибке четко выделяет имя таблицы, имя столбца и усеченное значение в SQL Server 2019.Поэтому мы не беспокоимся и не пытаемся найти проблемные данные или столбец с этим сообщением об ошибке. Это действительно беспокоило большинство администраторов баз данных и разработчиков, и эта функция долгое время пользовалась спросом.

Мы можем просмотреть это сообщение в sys. таблица сообщений в SQL Server 2019; идентификатор сообщения 2628 содержит это сообщение об ошибке усечения SQL. Мы видим, что таблица содержит сообщения об ошибках для одного и того же кода ошибки на другом языке. SQL Server выбирает правильный message_id на основе параметра сопоставления, который является идентификатором языка в этой таблице.

В то время как в предыдущих версиях до SQL Server 2017 отображалось только сообщение «Строковые или двоичные данные будут усечены».

В SQL Server 2019 нам нужно включить флаг трассировки 460 с помощью DBCC TraceOn. Этот флаг трассировки отображает сообщение об ошибке усечения SQL — «Строковые или двоичные данные будут усечены в таблице«%. * Ls », столбец«%. * Ls ». Усеченное значение: «%. * Ls». »

Включим флаг трассировки 460 и запустим запрос.

Теперь давайте запустим оператор вставки из нашего сценария в примере 2 с помощью DBCC TRACEON (460)

Решение для устранения усечения строк или двоичных данных

Теперь в SQL Server 2019. мы можем легко найти столбец и данные, которые вызвали проблему. Чтобы решить эту проблему, мы можем выбрать любое из приведенных ниже решений, подходящих нам.

• Исправьте данные, которые мы пытаемся вставить или обновить.Длина данных не должна превышать максимально допустимый предел для конкретного столбца.
• Используйте «SET ANSI_WARNINGS OFF», чтобы усечь данные и вставить их в соответствии с максимальной длиной строки столбца.

В приведенном ниже примере используется « SET ANSI_WARNINGS off». Следовательно, SQL Server будет усекать данные по мере необходимости, чтобы они поместились в столбце. Мы также не получим никакой ошибки усечения SQL, поскольку данные усекаются и вставляются в таблицу.

Мы видим, что SQL Server вставляет данные в соответствии с размером данных столбца. Например, «SQLShack ApexSQL Community» вставляется в таблицу после усечения как «SQLShack A».

• Измените столбец с помощью оператора alter table

Мы также можем изменить свойство столбца, чтобы разрешить данные с большей длиной строки. Мы можем использовать приведенный ниже запрос для изменения таблицы.

В приведенном ниже примере мы изменили столбец [CustomerName] на varchar (50) с varchar (30) и выполнили запрос из примера 2.На этот раз он выполняется успешно.

Заключение

Тихое усечение данных в SQL Server 2019 — действительно хорошее улучшение. Это порадует многих администраторов баз данных и разработчиков подробным сообщением об ошибке усечения SQL. В настоящее время мы должны включить флаг трассировки в SQL Server 2019. Я считаю, что эта функция будет доступна без флага трассировки в следующем выпуске.

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

Работая старшим консультантом администратора баз данных для крупных заказчиков и получив сертификат MCSA SQL 2012, он любит делиться знаниями в различных блогах.
С ним можно связаться по адресу [email protected]

Посмотреть все сообщения Rajendra Gupta

Последние сообщения Rajendra Gupta (посмотреть все)

Строковые или двоичные данные будут усечены: получить полную картину в SQL Server 2017

Предварительная версия SQL Server 2019 (CTP 2.0) представила долгожданное улучшение сообщения об ошибке, которое было в SQL Server много лет, но не помогло:

Msg 8152, уровень 16, состояние 6, процедура имя процедуры, номер строки
Строка или двоичные данные будут проигнорированы.

Спасибо ни за что, сообщение об ошибке. Какой стол? Какой столбец? Какие данные?

Вот как теперь выглядит сообщение об ошибке:

Msg 2628, уровень 16, состояние 6, процедура имя процедуры, номер строки
Строковые или двоичные данные будут усечены в таблице «%.* ls ‘, столбец’%. * ls ‘.
Усеченное значение: «%. * Ls».

Обратите внимание, что таблица , столбец и значение теперь упоминаются в сообщении об ошибке, что значительно упрощает отладку и устранение неполадок.Спасибо, Microsoft!

По состоянию на 24 октября 2018 г. теперь мы можем получить полную картину и в SQL Server 2017 при условии, что мы установим накопительное обновление 12. Я бы сказал, что это само по себе стоит обновления!

Как использовать эту новую информацию

Корпорация Майкрософт имеет давнюю политику поддержки обратной совместимости, поэтому включение этой функции не является обязательным, оно активируется с помощью Trace Flag 460 . Вот как это сделать в Windows и Linux:

SQL Server 2017 в Windows

В Windows мы можем включить флаг трассировки с помощью диспетчера конфигурации SQL Server.Если мы используем Windows 10 или Windows Server 2016, нам может потребоваться вручную найти Configuration Manager.

В разделе «Службы SQL Server» найдите службу SQL Server , щелкните ее правой кнопкой мыши и выберите Свойства .

Перейдите на вкладку Startup Parameters и введите -T460 (обратите внимание, что между -T и номером флага трассировки нет пробела).

Щелкните Добавить , а затем щелкните Применить .Теперь мы должны увидеть флаг трассировки в списке существующих параметров .

После нажатия ОК , последним шагом будет перезапуск службы SQL Server, чтобы изменения вступили в силу. Мы можем сделать это, снова щелкнув правой кнопкой мыши службу SQL Server и выбрав Restart . Это также перезапустит агент SQL Server, если он запущен.

SQL Server 2017 в Linux

В SQL Server в Linux (обратите внимание, что это включает SQL Server в контейнерах Docker), мы можем добавить флаг трассировки в экземпляр SQL Server с помощью инструмента командной строки mssql-conf .

В командной строке нашего экземпляра SQL Server выполните следующую команду, чтобы добавить Trace Flag 460.

sudo / opt / mssql / bin / mssql-conf traceflag 460 на

Как и в случае с Windows, нам необходимо перезапустить службу SQL Server, чтобы изменения вступили в силу.

sudo systemctl перезапустить mssql-server

А как насчет более старых версий SQL Server?

Хотя он пока недоступен в SQL Server 2016, мы можем ожидать этого в ближайшем будущем.Я бы точно не стал задерживать дыхание от SQL Server 2014 и более старых версий.

И помните, что срок службы SQL Server 2008 и 2008 R2 истекает в июле 2019 года. Если вы не обновились хотя бы до SQL Server 2014, у вас впереди много работы на следующие два года.

Поделитесь своими мыслями о сообщениях об ошибках SQL Server в комментариях ниже.

Нравится:

Нравится Загрузка …

Связанные

DBServerInfo Задание данных завершается неудачно с ошибкой «Строковые или двоичные данные будут усечены.«

Вопрос

Почему задание DBServerInfo Data завершается ошибкой «Строковые или двоичные данные будут усечены»?

Ответ

Задание DBServerInfo Data вызывает хранимую процедуру iss_DBSI_PrepareData_Update, которая используется для сбора обновленной информации о базе данных, которую команда DBServerInfo использует при запуске.

Монитор активности заданий агента SQL Server указывает на сбой задания DBServerInfo Data.При более тщательном изучении истории задания задание завершается ошибкой «Строковые или двоичные данные будут усечены».

Если вы попытаетесь запустить хранимую процедуру iss_DBSI_PrepareData_Update, вы получите ту же ошибку.

Важно отметить, что это задание не критично для работы SiteProtector, но важно для устранения неполадок. Это означает, что эта проблема не повлияет на функциональность SiteProtector.

Ошибка «Строка или двоичные данные будут усечены» означает, что процедура пытается сохранить в таблице DBServerInfo что-то, размер которого превышает допустимый для столбца.Это может произойти по двум известным причинам:

1. SQL Server имеет как минимум одну базу данных, имя которой превышает 25 символов.
2. Параметр «Показать дополнительные параметры» включен в SQL Server, и вы используете версию SQL Server Enterprise.

Для проблемы №1 единственное решение — либо переименовать базу данных, вызывающую проблему, либо переместить ее на другой сервер SQL.

Вы можете проверить, является ли проблема №2 проблемой, выполнив SQL-запрос:

sp_configure

Если одно из имен параметров, возвращенных в результате, является «соответствие общим критериям включено», то можно решить проблему проблема, выполнив следующий запрос, чтобы скрыть расширенные параметры из результатов sp_configure:

sp_configure 'show advanced options', 0;
перейти
перенастроить;
go

Если вы запустите задание DBServerInfo Data, оно должно работать без проблем.Если хранимая процедура продолжает выдавать эту или другие ошибки, обратитесь в службу поддержки, чтобы получить исправление для ваших конкретных нужд.

[{«Продукт»: {«код»: «SSETBF», «ярлык»: «IBM Security SiteProtector System»}, «Бизнес-подразделение»: {«код»: «BU008», «ярлык»: «Безопасность»}, «Компонент»: «База данных», «Платформа»: [{«код»: «PF033», «метка»: «Windows»}], «Версия»: «Независимая от версии», «Редакция»: «», «Строка of Business «: {» code «:» LOB24 «,» label «:» Security Software «}}]

Строковые или двоичные данные будут усечены

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

«up_InsertCustomerService» — это хранимая процедура, которую она использовала

[HTML], установите ANSI_NULLS ON
установить QUOTED_IDENTIFIER на
go

ИЗМЕНЕНИЕ ПРОЦЕДУРЫ [dbo]. [up_InsertCustomerService]

@MemberID int,
@OfferID varchar (100),
@CantAccess varchar (100),
@WontFunction varchar (100),
@ Другой ntext

AS

ВСТАВИТЬ В
Служба поддержки клиентов
(
MemberID,
OfferID,
CantAccess,
WontFunction,
Другое
)
ВЫБРАТЬ
@MemberID,
OfferIDList.Стоимость,
CantAccessList.Value,
WontFunctionList.Value,
OtherList.Value
ИЗ
ufn_ConvertListToTable (@OfferID) OfferIDList
ПРИСОЕДИНЯЙТЕСЬ к ufn_ConvertListToTable (@CantAccess) CantAccessList НА CantAccessList.ID = OfferIDList.ID
ПРИСОЕДИНЯЙТЕСЬ к ufn_ConvertListToTable (@WontFunction) WontFunctionList ON WontFunctionList.ID = OfferIDList.ID
ПРИСОЕДИНЯЙТЕСЬ к ufn_ConvertListToTable (@Other) OtherList ON OtherList.ID = OfferIDList.ID

[/ HTML]
а функция ufn_ConvertListToTable —
[HTML] установить ANSI_NULLS на
установить QUOTED_IDENTIFIER на
go

ИЗМЕНЕНИЕ ФУНКЦИИ [dbo].[ufn_ConvertListToTable] (@list ntext)
— установить Value varchar (100) на ntext
ВОЗВРАЩАЕТ ТАБЛИЦУ @tbl (ID INT IDENTITY, значение ntext NOT NULL) КАК
НАЧАТЬ
ЗАЯВИТЬ
@pos int,
@textpos int,
@chunklen smallint,
@str nvarchar (4000),
@tmpstr nvarchar (4000),
@leftover nvarchar (4000)

НАБОР @textpos = 1
НАБОР @leftover = »
WHILE @textpos <= datalength (@list) / 2
НАЧАТЬ
SET @chunklen = 4000 — длина данных (@leftover) / 2
НАБОР @tmpstr = ltrim (@leftover + substring (@list, @textpos, @chunklen))
НАБОР @textpos = @textpos + @chunklen

НАБОР @pos = charindex (‘|’, @tmpstr)
ПОКА @pos> 0
НАЧАТЬ
НАБОР @str = подстрока (@tmpstr, 1, @pos — 1)
ВСТАВИТЬ @tbl (Значение) ЗНАЧЕНИЯ (@str)
НАБОР @tmpstr = ltrim (подстрока (@tmpstr, @pos + 1, len (@tmpstr)))
НАБОР @pos = charindex (‘|’, @tmpstr)
КОНЕЦ

НАБОР @leftover = @tmpstr
КОНЕЦ

IF ltrim (rtrim (@leftover)) <> »
ВСТАВИТЬ @tbl (Значение) ЗНАЧЕНИЯ (@leftover)

ВОЗВРАТ
END

[/ HTML]

Я обнаружил ту же причину несоответствия типов данных или переполнения данных, но еще не определил, где в функции «ConvertListToTable» ее нужно изменить

.