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 sys.conversation_endpoints.

Here is the SQL:

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, 
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:

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?

  • What happens if you just remove ANSI_NULLS , from the SET command? – Philᵀᴹ May 9 '13 at 13:26
  • @phil - Just to clarify, you mean removing just the ANSI_NULLS, leaving the other in place? I assume because you suspect the .Net SqlClient Data Provider might cause different behaviour? – RoKa May 9 '13 at 14:27
  • 1
    SSMS defaults it to ON, .net provider to OFF. User profiler to see if the SET is being ignored or overridden msdn.microsoft.com/en-us/library/ms188048.aspx – gbn May 9 '13 at 15:11
  • @gbn : Using profiler, I found that the script is being run as is above, overriding with the SET. What I have found that is interesting though, is that my table has a primary xml index. – RoKa May 9 '13 at 15:31
  • 1
    "line 8 of proc on server SomeSillySrv" < if the code is in a stored procedure, note that ANSI_NULLS and QUOTED_IDENTIFIER settings are stored with the procedure definition. You can't override them with SET statements in the procedure. Script the procedure to see the settings in effect when it was created, or use OBJECTPROPERTYEX. – Paul White 9 Sep 26 '13 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.