Microsoft SQL Server 2008: Write to the server failed

Question

I have a Django app with a worker process which does database transactions. Under the hood, I have an Azure SQL database attached to my application. The worker code looks something like this:

class Command(BaseCommand):
    def handle(self, *args, **kwargs):
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'project.settings')
        django.setup()
    consumer = Consumer()

    logging.info('Booting drive consumer')

    while True:
        messages = consumer.poll()

        for message in messages:
          ...
          DriveEvent.objects.create(event_id=response['Id'], drive_id=drive_payload['drive_id'])
          ...

After about 20 mins, the create db object calls start failing with the following error:

[drive-consumer-1]2017-07-19T03:19:08.545128400Z Traceback (most recent call last):
[drive-consumer-1]2017-07-19T03:19:08.545133000Z   File "/code/miles/management/commands/drive_consumer_worker.py", line 28, in handle
[drive-consumer-1]2017-07-19T03:19:08.545137900Z     drive_consumer.consume(json.loads(record.value))
[drive-consumer-1]2017-07-19T03:19:08.545142500Z   File "/code/miles/workers/drive_consumer.py", line 35, in consume
[drive-consumer-1]2017-07-19T03:19:08.545152200Z     self._on_create(message['calendar_id'], drive_payload, message['access_token'])
[drive-consumer-1]2017-07-19T03:19:08.545156900Z   File "/code/miles/workers/drive_consumer.py", line 46, in _on_create
[drive-consumer-1]2017-07-19T03:19:08.545161500Z     DriveEvent.objects.create(event_id=response['Id'], drive_id=drive_payload['drive_id'])
[drive-consumer-1]2017-07-19T03:19:08.545166500Z   File "/usr/local/lib/python2.7/site-packages/django/db/models/manager.py", line 85, in manager_method
[drive-consumer-1]2017-07-19T03:19:08.545171300Z     return getattr(self.get_queryset(), name)(*args, **kwargs)
[drive-consumer-1]2017-07-19T03:19:08.545175900Z   File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py", line 399, in create
[drive-consumer-1]2017-07-19T03:19:08.545180700Z     obj.save(force_insert=True, using=self.db)
[drive-consumer-1]2017-07-19T03:19:08.545185400Z   File "/usr/local/lib/python2.7/site-packages/django/db/models/base.py", line 796, in save
[drive-consumer-1]2017-07-19T03:19:08.545190200Z     force_update=force_update, update_fields=update_fields)
[drive-consumer-1]2017-07-19T03:19:08.545194800Z   File "/usr/local/lib/python2.7/site-packages/django/db/models/base.py", line 821, in save_base
[drive-consumer-1]2017-07-19T03:19:08.545211400Z     with transaction.atomic(using=using, savepoint=False):
[drive-consumer-1]2017-07-19T03:19:08.545215800Z   File "/usr/local/lib/python2.7/site-packages/django/db/transaction.py", line 184, in __enter__
[drive-consumer-1]2017-07-19T03:19:08.545220400Z     connection.set_autocommit(False, force_begin_transaction_with_broken_autocommit=True)
[drive-consumer-1]2017-07-19T03:19:08.545224900Z   File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 391, in set_autocommit
[drive-consumer-1]2017-07-19T03:19:08.545229600Z     self._set_autocommit(autocommit)
[drive-consumer-1]2017-07-19T03:19:08.545234000Z   File "/usr/local/lib/python2.7/site-packages/sql_server/pyodbc/base.py", line 453, in _set_autocommit
[drive-consumer-1]2017-07-19T03:19:08.545238600Z     self.connection.autocommit = autocommit
[drive-consumer-1]2017-07-19T03:19:08.545243100Z   File "/usr/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
[drive-consumer-1]2017-07-19T03:19:08.545247700Z     six.reraise(dj_exc_type, dj_exc_value, traceback)
[drive-consumer-1]2017-07-19T03:19:08.545252200Z   File "/usr/local/lib/python2.7/site-packages/sql_server/pyodbc/base.py", line 453, in _set_autocommit
[drive-consumer-1]2017-07-19T03:19:08.545256800Z     self.connection.autocommit = autocommit
[drive-consumer-1]2017-07-19T03:19:08.545262600Z Error: ('08S01', '[08S01] [FreeTDS][SQL Server]Write to the server failed (20006) (SQLSetConnectAttr)')

Database init (in settings.py) looks something like this:

DATABASES = {
        'default': {
            'ENGINE': 'sql_server.pyodbc',
            'NAME': os.environ.get('DB_NAME'),
            'HOST': os.environ.get('DB_HOST'),
            'PORT': '1433',
            'USER': os.environ.get('DB_USER'),
            'PASSWORD': os.environ.get('DB_PASSWORD'),
            'OPTIONS': {
                'host_is_server': True,
                'driver_supports_utf8': True,
                'extra_params': 'tds_version=7.2;'
            }
        }
    }

score -1 · Accepted Answer · answered Jul 19 '17 at 09:41

-1

Per my experience, it seems to be caused by the tds_version that the 7.2 version does not support Azure SQL Database. Please refer to my answer for the other SO thread pymssql: Connection to the database only works sometimes to change to 7.3.

Possibly, the third-party project michiya/django-pyodbc-azure on GitHub may help for you to get the more details.

Hope it helps.

answered Jul 19 '17 at 09:41

Peter Pan

23,476
4
25
43

I can't connect to Azure SQL with TDS Version 7.3: `('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')` – Saksham Gupta Jul 20 '17 at 06:01
@SakshamGupta Please add an option `driver` with the value `ODBC Driver 13 for SQL Server` in your `OPTIONS` and try again. – Peter Pan Jul 20 '17 at 08:11
Thanks, this seems to work. I had to set up ODBC Driver v13.1 using the instructions here: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server – Saksham Gupta Jul 20 '17 at 22:12

Microsoft SQL Server 2008: Write to the server failed

1 Answers1