CODEWITHSUNDEEP
sql-serverpython-3.5pymssql
alireza
alireza

Reputation: 1205

pymssql connection fails with "collation not recognized"

I try to connect to sql server 2012 db with python3(3.5.3) library pymssql(2.1.3) and this error happened:

conn = pymssql.connect(host="192.168.xxx.xxx", user="wbs", password="123@123", database="mydb")
Traceback (most recent call last):
  File "pymssql.pyx", line 635, in pymssql.connect (pymssql.c:10734)
  File "_mssql.pyx", line 1902, in _mssql.connect (_mssql.c:21821)
  File "_mssql.pyx", line 637, in _mssql.MSSQLConnection.__init__ (_mssql.c:6581)
  File "_mssql.pyx", line 1630, in _mssql.maybe_raise_MSSQLDatabaseException (_mssql.c:17524)
_mssql.MSSQLDatabaseException: (4075, b'DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (192.168.100.249:1433)\n')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "pymssql.pyx", line 641, in pymssql.connect (pymssql.c:10824)
pymssql.OperationalError: (4075, b'DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (192.168.100.249:1433)\n')

and in mssql error there is a log for it :

The USE database statement failed because the database collation %.*ls is not recognized by older client drivers. Try upgrading the client operating system or applying a service update to the database client software, or use a different collation. See SQL

Upvotes: 1

Views: 640

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123809

By default, pip installs pymssql 2.1.3 from a binary wheel (.whl) file that is statically linked to FreeTDS 0.95. Unfortunately, that older version of FreeTDS does not know how to work with some of the less-frequently-used SQL Server collations. For example, Persian_100_CI_AI is known to cause an error similar to the one you describe under FreeTDS 0.9x (ref: here).

FreeTDS 1.x has better support for those collations, so one option would be for you to install the latest stable release of FreeTDS (currently 1.00.51) and then build pymssql from the latest source on GitHub.

Alternatively, you could try using pyodbc with a current version of Microsoft's ODBC Driver for SQL Server.

Upvotes: 1

Related Questions