Что такое QUOTED_IDENTIFIER

Мы только что столкнулись с проблемой, когда одна из сохраненных процедур выдает ошибку;

SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

Я исправил это, изменив сохраненную процедуру и установив для цитируемого идентификатора значение ON. Дело в том, что я сделал это до вызова CREATE PROCEDURE. Например;

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertStuff]

Я бы подумал, что это повлияет на оператор CREATE PROCEDURE, но не повлияет ни на что, связанное с выполнением этой процедуры.

Все наши сценарии развертываются как сценарии drop and create и запускаются через sqlcmd. Я только что прочитал здесь (выполните поиск по примеру: Выполнение SQLCMD) и здесь, что sqlcmd выполняется с < strong> цитируемый идентификатор выключен. Я изменил наш сценарий, включив переключатель -I, чтобы посмотреть, решит ли это наши проблемы.

Тогда мои вопросы;

1) Влияет ли оператор SET QUOTED_IDENTIFIER ON только на оператор DDL CREATE PROCEDURE или он также влияет на выполнение сохраненной процедуры? Мой быстрый тест указывает на последнее.

2) Поскольку по умолчанию этот переключатель включен, я предполагаю, что установка переключателя -I в моем запросе sqlcmd не окажет отрицательного воздействия. Во всех смыслах и целях я предполагаю, что это то же самое, что копировать содержимое скрипта, а затем вставлять его в диспетчер запросов и нажимать «Выполнить». Пожалуйста, поправьте меня, если я ошибаюсь. Наш простой сценарий развертывания выглядит следующим образом:

@echo off

SET dbodir=../Schema Objects/Schemas/dbo/Programmability/Stored Procedures/
SET tpmdir=../Schema Objects/Schemas/TPM/Programmability/Stored Procedures/

echo --- Starting dbo schema

for %%f in ("%dbodir%*.sql") do (echo Running %%f.... && @sqlcmd -I -U %1 -P %2 -S %3 -d %4 -i "%dbodir%%%f")

echo --- Completed dbo schema

echo --- Starting TPM schema

for %%g in ("%tpmdir%*.sql") do (echo Running %%g.... && @sqlcmd -I -U %1 -P %2 -S %3 -d %4 -i "%tpmdir%%%g")

echo --- Completed TPM schema

pause

заранее спасибо

Изменить:

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

«... Во время создания процедуры фиксируются только параметры QUOTED_IDENTIFER и ANSI_NULLS». «... SET QUOTED IDENTIFIER не может быть установлен во время выполнения внутри сохраненной процедуры» (выделено мной).

Я чувствую, что это отвечает на мой первый вопрос.

Есть ли желающие на вторую часть?


sql-server settings ddl
person Mr Moose    schedule 20.09.2011    source источник

Ответы (4)


arrow_upward
2
arrow_downward

Я сохранил следующую команду в текстовом файле, а затем выполнил ее с помощью SQLCMD:

SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER OFF

Проверяя профилировщик SQL, SQLCMD -i <filename> подключается к следующим параметрам подключения в моей системе:

-- network protocol: LPC
set quoted_identifier on
...

однако при подключении SQLCMD выдает следующую команду:

SET QUOTED_IDENTIFIER OFF SET TEXTSIZE 4096

а затем запускает мой сценарий.

Итак, ответ на 2) - нет - запуск сценария с SQLCMD -i не то же самое, что выполнение из SSMS (с параметрами подключения по умолчанию). Если для сценария требуется QUOTED_IDENTIFIER ON, вам нужно явно установить его в начале, если вы собираетесь выполнять его таким образом.

person Ed Harper    schedule 20.09.2011
comment
Привет, Эд. Я думаю, вы используете правильный переключатель, но неправильный регистр. Я имел в виду заглавную I. Нижний регистр -i указывает имя файла, верхний регистр -I относится к цитируемому идентификатору. msdn.microsoft.com/en-us/library/ms162773.aspx. Мой главный вопрос: действительно ли использование переключателя -I означает то же самое, что и запуск команды через студию управления? Тем не менее, хорошая идея использовать профилировщик. Я мог бы исследовать это дальше. - person Mr Moose; 20.09.2011
comment
@ Мистер Лось - моя ошибка. В этом случае да, это должно быть то же самое, что и выполнение через SSMS. - person Ed Harper; 20.09.2011

arrow_upward
16
arrow_downward

Если вы ищете понимание QUOTED_IDENTIFIER, я опубликую здесь кое-что.

Short version

ANSI требовал, чтобы кавычки использовались вокруг идентификаторов (а не вокруг строк). SQL Server поддерживает оба:

SQL Server изначально:

  • SELECT "Hello, world!" - кавычка
  • SELECT 'Hello, world!' --апостроф
  • CREATE TABLE [The world's most awful table name] ([Hello, world!] int)
  • SELECT [Hello, world!] FROM [The world's most awful table name]

ANSI (т. е. SET QUOTED_IDENTIFIER ON):

  • SELECT "Hello, world!" - кавычки вокруг строк больше не действительны в ANSI
  • SELECT 'Hello, world!' --апостроф
  • CREATE TABLE "The world's most awful table name" ("Hello, world!" int)
  • SELECT "Hello, world!" FROM "The world's most awful table name"

Long Version

Первоначально SQL Server позволял использовать кавычки ("...") и апострофы ('...') вокруг строк взаимозаменяемо (как это делает Javascript):

  • SELECT "Hello, world!" - кавычка
  • SELECT 'Hello, world!' --апостроф

И если вам нужна таблица имен, представление, процедура, столбец и т. Д. С чем-то, что в противном случае нарушило бы все правила именования объектов, вы можете заключить это в квадратные скобки ([, ]):

CREATE TABLE [The world's most awful table name] ([Hello, world!] int)
SELECT [Hello, world!] FROM [The world's most awful table name]

И все это сработало и имело смысл.

Then came ANSI

Затем появился ANSI, и у него были другие идеи:

  • если у вас забавное имя, заключите его в кавычки ("...")
  • используйте апостроф ('...') для строк
  • и нам даже наплевать на твои квадратные скобки

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

SELECT "Hello, world!" FROM "The world's most awful table name"

Если вы знали SQL Server, вы знали, что кавычки уже используются для представления строк. Если вы вслепую попытались выполнить этот ANSI-SQL, как если бы это был T-SQL: это ерунда, и SQL Server сказал вам об этом:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'The world's most awful table name'.

Это моральный эквивалент попытки казнить:

SELECT 'Hello, world!' FROM 'The world''s most awful table name'

Это похоже на выполнение:

SELECT 'string' FROM 'string'

You must opt-in to the new ANSI behavior

Поэтому Microsoft добавила функцию, позволяющую вам использовать ANSI-версию SQL.

Исходный (или QUOTED_IDENTIFIER выкл.):

SELECT "Hello, world!" --valid
SELECT 'Hello, world!' --valid

ВКЛЮЧИТЬ QUOTED_IDENTIFIER:

SELECT "Hello, world!" --INVALID
SELECT 'Hello, world!' --valid

SQL Server по-прежнему позволяет вам использовать [square brackets], а не заставлять вас использовать "quotation marks". Но если QUOTED_IDENTIFIER ON, вы не можете использовать "double quote quotation mark around strings", вы должны использовать только 'the single quote apostrophe'.

person Ian Boyd    schedule 24.08.2017
comment
Использование SELECT "Hello, world!" для иллюстрации имени столбца и строкового литерала сбивает с толку. Было бы лучше, если бы вы изменили SELECT "Hello, world!" FROM "The world's most awful table name" на SELECT "The world's most awful column name" FROM "The world's most awful table name" - person Caltor; 29.04.2019
comment
Замешательство идеальное; как SQL Server может узнать, если Hello, world! относится к имени столбца или строке. - person Ian Boyd; 29.04.2019
comment
Не согласен - мне пришлось объяснять примеры моему (опытному) коллеге. ИМХО хорошее преподавание должно прояснять, а не путать. Но, в конце концов, это ваш ответ. - person Caltor; 30.04.2019
comment
Причина, по которой мне это не понравилось, заключается в том, что люди могут подумать, что The world's most awful column name - это имя столбца. - person Ian Boyd; 30.04.2019
comment
Теперь я запуталась! Я думал, что некоторые из примеров были строковыми литералами, но теперь вы переименовали их все, чтобы они выглядели как имена столбцов! - person Caltor; 01.05.2019
comment
@Caltor Это было то, что вы хотели! Ставлю обратно! - person Ian Boyd; 01.05.2019
comment
Я только хотел, чтобы вы изменили его там, где это имя столбца, а у вас есть предложение FROM. Не там, где это на самом деле строковый литерал, как в последних 4 примерах. - person Caltor; 02.05.2019

arrow_upward
0
arrow_downward 

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO
--SQL PROCEDURE, SQL FUNCTIONS, SQL OBJECTGO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

ANSI NULL ON/OFF:

Этот параметр определяет настройку для сравнения ANSI NULL. Когда он включен, любой запрос, сравнивающий значение с нулевым значением, возвращает 0. Когда он выключен, любой запрос, сравнивающий значение с нулевым значением, возвращает нулевое значение.

QUOTED IDENTIFIER ON/OFF:

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

person gngolakia    schedule 24.11.2011

arrow_upward
0
arrow_downward

Причина вашего вопроса № 1 указана в Замечаниях при использовании УСТАНОВИТЬ Заявления. Говорится:

Сохраненные процедуры выполняются с параметрами SET, указанными во время выполнения, за исключением SET ANSI_NULLS и SET QUOTED_IDENTIFIER. Хранимые процедуры, указывающие SET ANSI_NULLS или SET QUOTED_IDENTIFIER, используют параметр, указанный во время создания хранимой процедуры. Если используется внутри хранимой процедуры, любой параметр SET игнорируется.

person Guillermo Gutiérrez    schedule 23.01.2017
comment
Спасибо за это. Я наткнулся на ту же информацию после того, как изначально задал этот вопрос, поэтому мое обновление с правкой. - person Mr Moose; 24.01.2017