Msg 2501, Level 16, State 45, Line 1
The CONCAT_NULL_YIELDS_NULL option must be set to ON to run DBCC CHECKCONSTRAINTS.
This error message appears when you try to use the DBCC CHECKCONSTRAINTS command while the CONCAT_NULL_YIELDS_NULL option is deactivated.
The T-SQL statement can be parsed, but causes the error at runtime.
Error of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. The CONCAT_NULL_YIELDS_NULL must be set to ON before DBCC CHECKCONSTRAINTS can be used.
This error message was introduced with SQL Server 2005.
IF OBJECT_ID(‘tempdb..t’) > 0
DROP TABLE t
CREATE TABLE t
INSERT INTO t VALUES (100);
ALTER TABLE t WITH NOCHECK ADD
CONSTRAINT CHK_t_id CHECK (id > 100);
SET CONCAT_NULL_YIELDS_NULL OFF
In the above example we try to execute DBCC CHECKCONSTRAINTS while the CONCAT_NULL_YIELDS_NULL option is deactivated. This raises the error.