Error during insert to linked server via view.

[xnick]

Software versions

• Django: 5.0.2
• mssql-django: 1.4
• python: 3.12
• SQL Server: 16.0.4095.4-1 (Ubuntu)
• OS: Ubuntu (different VM from the SQL Server)

Database Connection Settings

DATABASES = {
        'default': {
            'ENGINE'  : 'mssql',
            'NAME'    : 'redacted',
            'USER'    : 'redacted',
            'PASSWORD': 'redacted',
            'HOST'    : 'redacted',
            'PORT'    : '1433',
            'OPTIONS' : {
                'driver'         : 'ODBC Driver 18 for SQL Server',
                "extra_params"   : "TrustServerCertificate=yes;MARS_Connection=Yes",
                'unicode_results': True,
            }
        }
    }

---

Hello!

I have a weird use case, where to transfer data between 2 applications, both using mssql-server, I have created a linked server from the django one to the other (Atlantis).

The data transfer is one-way only. So Atlantis only reads data from the table, while django only deletes and writes to it.

The table exists on Atlantis' DB, while for django's DB it's defined as a view to a linked server's table:

CREATE VIEW [dbo].[ATLANTIS_ORDER_TRANSFER_LINKED_VIEW]
AS
    SELECT [ATL_OT].[key]
      ,[ATL_OT].[Number]
      ,[ATL_OT].[Linenum]
      ,[ATL_OT].[FtrDate]
      ,[ATL_OT].[CusCode]
      ,[ATL_OT].[IteCode]
      ,[ATL_OT].[Price]
      ,[ATL_OT].[Qty]
      ,[ATL_OT].[Percentage]
      ,[ATL_OT].[CUSTOMERNAME]
  FROM [IP_ADDR].[DB_NAME].[dbo].[ATLANTIS_ORDER_TRANSFER] as [ATL_OT] 
GO

For the django app, I use this model:

class AtlantisOrderTransfer(models.Model):
    key = models.AutoField(primary_key=True)
    number = models.IntegerField(db_column='Number')
    linenum = models.CharField(db_column='Linenum', max_length=25, db_collation='GREEK_CI_AS')
    ftrdate = models.DateTimeField(db_column='FtrDate')
    cuscode = models.CharField(db_column='CusCode', max_length=25, db_collation='GREEK_CI_AS')
    itecode = models.CharField(db_column='IteCode', max_length=255, db_collation='GREEK_CI_AS')
    price = models.DecimalField(db_column='Price', max_digits=18, decimal_places=2)
    qty = models.IntegerField(db_column='Qty')
    percentage = models.DecimalField(db_column='Percentage', max_digits=18, decimal_places=2)
    customername = models.CharField(db_column='CUSTOMERNAME', max_length=255, db_collation='GREEK_CI_AS', blank=True,
                                    null=True)

    class Meta:
        managed = False  # VIEW!!!
        db_table = 'ATLANTIS_ORDER_TRANSFER_LINKED_VIEW'

Django deletes all the contents in the table, and proceeds to fill it again with the next set of orders.

    @classmethod
    def build_index(cls, order: Order):
        index = 1
        table = []
        for orderitem in OrderItem.objects.filter(order_id=order, inactive=False).all():
            table.append(cls.__index_item(orderitem, index))
            index += 1

        [row.delete() for row in cls.objects.all()] 
        [row.save() for row in table]
        return table

For various reasons this was the least painful way to implement this data transfer.

---

Since mssql-django 1.4 row.save() fails with:

A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement. 

Here's the query that django 5.0.2 tries to perform with mssql-django 1.4:
FAILS

{'sql': 'INSERT INTO [ATLANTIS_ORDER_TRANSFER_LINKED_VIEW] ([Number], [Linenum], [FtrDate], [CusCode], [IteCode], [Price], [Qty], [Percentage], [CUSTOMERNAME]) OUTPUT INSERTED.[key] VALUES (79664, 1, 2024-02-12 22:00:00+00:00, 1000000, 799.0004.00.00, 87.10, 1, 0.00, ΔΙΑΦΟΡΟΙ)', 'time': '0.000'}

And here's with django 4.2.9 and mssql-django 1.3:
WORKS

{'sql': 'SET NOCOUNT ON INSERT INTO [ATLANTIS_ORDER_TRANSFER_LINKED_VIEW] ([Number], [Linenum], [FtrDate], [CusCode], [IteCode], [Price], [Qty], [Percentage], [CUSTOMERNAME]) VALUES (79664, 1, 2024-02-13 00:00:00, 1000000, 799.0004.00.00, 87.10, 1, 0.00, ΔΙΑΦΟΡΟΙ); SELECT CAST(SCOPE_IDENTITY() AS bigint)', 'time': '0.032'}

I'm sure writing into a view of a linked server's table won't win me any DB architecture awards...

So how should I handle my edge-case?
Maybe there's an option to get the old style of insert back that I don't know about?
As a workaround, I guess I'll write a manual insert for now.

Thank you.

[xnick]

From my limited understanding, it looks like it's the by-default enabling of return_rows_bulk_insert that could be the culprit, but setting it to False doesn't have any effect on the query produced. Neither with the code I posted above, nor when using cls.objects.bulk_create(table).

[mShan0]

Hi @xnick, I think you're correct. We've changed the default value back in the dev branch. Can you try it out there and see if that resolves your issue?

[xnick]

Hello, thanks for the suggestion.

I can confirm that the dev build works fine. Just out of curiosity, I enabled return_rows_bulk_insert and the problem appeared again.

Does return_rows_bulk_insert provide a significant speedup? Is it worth it enabling it and overriding this snippet with a custom insert, I wonder?

Thank you!

[mShan0]

I don't believe there's a performance benefit. The dev build will be released next month with the default value changed back.