Finding "varchar(max) and ntext are incompatible = to operator" when comparison string is long using SQLAlchemy/FreeTDS.

Question

Before I state the problem, let me state the settings used surrounding this problem:

Compile-time settings (established with the "configure" script)
                        Version: freetds v0.95.81
         freetds.conf directory: /etc
 MS db-lib source compatibility: yes
    Sybase binary compatibility: yes
                  Thread safety: yes
                  iconv library: yes
                    TDS version: 4.2
                          iODBC: no
                       unixodbc: yes
          SSPI "trusted" logins: no
                       Kerberos: yes
                        OpenSSL: no
                         GnuTLS: yes

SQLAlchemy Version 1.1.13
Connection to MS SQL Server 2008.

Problem description:

I have a table in the database that looks like this:

DNA Sequence
id (int, primary key)
NTSequence (varchar(max))

In the SQLAlchemy model the table gets represented like so:

class DNAPtsSeq(Base):
    id = Column("DNAPartsSequenceID", Integer, primary_key=True)
    nt_seq = Column("NTSequence", VARCHAR('max'))

I connect to the DB like so:

prod_eoi_engine = create_engine(
"mssql+pyodbc://someuser:somepw@somehost:1234/EOI?driver=FreeTDS")

This is how I connect to the db:

def set_con(engine):
    # Create a db session outside of the ORM that we can roll back
    connection = engine.connect()
    # bind db.session to that connection, and start a nested transaction
    session = Session(bind=connection)
    session.autoflush = True
    return session

When I use the following SQLAlchemy query to query the database:

dna_pts_seq = db_s.query(DNAPtsSeq).filter(DNAPtsSeq.nt_seq ==
                                           nt_seq).first()

I am encountering a problem under select circumstances.

If nt_seq is longer than 2000 bp, I am getting the following error:

ProgrammingError}(pyodbc.ProgrammingError) ('42000', '[42000] [FreeTDS][SQL 
Server]The data types varchar(max) and ntext are incompatible in the equal 
to operator. (402)

If nt_seq is up to 2000 bp, everything is fine.

I don't know why I am getting this error and need help to troubleshoot it. I have been trying to find solutions but have not found an answer so help would greatly be appreciated.

The following threads do not provide answers even though the topic is similar:

Django SQLServer [FreeTDS][SQL Server]The data types nvarchar(max) and ntext are incompatible in the equal to operator

OLEDB comparison problem nvarchar against ntext (SQLServer 2005)

Note: I am trying to not use direct SQL statements in python code, otherwise casting may have been a solution.

The data types text and varchar are incompatible in the equal to operator

Note: My database field is already declared as VARCHAR(MAX) and not text.

score 1 · Answer 1 · answered Apr 21 '18 at 00:53

If you want to avoid CAST statements in raw SQL and to stick to the ORM, you're going to have change the structure of your SQL Server columns. This is a restriction of SQL Server, nothing further down the stack.

You can do one of two things.

Change the VARCHAR(MAX) column to NTEXT:

ALTER TABLE table_name ALTER COLUMN varchar_column NTEXT;

or, change both columns to NVARCHAR(MAX):

ALTER TABLE table_name ALTER COLUMN ntext_column NVARCHAR(MAX);
ALTER TABLE table_name ALTER COLUMN varchar_column NVARCHAR(MAX);

Before doing any of this, I'd read up on the side effects of either decision. However, I would suggest that in this case, dropping to raw SQL from the ORM is the best decision you could make. In my experience, ORMs work best when the source database is built by the ORM; trying to shoehorn an ORM on a database that wasn't designed with an ORM in mind always has compromises at best, and unintended consequences at worst.

Good luck!

Finding "varchar(max) and ntext are incompatible = to operator" when comparison string is long using SQLAlchemy/FreeTDS.

1 Answers1