I've added a stored procedure into an existing database which is trying to delete records in a table based on where clause criteria. When I try to execute it I get a 'DELETE failed due to setting ANSI_NULL incorrectly set' error.
I've looked at the help & it appears that I can't explicitly set this option in my SP - fine, I didn't want to mess with it anyway. I've checked the option on the database connection & have set it both on & off - stopping & restarting the server after each. To no avail though, the sql query analyzer debug gives me the same error.
I have noticed that if I open the SP in the sql query analyzer for edit, the text of my SP is changed so that a 'SET ANSI_NULL OFF' & 'GO' are added before my 'CREATE PROCEDURE'. So I've summised that at some level between the database & the SP this option is being set. But where?
Lastly, the table in question has several simple views over it. These & the table have an index column as their primary key.