Updating SQL Server table with GEOGRAPHY column in Django

Question

I have a Django 1.8.3 application that I'm connecting to SQL Server Enterprise 2012 as one of the databases. I am using django-pyodbc-azure==1.8.3.0 and my connection looks like this:

'sql_server': {
    'ENGINE': 'sql_server.pyodbc',
    'NAME': 'mydb',
    'USER': 'myuser',
    'PASSWORD': 'mypass',
    'HOST': '173.1.1.1',
    'PORT': 1433,
    'OPTIONS': {
        'host_is_server': True,
    }
},

I have a column in a table in the database that was created as such:

ALTER TABLE mytable ADD geo AS GEOGRAPHY::Point (
    latitude
    ,longitude
    ,4326
    ) persisted;
CREATE spatial INDEX SI_mytable__geo ON mytable (geo)

When trying to update any column in this table via the Django admin, I receive this error:

('42000', "[42000] [FreeTDS][SQL Server]UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. (1934) (SQLExecDirectW)")

My database currently has SET options like this:

SELECT 
    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

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
0   0   0   0   0   0   0

I only receive this error in production and not when running in development on my laptop. I understand that I need to correctly update the SET options, per the error and this answer: https://stackoverflow.com/a/9235638/1753891

How can I tell django-pyodbc-azure to send the correct SET options prior to updating this table?
Or do I need to update the options on the database itself and, if so, are they any potential pitfalls to watch out for if I change these SET options?

What database are you using in development, SQL Server as well? I don't think spatial indexes are support for SQL Server over pyodbc, if GeoDjango is any indicator: https://docs.djangoproject.com/en/1.8/ref/contrib/gis/db-api/ — FlipperPA, Aug 18 '15 at 01:32
@FlipperPA Yes, SQL Server 2012 in development as well - which is confusing. — duffn, Aug 18 '15 at 01:34
I think it's the SET options on your session that matter, not the database settings. Do you have the option to use stored procedures here (so you can control the SET options within the context of the proc explicitly)? — Ben Thul, Aug 18 '15 at 03:44
@BenThul I do have the ability to create stored procedures on the server, but this is happening when using a plain Django model and the admin. — duffn, Aug 18 '15 at 11:31

score 1 · Accepted Answer · answered Aug 18 '15 at 12:59

I ending up changing the SET options in the model's save method and this is working for now.

class MyModel(models.Model):
    # Model fields here
    def save(self, force_insert=False, force_update=False, using=None,
         update_fields=None):

        cursor = connections['sql_server'].cursor()
        cursor.execute("""SET ANSI_NULLS ON
        SET QUOTED_IDENTIFIER ON
        SET CONCAT_NULL_YIELDS_NULL ON
        SET ANSI_WARNINGS ON
        SET ANSI_PADDING ON""")
        cursor.close()

        super(MyModel, self).save(force_insert, force_update, using, update_fields)

Updating SQL Server table with GEOGRAPHY column in Django

1 Answers1