Today I was trying to create a table and got this message:
SQLServer1: DDL_DATABASE_LEVEL_EVENTS: Error: INSERT failed because the following SET options have incorrect settings: ‘CONCAT_NULL_YIELDS_NULL’. 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.
Msg 3616, Level 16, State 2, Line 1
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
The DDL was:
create table schema1.table1 ( column1 int not null, column2 varchar(10), column3 varchar(20), constraint PK_table1 primary key clustered ( column1 ) )
Solution: Tools menu, option, Query Execution, SQL Server, Advanced: make sure “SET CONCAT_NULL_YIELDS_NULL” is checked.
If it is not checked, the create index statement will error. In the above DDL it tried to create a clustered index for the PK hence it failed.
For further info see http://msdn.microsoft.com/en-us/library/ms176056.aspx
Leave a Reply