Сегодня я хотел бы поговорить о настройках SQL Server, инструкциях
SET. При этом не обо всех инструкциях, а только о
SET QUOTED_IDENTIFIER, т.к. эта инструкция таит в себе очень много интересных особенностей, о которых многие даже не догадываются.
Если вы считаете, что эта настройка только
"Заставляет SQL Server следовать правилам ISO относительно разделения кавычками идентификаторов и строк-литералов. Идентификаторы, заключенные в двойные кавычки, могут быть либо зарезервированными ключевыми словами Transact-SQL, либо могут содержать символы, которые обычно запрещены правилами синтаксиса для идентификаторов Transact-SQL" , то эта статья для вас, ибо я развею ваши мифы о том, что эта инструкция такая безобидная.
Для демонстрации некоторых особенностей данной инструкции, создадим тестовую БД
test_db
if db_id( N'test_db' ) is not null
begin
alter database test_db set restricted_user with rollback immediate;
drop database test_db;
end
go
create database test_db;
go
Влияние инструкции на работу с XML
Если вы используете в своей работе тип данных
XML, а особенно
Методы типа данных XML, то вам необходимо отслеживать, что настройка
set quoted_identifier имеет значение
ON.
set quoted_identifier on;
declare @xml xml = '100200300';
select t.xml.value( '.', 'int' ) as [a]
from @xml.nodes( '/root/a' ) t(xml);
В противном случаи, мы получим ошибку выполнения нашего запроса:
set quoted_identifier off;
declare @xml xml = '100200300';
select t.xml.value( '.', 'int' ) as [a]
from @xml.nodes( '/root/a' ) t(xml);
Msg 1934, Level 16, State 1, Line 6
SELECT 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.
Влияние инструкции на пространственные индексы
if object_id ( N'dbo.SpatialTable', N'U' ) is not null
drop table dbo.SpatialTable;
go
create table dbo.SpatialTable ( id int primary key, geometry_col geometry );
go
set quoted_identifier off;
create spatial index SIndx_SpatialTable_geometry_col1
on dbo.SpatialTable ( geometry_col )
with ( bounding_box = ( 0, 0, 500, 200 ) );
go
Msg 1934, Level 16, State 1, Line 4
CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with spatial index operations.
Влияние инструкции на индексы с условием
if object_id ( N'dbo.test_table_01', N'U' ) is not null
drop table dbo.test_table_01;
go
create table dbo.test_table_01 ( id int );
go
set quoted_identifier off;
create index ix
on dbo.test_table_01 ( id )
where id > 0;
go
Msg 1934, Level 16, State 1, Line 4
CREATE INDEX 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.
Влияние инструкции на индексы на вычисляемых полях
if object_id ( N'dbo.test_table_02', N'U' ) is not null
drop table dbo.test_table_02;
go
create table dbo.test_table_02 ( a int, b int, c as a + b );
go
set quoted_identifier off;
create index ix
on dbo.test_table_02 ( c );
go
Msg 1934, Level 16, State 1, Line 4
CREATE INDEX 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.
Влияние инструкции на индексированные представления
set quoted_identifier on;
if object_id ( N'dbo.test_view_01', N'V' ) is not null
drop view dbo.test_view_01;
go
if object_id ( N'dbo.test_table_03', N'U' ) is not null
drop table dbo.test_table_03;
go
create table dbo.test_table_03 ( a int );
go
create view dbo.test_view_01
with schemabinding
as
select a from dbo.test_table_03;
go
set quoted_identifier off;
create unique clustered index ix
on dbo.test_view_01 ( a );
go
Msg 1934, Level 16, State 1, Line 4
CREATE INDEX 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.
Влияние инструкции на запросы с уведомлениями (Query for Notification)
Уведомления о запросах построены на механизме отслеживания изменений, при помощи которого компонент Database Engine обслуживает индексированные представления. Требования и ограничения, предъявляемые к инструкциям в запросах с уведомлениями, аналогичны тем, что предъявляются к индексированным представлениям.
При выполнении инструкции SELECT с запросом уведомления для соединения, с которого поступил запрос, должны быть установлены следующие параметры:
- ANSI_NULLS ON
- ANSI_PADDING ON
- ANSI_WARNINGS ON
- CONCAT_NULL_YIELDS_NULL ON
- QUOTED_IDENTIFIER ON
- NUMERIC_ROUNDABORT OFF
- ARITHABORT ON
Влияние инструкции на путь в файловой системе
Если в качестве входного параметра указать путь к файловой системе в двойных кавычках, то необходимо так же отслеживать настройку
set quoted_identifier, т.к. при указании длинного пути (более 128 символов при инструкции ON мы получим ошибку).
Код ниже рабочий:
set quoted_identifier off;
go
exec master..xp_dirtree "C:\Program Files\Microsoft Visual Studio\Microsoft Visual Studio 10.0\Common7\Packages\Debugger\X64\Microsoft Visual Studio Test Folder";
go
А этот завершается исключением:
set quoted_identifier on;
go
exec master..xp_dirtree "C:\Program Files\Microsoft Visual Studio\Microsoft Visual Studio 10.0\Common7\Packages\Debugger\X64\Microsoft Visual Studio Test Folder";
go
Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'C:\Program Files\Microsoft Visual Studio\Microsoft Visual Studio 10.0\Common7\Packages\Debugger\X64\Microsoft Visual Studio Test' is too long. Maximum length is 128.
Поэтому при установленной инструкции
set quoted_identifier ON, необходимо указывать путь в одинарных кавычках или можно оставить двойные, если путь меньше 128 символов.
Эта особенность относится ко многим инструкциям, не только для рассмотренной мной процедуры
xp_dirtree, например у меня возникло подобное исключение при выполнении инструкции
BULK INSERT.
Влияние инструкции на план запроса
Все проблемы, рассмотренные выше, достаточно прозрачны, т.к., получив ошибку, мы можем оперативно изменить значение инструкции. Но существует ещё одна особенность, даже если мы смогли создать индекс с условием или индекс на вычисляемом поле, то оптимизатор не всегда сможет их использовать, т.к. мы опять же зависим от инструкции
set quoted_identifier ON.
set quoted_identifier on;
go
if object_id ( N'dbo.test_table_04', N'U' ) is not null
drop table dbo.test_table_04;
go
--Таблица с вычисляемым полем
create table dbo.test_table_04 ( id int identity primary key, a int, r_id as ( reverse( id ) ) persisted );
go
--Индекс с условием
create index xxx on dbo.test_table_04 ( a ) where a > 0;
go
А теперь эксперементы:
set quoted_identifier on;
go
--1 запрос по кластерному индексу
select * from dbo.test_table_04
where id = 1;
go
set quoted_identifier on;
go
--2 запрос по индексу с условием
select a from dbo.test_table_04
where a = 1;
go
А теперь эти же запросы, но с настройкой
OFF
set quoted_identifier off;
go
--1 запрос
select * from dbo.test_table_04
where id = 1;
go
set quoted_identifier off;
go
--2 запрос
select a from dbo.test_table_04
where a = 1;
go
Выдержка из BOL:
Когда параметры SET установлены неправильно, может произойти следующее:
- Компонент Database Engine выдаст ошибку и выполнит откат любой инструкции INSERT, UPDATE или DELETE, которая изменила значения данных, хранящихся в индексе.
- Оптимизатор запроса не учтет индекс в плане выполнения любой инструкции Transact-SQL.
- Создание индексированного представления или вычисляемого столбца окажется невозможным.
Настройка QUOTED_IDENTIFIER и сохранённый код в виде процедур/функций/представлений/триггеров
Существует ещё одна особенность некоторых инструкций, таких как
QUOTED_IDENTIFIER и
ANSI_NULLS. Эти настройки сохраняются в момент создания/изменения некоторых объектов, таких как процедуры/функции/представления/триггеры. Поэтому всегда стоит обращать внимание на то, с какими параметрами вы создаёте объекты. Ниже пример:
set quoted_identifier on;
go
if object_id ( N'dbo.test_table_05', N'U' ) is not null
drop table dbo.test_table_05;
go
--Таблица с одним полем
create table dbo.test_table_05 ( a int );
go
--Создадим процедуру, которая будет делать вставку в нашу таблицу
if object_id ( N'dbo.test_proc_01', N'P' ) is not null
drop procedure dbo.test_proc_01;
go
--Умышленно меняем настройку на OFF
set quoted_identifier off;
go
create procedure dbo.test_proc_01
@a int
as
insert into dbo.test_table_05
values ( @a )
go
Мы создали процедуру, которая делает вставку данных в нашу тестовую таблицу
dbo.test_table_05. При этом мы успешно используем её в нашем продакшн коде:
exec dbo.test_proc_01 @a = 1;
go
select * from dbo.test_table_05;
go
Код абсолютно рабочий и мы не ждём никаких сюрпризов, пока кто-то не "оптимизировал" работу с нашей таблицей и не создал, например, индекс с условием:
set quoted_identifier on;
go
create index xxx on dbo.test_table_05 (a) where a > 0;
go
При этом индекс создан с инструкцией
quoted_identifier ON, а иначе мы бы получили ошибку. И наши пользователи, не подозревая об "оптимизации" продолжают вызывать интерфейсную процедуру
dbo.test_proc_01.
exec dbo.test_proc_01 @a = 2;
go
select * from dbo.test_table_05;
go
И вместо корректного результата, наши несчастные пользователи получают уже знакомую нам ошибку:
Msg 1934, Level 16, State 1, Procedure test_proc_01, Line 5
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.
При этом их не выручает явная установка инструкции в
ON:
set quoted_identifier on;
go
exec dbo.test_proc_01 @a = 2;
go
select * from dbo.test_table_05;
go
И даже если бы в теле нашей процедуры была бы инструкция
ON, мы получили бы эту ошибку:
if object_id ( N'dbo.test_proc_01', N'P' ) is not null
drop procedure dbo.test_proc_01;
go
--Умышленно меняем настройку на OFF
set quoted_identifier off;
go
create procedure dbo.test_proc_01
@a int
as
set quoted_identifier on;
insert into dbo.test_table_05
values ( @a );
go
set quoted_identifier on;
go
exec dbo.test_proc_01 @a = 2;
go
Как я уже сказал выше, некоторые настройки сохраняются в момент создания/изменения. Отследить их можно с помощью некоторых запросов:
--Либо
select quotename( object_schema_name( object_id ), '[' ) + '.' + quotename( object_name( object_id ), '[' ) as obj
, *
from sys.sql_modules
where uses_quoted_identifier = 0;
--Либо
select quotename( schema_name( schema_id ), '[' ) + '.' + quotename( name, '[' ) as obj
, *
from sys.objects
where objectproperty ( object_id, 'ExecIsQuotedIdentOn' ) = 0;
Но и это ещё не все сюрпризы. Если у вас создаются объекты внутри процедуры, то они так же наследуют настройки, с которыми была скомпилирована базовая процедура. Пример:
if object_id ( N'dbo.test_proc_02', N'P' ) is not null
drop procedure dbo.test_proc_02;
go
--Умышленно меняем настройку на OFF
set quoted_identifier off;
go
create procedure dbo.test_proc_02
@proc_name sysname
as
set quoted_identifier on;
declare @command nvarchar(max) = N' create procedure ' + @proc_name + ' as select getdate() as dt ';
exec ( @command );
go
--Вызываем нашу процедуру с пар-ом = dbo.test_proc_03
set quoted_identifier on;
go
exec dbo.test_proc_02 @proc_name = N'dbo.test_proc_03';
go
--И смотрим, с какими пар-ми созданы наши объекты:
select quotename( object_schema_name( object_id ), '[' ) + '.' + quotename( object_name( object_id ), '[' ) as obj
, uses_quoted_identifier
from sys.sql_modules
where object_id in ( object_id( N'dbo.test_proc_02', N'P' ), object_id( N'dbo.test_proc_03', N'P' ) );
go
Но и тут не всё так гладко, предположим, что создание объектов в процедуре идёт не на прямую, а через
xp_cmdshell, которая в свою очередь вызывает утилиту командной строки
sqlcmd.exe и уже через неё создаёт объект:
if object_id ( N'dbo.test_proc_04', N'P' ) is not null
drop procedure dbo.test_proc_04;
go
--Теперь-то мы знаем, как создавать объекты
set quoted_identifier on;
go
create procedure dbo.test_proc_04
@proc_name sysname
as
declare @command nvarchar(2000) = N' create procedure ' + @proc_name + ' as select getdate() as dt ';
declare @cmd_command nvarchar(4000) = N'sqlcmd -E -d test_db -Q"' + @command + '"';
exec master..xp_cmdshell @cmd_command;
go
--Вызываем нашу процедуру с пар-ом = dbo.test_proc_05
set quoted_identifier on;
go
exec dbo.test_proc_04 @proc_name = N'dbo.test_proc_05';
go
--И смотрим, с какими пар-ми созданы наши объекты:
select quotename( object_schema_name( object_id ), '[' ) + '.' + quotename( object_name( object_id ), '[' ) as obj
, uses_quoted_identifier
from sys.sql_modules
where object_id in ( object_id( N'dbo.test_proc_04', N'P' ), object_id( N'dbo.test_proc_05', N'P' ) );
go
Всё дело в том, что разные утилиты (приложения, драйвера) подключаются с разными настройками некоторых инструкций.
Настройка QUOTED_IDENTIFIER и различие между клиентскими приложениями
И так, мы уже знаем, что настройка
QUOTED_IDENTIFIER может быть очень коварной. Теперь поговорим о различных значениях этого параметра у разных приложений. Выдержка из
BOL:
При соединении с драйвером ODBC для собственного клиента SQL Server и поставщика OLE DB для собственного клиента SQL Server для SQL Server параметр QUOTED_IDENTIFIER автоматически устанавливается в значение ON. Это может быть настроено в источниках данных ODBC, в атрибутах соединения ODBC или свойствах соединения OLE DB. По умолчанию параметр SET QUOTED_IDENTIFIER имеет значение OFF для соединений из приложений DB-Library.
А теперь небольшой список приложений и их настройки
set quoted_identifier:
| Tool | Default Quoted Identifier Setting |
| osql.exe | OFF |
| sqlcmd.exe | OFF |
| SQLCMD Scripts | ON |
| SSMS | ON |
У некоторых приложений, таких, как
bcp, есть отдельный входной параметр, отвечающий за эту настройку:
-qВыполняет инструкцию
SET QUOTED_IDENTIFIERS ON в соединении между программой
bcp и экземпляром SQL Server.
Инструкция QUOTED_IDENTIFIER и параметр базы данных DATE_CORRELATION_OPTIMIZATION
Параметр
DATE_CORRELATION_OPTIMIZATION инструкции SET улучшает производительность запросов, выполняющих эквивалентное соединение двух таблиц, у которых столбцы типа
date или
datetime коррелируют друг с другом, и содержащих ограничение на дату в предикате запроса.
Таблицы с коррелируемыми столбцами типа date или datetime, которые могут получить преимущество при включении параметра
DATE_CORRELATION_OPTIMIZATION, обычно участвуют в связи «один ко многим» и применяются главным образом для поддержки принятия решений, отчетов или для целей хранения данных.
http://msdn.microsoft.com/ru-ru/library/ms177416(v=sql.105).aspx.
select * from sys.databases
where is_date_correlation_on = 1;
При включении параметра базы данных
DATE_CORRELATION_OPTIMIZATION для каждой подходящей пары таблиц
автоматически создается статистика корреляции в форме индексированных представлений. Когда оптимизатор запросов SQL Server может извлечь пользу из сведений о корреляции двух столбцов
datetime, он использует статистику корреляции в плане запроса. Статистика корреляции также включается в логику работы инструкций INSERT, UPDATE и DELETE. Имя статистики корреляции имеет следующую форму:
_MPStats_Sys_[constraint_object_id]_[GUID]_[FK_constraint_name]
[FK_constraint_name] — это имя ограничения внешнего ключа в представлении каталога sys.objects, на котором основано соответствие столбцов типа datetime.
[constraint_object_id] — это состоящее из 8 цифр представление идентификатора objectid ограничения внешнего ключа.
Благодаря этой "оптимизации" со стороны сиквел сервера, у нас так же могут возникнуть сюрпризы, когда наша процедура, скомпилированная с параметром set quoted_identifier
OFF, вдруг перестаёт работать, т.к. у таблицы, в которую мы делали вставку из нашей процедуры, появилось новое индексированное представление (о котором мы даже не догадывались изначально). Вот такие сюрпризы может подкинуть нам SQL Server.
Инструкция QUOTED_IDENTIFIER настройки на уровне сервера и БД
Мы можем явно задать параметр
QUOTED_IDENTIFIER ON на уровне конкретной БД или всего сервера БД.
Для просмотра настроек на уровне БД или нашего соединения можно воспользоваться запросами:
--Значение текущей БД
select databasepropertyex( db_name(), 'IsQuotedIdentifiersEnabled' );
go
--Список БД, у которых настройка = OFF
select * from sys.databases where is_quoted_identifier_on = 0;
go
--Параметры нашей конкретной сессий
select quoted_identifier, * from sys.dm_exec_sessions where session_id = @@spid
go
--Соответственно сменить параметры у нашей БД:
alter database test_db set quoted_identifier on;
go
Кроме того мы можем изменить настройку на уровне сервера БД:
--Просмотр параметров сервера
select case value & 256 when 256 then 'ON' else 'OFF' end as [Quoted_Identifier]
, *
from sys.sysconfigures
where comment = 'user options';
go
--Смена настройки:
exec sp_configure 'user options', 256;
reconfigure;
go
Настройка параметра конфигурации сервера user options -
http://msdn.microsoft.com/ru-ru/library/ms190763.aspx
Кроме того существует трэйс, который выставляет значение инструкции set quoted_identifier
ON
| flag | Trace Flag Description |
| 208 | SET QUOTED IDENTIFIER ON.
|
http://www.sqlservercentral.com/articles/trace+flags/70131/
http://support.microsoft.com/kb/243352/
Но нужно помнить, что явный SET перекрывает настройки уровня БД или сервера, поэтому необходимо самостоятельно отслеживать параметры с которыми создаются объекты на вашем сервере БД.
Инструкция QUOTED_IDENTIFIER и механизм отслеживания
В качестве одного из способов, которым вы можете регулировать настройку
QUOTED_IDENTIFIER могут выступать политики SQL Server (
policy based management).
Или можно написать свой инструмент контроля в виде
DDL-триггера. Ниже пример такого триггера, которым следит за несколькими настройками, такими, как
QUOTED_IDENTIFIER/ANSI_NULLS у процедур/триггеров/функций/представлений,
ANSI_PADDING у таблиц. При этом триггер может, как логировать события, так и запрещать создание объектов, у которых настройки не соответствуют нужным нам.
--Таблица исключений, если нам всё-таки нужны будут объекты с настройками OFF
set ansi_padding on;
go
if object_id ( N'dbo.policy_options_exclusion_objects', N'U' ) is not null
drop table dbo.policy_options_exclusion_objects;
go
create table dbo.policy_options_exclusion_objects ( [object_name] sysname not null
, [schema_name] sysname not null
, constraint pk_name primary key ( [schema_name], [object_name] )
, type char(3) not null
constraint ch_name check ( [type] in ( 'U', 'P', 'TR', 'V', 'UDF' ) )
, [ansi_nulls] bit not null
constraint df_ansi_nulls default (1)
, [ansi_padding] bit not null
constraint df_ansi_padding default (1)
, [quoted_identifier] bit not null
constraint df_quoted_identifier default (1)
);
go
--Таблица-лог, если нам нужно будет откатывать нарушение наших правил, то её не создаём
if object_id ( N'dbo.policy_options_log', N'U' ) is not null
drop table dbo.policy_options_log;
go
set ansi_padding on;
go
create table dbo.policy_options_log ( [object_name] sysname not null
, [schema_name] sysname not null
, object_type sysname
, [ansi_nulls] nchar(3)
, [ansi_padding] nchar(3)
, [quoted_identifier] nchar(3)
, login_name nvarchar(256)
, command_text nvarchar(max)
, last_process_datetime datetime
, last_error nvarchar(2100)
, cnt int
);
go
create clustered index cix_policy_options_log__process_datetime on dbo.policy_options_log ( last_process_datetime );
go
--Ну и сам триггер, который и будет отслеживать значения инструкций
set ansi_nulls, quoted_identifier on;
go
if exists ( select * from sys.triggers
where parent_class = 0
and name = 'tr_ddl_policy_options'
)
drop trigger tr_ddl_policy_options
on database;
go
create trigger [tr_ddl_policy_options]
on database
for create_table , alter_table
, create_procedure, alter_procedure
, create_view , alter_view
, create_function , alter_function
, create_trigger , alter_trigger
as
set xact_abort off;
begin try
set ansi_padding on;
set ansi_nulls on;
set ansi_warnings on;
set quoted_identifier on;
set concat_null_yields_null on;
set numeric_roundabort off;
declare @object_name sysname
, @schema_name sysname
, @object_type sysname
, @ansi_nulls nchar(3)
, @ansi_padding nchar(3)
, @quoted_identifier nchar(3)
, @process_datetime datetime
, @login_name nvarchar(256)
, @command_text nvarchar(max);
select @schema_name = eventdata().value( '(/EVENT_INSTANCE/SchemaName)[1]','sysname' )
, @object_name = eventdata().value( '(/EVENT_INSTANCE/ObjectName)[1]','sysname' )
, @object_type = eventdata().value( '(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(64)' )
, @ansi_nulls = eventdata().value( '(/EVENT_INSTANCE/TSQLCommand/SetOptions/@ANSI_NULLS)[1]','nchar(3)' )
, @ansi_padding = eventdata().value( '(/EVENT_INSTANCE/TSQLCommand/SetOptions/@ANSI_PADDING)[1]','nchar(3)' )
, @quoted_identifier = eventdata().value( '(/EVENT_INSTANCE/TSQLCommand/SetOptions/@QUOTED_IDENTIFIER)[1]','nchar(3)' )
, @process_datetime = eventdata().value( '(/EVENT_INSTANCE/PostTime)[1]','datetime' )
, @login_name = eventdata().value( '(/EVENT_INSTANCE/LoginName)[1]','nvarchar(256)' )
, @command_text = eventdata().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');
--Проверка настроек ansi_nulls/quoted_identifier
if ( @object_type in ( 'TRIGGER', 'VIEW', 'PROCEDURE', 'FUNCTION' ) )
and
( @ansi_nulls = 'OFF' or @quoted_identifier = 'OFF' )
and
( not exists ( select * from dbo.policy_options_exclusion_objects with (nolock)
where [object_name] = @object_name
and [schema_name] = @schema_name
and [type] = case @object_type
when 'TRIGGER' then 'TR'
when 'VIEW' then 'V'
when 'PROCEDURE' then 'P'
when 'FUNCTION' then 'UDF'
end
and [ansi_nulls] = case @ansi_nulls
when 'OFF' then 0
when 'ON' then 1
end
and [quoted_identifier] = case @quoted_identifier
when 'OFF' then 0
when 'ON' then 1
end
)
)
begin
raiserror ( N'Объекты (P, UDF, V, TR) необходимо создавать/изменять с настройкой set quoted_identifier/ansi_nulls ON', 18, 1 );
end
--Проверка настроек ansi_padding
if ( @object_type in ( 'TABLE' ) )
and
( @ansi_padding = 'OFF' )
and
( not exists ( select * from dbo.policy_options_exclusion_objects with (nolock)
where [object_name] = @object_name
and [schema_name] = @schema_name
and [type] = 'U'
and [ansi_padding] = 0
)
)
begin
raiserror ( N'Таблицы необходимо создавать/изменять с настройкой set ansi_padding ON', 18, 1 );
end
end try
begin catch
declare @error nvarchar(2100) = 'Policy has been violated: ' + error_message();
--Либо логируем в пользовательскую таблицу, либо откатываем транзакцию и пишем в SQL-лог
if object_id ( N'dbo.policy_options_log', N'U' ) is not null
begin
merge dbo.policy_options_log l
using ( select @schema_name, @object_name, @object_type
, @ansi_nulls, @ansi_padding, @quoted_identifier
, @process_datetime, @login_name, @error, @command_text
) t ( [schema_name], [object_name], object_type
, [ansi_nulls], [ansi_padding], [quoted_identifier]
, process_datetime, login_name, error, command_text
)
on l.[schema_name] = t.[schema_name]
and l.[object_name] = t.[object_name]
and l.object_type = t.object_type
and l.[ansi_nulls] = t.[ansi_nulls]
and l.[ansi_padding] = t.[ansi_padding]
and l.[quoted_identifier] = t.[quoted_identifier]
and l.login_name = t.login_name
and l.command_text = t.command_text
when matched then update set last_process_datetime = t.process_datetime
, last_error = t.error
, cnt = isnull( cnt, 0 ) + 1
when not matched then insert ( [schema_name], [object_name], object_type
, [ansi_nulls], [ansi_padding], [quoted_identifier]
, login_name, command_text
, last_process_datetime, last_error, cnt
) values
( @schema_name, @object_name, @object_type
, @ansi_nulls, @ansi_padding, @quoted_identifier,
, @login_name, @command_text
, @process_datetime, @error, 0
);
end
else
begin
rollback;
raiserror ( @error, 18, 1 ) with log;
end;
end catch;
go
enable trigger [tr_ddl_policy_options] on database;
go
Подписаться