Invalid collation 'SQL_Latin1_General_CP1_CS_AI' when database collation is 'SQL_Latin1_General_CP1_CI_AI'

[daarsan]

When our database is case and accent insensitive, search functionality does not work.

Software versions

• Django: 5.0
• mssql-django: 1.5
• python: 3.12
• SQL Server: 15.0.4390
• OS: Windows

The root case is the function sqlserver_replace in functions.py. In that method, there is a replace where that changes CI by CS. If the default collation is SQL_Latin1_General_CP1_CI_AI, the result is SQL_Latin1_General_CP1_CS_AI which does not exist.
The exception is as follows:

django.db.utils.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid collation 'SQL_Latin1_General_CP1_CS_AI'. (448) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

My suggestion is to replace the current line:
current_collation = default_collation.replace('_CI', '_CS')
by
current_collation = default_collation.replace('_CI', '_CS') if default_collation.endswith('_AS') else default_collation

[almost-online]

Hello
I had a related issue when a DB has no a default collation and I've got the exception like this one

  File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1982, in execute_sql
    cursor = super().execute_sql(result_type)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1547, in execute_sql
    sql, params = self.as_sql()
                  ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/mssql/compiler.py", line 666, in as_sql
    sql, params = super().as_sql()
                  ^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1954, in as_sql
    sql, params = self.compile(val)
                  ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/mssql/compiler.py", line 442, in compile
    return super().compile(node, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 542, in compile
    sql, params = vendor_impl(self, self.connection)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/mssql/functions.py", line 65, in sqlserver_replace
    current_collation = default_collation.replace('_CI', '_CS')
                        ^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'NoneType' object has no attribute 'replace'