I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called
msw_timer_task, based on a filtered join to
Here is the SQL:
SET ANSI_NULLS , ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF /* SELECT name, is_ansi_nulls_on, is_ansi_padding_on, is_ansi_warnings_on, is_arithabort_on, is_concat_null_yields_null_on, is_numeric_roundabort_on, is_quoted_identifier_on FROM sys.databases where name = db_name() */ print 'clean up unattended time tasks' delete from msw_timer_task where timer_task_id in ( select m.timer_task_id from msw_timer_task m left join sys.conversation_endpoints c on c.conversation_handle = m.conversation_handle where c.conversation_handle is null and m.state = 'WAITING')
This script errors with the following error message:
ERROR: Severity 16, state 1 error number 1934 line 8 of proc on server SomeSillySrv: DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
I have done a lot of Googling on the issue, and have played around a lot with the set statement in an attempt to get it to work. In testing, in SSMS, the script did not produce an error, at least not before shipping it.
I am not sure if I am missing something, but it seems that the settings are not properly set when run through the app.
Am I missing something in the script?