Procedura zapamiętuje ustawienie QUOTED_IDENTIFIER - skąd bierze się błąd INSERT

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Procedura w T-SQL, która robi INSERT do tabeli z indeksem filtrowanym albo z indeksem na kolumnie obliczanej, potrafi zgłosić błąd 1934, choć jej kod jest poprawny. Administrator albo programista, który wgrywa zmiany procedur przez sqlcmd, widzi wtedy błąd INSERT i szuka go w składni. Przyczyna leży w ustawieniu QUOTED_IDENTIFIER, które procedura zapisuje w chwili CREATE albo ALTER. Nazwy tabel i procedur w kodzie są przykładowe.

Procedura zapamiętuje ustawienie QUOTED_IDENTIFIER z chwili CREATE, więc procedura utworzona przy ustawieniu OFF pada na INSERT do takiej tabeli, nawet gdy sesja wywołująca ma ustawienie ON. Naprawia ją ALTER przy ustawieniu ON, a kolumna uses_quoted_identifier w sys.sql_modules pokazuje stan ustawienia. Przykład dał ten sam wynik na poziomach zgodności 130 i 160.

Wzorzec dotyczy tylko zapisu do obiektów, które wymagają włączonego ustawienia. Zwykły INSERT do tabeli bez takiego indeksu nie zgłasza błędu, więc procedura z ustawieniem OFF może działać całkiem długo, zanim ktoś dotknie tabeli z indeksem filtrowanym. Tym trudniej ją wtedy powiązać z ostatnią zmianą.

Procedura zapisuje QUOTED_IDENTIFIER z chwili tworzenia

Procedura utworzona po SET QUOTED_IDENTIFIER OFF ma w sys.sql_modules wartość uses_quoted_identifier równą 0, a uses_ansi_nulls równą 1. Ustawienie zostaje zapisane w module i pozostaje tam niezależnie od tego, co ustawi później sesja. Przykład zaczyna od dwóch tabel z indeksami, które wymagają włączonego ustawienia, i od dwóch procedur tworzonych przy ustawieniu OFF.

Pierwszy blok czyści obiekty z poprzedniego uruchomienia i tworzy tabele. Tabela zamowienie_przyklad ma indeks filtrowany z warunkiem na kolumnie bit, a tabela obliczana_przyklad ma indeks na kolumnie obliczanej z opcją PERSISTED.

SELECT compatibility_level AS poziom_zgodnosci FROM sys.databases WHERE name = DB_NAME();
SELECT CAST(SESSIONPROPERTY('QUOTED_IDENTIFIER') AS int) AS quoted_identifier_sesji_na_starcie;
IF OBJECT_ID('dbo.dodaj_zamowienie') IS NOT NULL DROP PROCEDURE dbo.dodaj_zamowienie;
IF OBJECT_ID('dbo.dodaj_obliczana') IS NOT NULL DROP PROCEDURE dbo.dodaj_obliczana;
IF OBJECT_ID('dbo.zamowienie_przyklad') IS NOT NULL DROP TABLE dbo.zamowienie_przyklad;
IF OBJECT_ID('dbo.obliczana_przyklad') IS NOT NULL DROP TABLE dbo.obliczana_przyklad;
GO
CREATE TABLE dbo.zamowienie_przyklad (
    id int IDENTITY PRIMARY KEY, numer varchar(20) NOT NULL, aktywne bit NOT NULL DEFAULT 1);
CREATE UNIQUE INDEX ux_zamowienie_numer ON dbo.zamowienie_przyklad (numer) WHERE aktywne = 1;
CREATE TABLE dbo.obliczana_przyklad (
    id int IDENTITY PRIMARY KEY, a int NOT NULL, b int NOT NULL, suma AS (a + b) PERSISTED);
CREATE INDEX ix_obliczana_suma ON dbo.obliczana_przyklad (suma);
GO

Przykład ilustracyjny, nazwy wymyślone. Przygotowanie dwóch tabel z indeksami.

• Tabela z indeksem filtrowanym: zamowienie_przyklad, indeks z warunkiem aktywne = 1.
• Tabela z kolumną obliczaną: obliczana_przyklad, indeks na kolumnie suma z opcją PERSISTED.

Dwa pierwsze zapytania w bloku wypisują poziom zgodności bazy i ustawienie QUOTED_IDENTIFIER w sesji na starcie. Pozwalają przypisać wynik testu do poziomu zgodności i do sesji, w której go wykonano. Cztery instrukcje IF OBJECT_ID usuwają obiekty z poprzedniego uruchomienia, więc blok można powtarzać na tej samej bazie.

Ustawienie sesji wywołującej nie zmienia tego, co zapisano w procedurze

Drugi blok tworzy obie procedury po SET QUOTED_IDENTIFIER OFF, a potem włącza ustawienie w sesji poleceniem SET QUOTED_IDENTIFIER ON. Ostatnie polecenie nie zmienia procedur, które już istnieją: ich moduły zachowują wartość 0, a sesja przed wywołaniem ma wartość 1. Dwa wywołania w blokach TRY i CATCH pokazują skutek, bo każde kończy się błędem, który blok CATCH wypisuje jako wynik.

1. SET QUOTED_IDENTIFIER OFF i utworzenie obu procedur.
2. SET QUOTED_IDENTIFIER ON w sesji.
3. Odczyt uses_quoted_identifier z sys.sql_modules i ustawienia sesji.
4. Dwa wywołania w blokach TRY i CATCH, po jednym dla każdej procedury.

SET QUOTED_IDENTIFIER OFF;
GO
CREATE PROCEDURE dbo.dodaj_zamowienie @numer varchar(20)
AS INSERT INTO dbo.zamowienie_przyklad (numer) VALUES (@numer);
GO
CREATE PROCEDURE dbo.dodaj_obliczana @a int, @b int
AS INSERT INTO dbo.obliczana_przyklad (a, b) VALUES (@a, @b);
GO
SET QUOTED_IDENTIFIER ON;
GO
SELECT OBJECT_NAME(object_id) AS procedura, uses_quoted_identifier, uses_ansi_nulls FROM sys.sql_modules
 WHERE object_id IN (OBJECT_ID('dbo.dodaj_zamowienie'), OBJECT_ID('dbo.dodaj_obliczana')) ORDER BY 1;
SELECT CAST(SESSIONPROPERTY('QUOTED_IDENTIFIER') AS int) AS quoted_identifier_sesji_przed_wywolaniem;
BEGIN TRY EXEC dbo.dodaj_zamowienie @numer = 'Z-001'; END TRY
BEGIN CATCH SELECT 'filtrowany' AS tabela, ERROR_NUMBER() AS numer_bledu, ERROR_MESSAGE() AS komunikat; END CATCH
BEGIN TRY EXEC dbo.dodaj_obliczana @a = 1, @b = 2; END TRY
BEGIN CATCH SELECT 'kolumna_obliczana' AS tabela, ERROR_NUMBER() AS numer_bledu, ERROR_MESSAGE() AS komunikat; END CATCH
GO

Przykład ilustracyjny, nazwy wymyślone. Procedury tworzone przy OFF i wywołane przy włączonym ustawieniu sesji.

Ponowny test z przełącznikiem -I dał ten sam wynik. Sesja zaczynała wtedy z ustawieniem ON, a wywołania procedur utworzonych przy OFF mimo to kończyły się błędem 1934. O wyniku decyduje więc ustawienie zapisane w procedurze, a nie ustawienie sesji wywołującej.

Każda z dwóch procedur robi jeden INSERT i nic poza tym, więc różnica między nimi dotyczy wyłącznie tabeli docelowej. Pierwsza trafia w indeks filtrowany, druga w indeks na kolumnie obliczanej, a obie kończą się tym samym błędem.

INSERT do tabeli z indeksem filtrowanym albo z kolumną obliczaną zwraca błąd 1934

Wywołanie procedury, która robi INSERT do tabeli z indeksem filtrowanym z warunkiem WHERE na kolumnie bit, kończy się błędem 1934 z komunikatem podanym niżej. Ten sam błąd i ten sam komunikat daje INSERT do tabeli z indeksem na kolumnie obliczanej (PERSISTED), gdy procedura powstała przy OFF.

INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Komunikat wymienia więcej obiektów, niż sprawdza przykład, bo dodatkowo cztery: widoki indeksowane, powiadomienia, metody typu XML, indeksy przestrzenne. W teście błąd dały dwa z nich, czyli indeks filtrowany i indeks na kolumnie obliczanej, a pozostałych nie sprawdzano.

Schemat prowadzi przez pięć kroków. Kolumna uses_quoted_identifier ma wartość 0 po CREATE, po zapisie w module i przy wywołaniu, a przy kroku z błędem 1934 nadal wynosi 0. Dopiero ALTER przy włączonym ustawieniu zmienia ją na 1.

Ustawienie procedury sprawdza się w sys.sql_modules

Widok katalogu sys.sql_modules zawiera dla każdego modułu kolumny uses_quoted_identifier i uses_ansi_nulls. Zapytanie z drugiego bloku wypisuje je przed wywołaniem i daje wartość 0 dla obu procedur, a trzeci blok powtarza je po naprawie. Kontrola w katalogu jest tańsza niż szukanie błędu w kodzie, bo od razu pokazuje, z jakim ustawieniem procedura została zapisana.

Kolumna uses_ansi_nulls ma w całym przykładzie wartość 1, więc ustawienie ANSI_NULLS nie bierze udziału w błędzie. Tabela zestawia cztery kroki przykładu z wartością uses_quoted_identifier i z wynikiem.

Wartość uses_quoted_identifier zmienia się dopiero po ALTER przy włączonym ustawieniu.

ALTER przy włączonym ustawieniu naprawia procedurę

Trzeci blok przebudowuje obie procedury poleceniem ALTER, wykonanym w sesji z ustawieniem ON. Po ALTER kolumna uses_quoted_identifier ma wartość 1, a to samo wywołanie dodaje wiersz. Pierwsza tabela dostaje wiersz z numerem Z-001 i aktywnym znacznikiem, a druga wiersz z wartościami 1 i 2, którego suma wynosi 3. Po zmianie ustawienia nie ma więc żadnej różnicy w kodzie procedur.

ALTER PROCEDURE dbo.dodaj_zamowienie @numer varchar(20)
AS INSERT INTO dbo.zamowienie_przyklad (numer) VALUES (@numer);
GO
ALTER PROCEDURE dbo.dodaj_obliczana @a int, @b int
AS INSERT INTO dbo.obliczana_przyklad (a, b) VALUES (@a, @b);
GO
SELECT OBJECT_NAME(object_id) AS procedura, uses_quoted_identifier, uses_ansi_nulls FROM sys.sql_modules
 WHERE object_id IN (OBJECT_ID('dbo.dodaj_zamowienie'), OBJECT_ID('dbo.dodaj_obliczana')) ORDER BY 1;
EXEC dbo.dodaj_zamowienie @numer = 'Z-001';
EXEC dbo.dodaj_obliczana @a = 1, @b = 2;
SELECT numer, aktywne FROM dbo.zamowienie_przyklad;
SELECT a, b, suma FROM dbo.obliczana_przyklad;
GO

Przykład ilustracyjny, nazwy wymyślone. ALTER obu procedur, kontrola w katalogu i ponowne wywołania.

• ALTER przy ON: zapisuje w module wartość 1, więc INSERT przechodzi.
• ALTER przy OFF: zapisałby znowu wartość 0, więc błąd by wrócił.

W przykładzie ALTER wykonuje się po poleceniu SET QUOTED_IDENTIFIER ON z poprzedniego bloku, więc ustawienie sesji jest właściwe. Pułapką jest ALTER wykonany w sesji, która ma ustawienie OFF. Taki ALTER zapisałby w module znowu wartość 0 i błąd by wrócił. Dlatego kontrola w katalogu po każdej zmianie procedury ma sens: pokazuje wynik zmiany, a nie intencję autora skryptu.

Przełącznik -I w sqlcmd włącza QUOTED_IDENTIFIER w sesji

Narzędzie sqlcmd otwiera sesję z wyłączonym ustawieniem QUOTED_IDENTIFIER, gdy uruchamia się je bez przełącznika -I, a z przełącznikiem -I sesja ma ustawienie włączone. Test pokazał wartość 0 bez przełącznika i 1 z przełącznikiem, na obu poziomach zgodności. Skrypt wdrożeniowy, który tworzy albo zmienia procedury, powinien więc ustawiać QUOTED_IDENTIFIER jawnie w osobnej partii przed CREATE albo ALTER, zamiast polegać na domyślnym ustawieniu narzędzia.

SELECT CAST(SESSIONPROPERTY('QUOTED_IDENTIFIER') AS int) AS quoted_identifier_sesji;

Przykład ilustracyjny. Zapytanie o ustawienie sesji, uruchomione z przełącznikiem -I i bez niego.

Wynik zapytania o ustawienie sesji na obu poziomach zgodności.

Osobna partia oznacza w praktyce, że po poleceniu SET stoi GO. GO jest separatorem narzędzia, a nie częścią języka SQL, więc narzędzie wysyła wtedy do serwera dwie osobne partie, a ustawienie obowiązuje już przy CREATE albo ALTER.

Przykład (dane wymyślone): administrator wgrywa zmianę procedury dodaj_zamowienie przez sqlcmd bez przełącznika -I. Procedura dostaje QUOTED_IDENTIFIER OFF, a kod wygląda poprawnie. Pierwsze wywołanie z aplikacji kończy się błędem 1934, bo tabela zamowienie_przyklad ma indeks filtrowany. Administrator sprawdza uses_quoted_identifier w sys.sql_modules, widzi 0, wykonuje ALTER po SET QUOTED_IDENTIFIER ON i kontroluje wynik. Od tej chwili ta sama procedura dodaje wiersze. Pokrewne wzorce opisujemy w artykułach o sortowaniu liczbowym kodów zapisanych jako tekst i o aktualizacji nagłówka bez zerowania pozycji.

Pytania do analizy

Przed zmianą procedury odpowiedz na cztery pytania.

1. Jak uzasadnić, że ustawienie sesji wywołującej nie ratuje procedury utworzonej przy OFF?
2. Które obiekty w bazie wymagają QUOTED_IDENTIFIER ON przy zapisie, a które nie zgłoszą błędu?
3. Czy skrypt wdrożeniowy powinien sam sprawdzać uses_quoted_identifier po zmianie?
4. Jak odróżnić błąd 1934 od błędów składni w tym samym wdrożeniu?