From 8316e21e3821f678cf4c0ecbc9df5e360ddb5668 Mon Sep 17 00:00:00 2001 From: Gord Thompson Date: Fri, 4 Mar 2022 11:13:14 -0700 Subject: [PATCH] Add LongAsMax note to mssql+pyodbc dialect docs Change-Id: I4491b188bae49ac615f8691dd9b7a8a341428ce7 --- lib/sqlalchemy/dialects/mssql/pyodbc.py | 27 +++++++++++++++++++++++++ 1 file changed, 27 insertions(+) diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py index 0951f219b3..530a0a4809 100644 --- a/lib/sqlalchemy/dialects/mssql/pyodbc.py +++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py @@ -179,6 +179,33 @@ at both the pyodbc and engine levels:: isolation_level="AUTOCOMMIT" ) +Avoiding sending large string parameters as TEXT/NTEXT +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +By default, for historical reasons, Microsoft's ODBC drivers for SQL Server +send long string parameters (greater than 4000 SBCS characters or 2000 Unicode +characters) as TEXT/NTEXT values. TEXT and NTEXT have been deprecated for many +years and are starting to cause compatibility issues with newer versions of +SQL_Server/Azure. For example, see `this +issue `_. + +Starting with ODBC Driver 18 for SQL Server we can override the legacy +behavior and pass long strings as varchar(max)/nvarchar(max) using the +``LongAsMax=Yes`` connection string parameter:: + + connection_url = sa.engine.URL.create( + "mssql+pyodbc", + username="scott", + password="tiger", + host="mssqlserver.example.com", + database="mydb", + query={ + "driver": "ODBC Driver 18 for SQL Server", + "LongAsMax": "Yes", + }, + ) + + Pyodbc Pooling / connection close behavior ------------------------------------------ -- 2.47.2