1.The scenario was:
- A stored procedure that uses Linked Server to pull record-set from another SQL Server database.
- Pull records and load this result set on a temp-table.
- For implementing the business logic and to do some calculations the query uses some aggregate functions
like SUM(), MIN(), MAX() on a few columns in that temp table.
Here if any of these aggregated column contain NULL values, it results in an obvious Warning message (not Error), i.e.: Warning: Null value is eliminated by an aggregate or other SET operation.
We could ignore this warning message, but when used in SSIS packages it causes the package to fail. So to avoid this Warning message, “SET ANSI_WARNINGS OFF” option was set at the beginning of the stored procedure.
The stored procedure compiled fine, but when we executed it, it resulted into this strange error: “Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS…”.
We observed that this error is due to setting ANSI_WARNINGS to OFF just before the linked server call.
So the workaround was to shift the “SET ANSI_WARNINGS OFF” statement just below the linked server call.
And to be on the safer side applied the “SET ANSI_WARNINGS ON” statement at the end of the stored procedure.
2.UPDATE statement conflicted with the REFERENCE constraint
When tables are referenced by Foreign Key constraints it gets difficult to UPDATE the key columns.
-- Now lets UPDATE the FK column values
UPDATE emp SET d_fk=3 WHERE d_fk=2
Error Message:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "fk_dept_d_fk".
The conflict occurred in database "tempdb", table "dbo.emp", column 'd_fk'.
The statement has been terminated.
We can disable the FK constraints by ALTER statement before the UPDATE and enable them after the UPDATE, like:
-- Disable FK Constraint
ALTER TABLE emp NOCHECK CONSTRAINT fk_dept_d_fk
-- Perform UPDATE
UPDATE emp SET d_fk=3 WHERE d_fk=2
-- Enable FK Constraint
ALTER TABLE emp WITH CHECK CHECK CONSTRAINT fk_dept_d_fk
- A stored procedure that uses Linked Server to pull record-set from another SQL Server database.
- Pull records and load this result set on a temp-table.
- For implementing the business logic and to do some calculations the query uses some aggregate functions
like SUM(), MIN(), MAX() on a few columns in that temp table.
Here if any of these aggregated column contain NULL values, it results in an obvious Warning message (not Error), i.e.: Warning: Null value is eliminated by an aggregate or other SET operation.
We could ignore this warning message, but when used in SSIS packages it causes the package to fail. So to avoid this Warning message, “SET ANSI_WARNINGS OFF” option was set at the beginning of the stored procedure.
The stored procedure compiled fine, but when we executed it, it resulted into this strange error: “Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS…”.
We observed that this error is due to setting ANSI_WARNINGS to OFF just before the linked server call.
So the workaround was to shift the “SET ANSI_WARNINGS OFF” statement just below the linked server call.
And to be on the safer side applied the “SET ANSI_WARNINGS ON” statement at the end of the stored procedure.
2.UPDATE statement conflicted with the REFERENCE constraint
When tables are referenced by Foreign Key constraints it gets difficult to UPDATE the key columns.
-- Now lets UPDATE the FK column values
UPDATE emp SET d_fk=3 WHERE d_fk=2
Error Message:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "fk_dept_d_fk".
The conflict occurred in database "tempdb", table "dbo.emp", column 'd_fk'.
The statement has been terminated.
We can disable the FK constraints by ALTER statement before the UPDATE and enable them after the UPDATE, like:
-- Disable FK Constraint
ALTER TABLE emp NOCHECK CONSTRAINT fk_dept_d_fk
-- Perform UPDATE
UPDATE emp SET d_fk=3 WHERE d_fk=2
-- Enable FK Constraint
ALTER TABLE emp WITH CHECK CHECK CONSTRAINT fk_dept_d_fk
