Thank you, Mike - very much appreciated! Just to be clear, pyodbc is not a driver, it’s a ODBC-compliant DBAPI, right? I separately downloaded a driver for SQL Server from Microsoft, which pyodbc makes use of.
Do you suggest that changing pyodbc to another SQL Server DPAPI would solve the problem? If so, can you recommend another DBAPI for SQL Server? Or do you think that the problem is caused by Microsoft’s driver? I’m pretty sure SQL Server works fine when accessed using .NET, otherwise the Internet would be full of complaints regarding not being able to insert +2000 characters in a varchar(max). Best regards Nicolas On Tue, 13 Oct 2020 at 22.22, Mike Bayer <mike...@zzzcomputing.com> wrote: > > > On Tue, Oct 13, 2020, at 10:50 AM, Nicolas Lykke Iversen wrote: > > Hi SQLAlchemy, > > *System information:* > > - Mac OS X v. 10.15.7 > - Python v. 3.8.5 > - SQLAlchemy v. 1.3.19 > - MS SQL Server 2017 and 2019 (both Enterprise and Docker images e.g. > mcr.microsoft.com/mssql/server:2019-latest) > > *Problem*: > I have an issue with inserting strings with a length greater than 2000 > into columns with a datatype of Text, VARCHAR, or NVARCHAR using MS SQL > Server 2017 and 2019. > > I've checked the MS SQL Server and it creates it column properly with e.g. > a datatype of varchar(max)for Text and VARCHAR, which should be able to > store strings with a size up to 2 GB according to Microsoft documentation. > > Furthermore, I've tried using other collations, but I need _SC (supplementary > character) support for my applications, so I cannot drop it, and adding _UTF8 > (UTF-8) doesn't solve the problem either. > > Why am I not allowed to store strings with a size greater than 2000? > > > I'm not really sure, this has to do with a behavior of SQL Server and/or > your ODBC driver. a google search finds fairly scant results but there is > a long discussion regarding this error here: > https://www.sqlservercentral.com/forums/topic/collation-error-when-adding-distributer > > > > > > > > > And why is SQLAlchemy displaying that error message, when trying to insert > plain ASCII text ("AAAA...")? > > > SQLAlchemy runs SQL statements using a method called "cursor.execute()", > which is a feature of the DBAPI (database driver) in use. As your error > message indicates you're using the pyodbc driver, this method is documented > here: > https://github.com/mkleehammer/pyodbc/wiki/Cursor#executesql-parameters > > The DBAPI execute() method, and most of the other DBAPI methods, can throw > exceptions if something goes wrong. SQLAlchemy has the policy that if it > encounters such an exception when it calls cursor.execute(), it wraps it in > a SQLAlchemy-specific version of that exception (named the same) and then > throws it. > > In other words you're using a driver called pyodbc that's creating this > error. SQLAlchemy just propagates it for you but otherwise has nothing to > do with how it's produced. > > > > I would really appreciate some guidance on how to solve this problem. What > could be causing it? > > *Error*: > sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', > "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot > convert to text/ntext or collate to 'Latin1_General_100_CI_AS_SC' because > these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types > varchar(max), nvarchar(max) or a collation which does not have the _SC or > _UTF8 flags. (4189) (SQLParamData)") > [SQL: INSERT INTO msg (content) OUTPUT inserted.id VALUES (?)] > [parameters: > ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA > ... (1703 characters truncated) ... > AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA',)] > (Background on this error at: http://sqlalche.me/e/13/f405) > > > *Program (POC)*: > import logging > import sqlalchemy > from sqlalchemy import Column, Text, Integer, NVARCHAR, VARCHAR > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import sessionmaker > > logging.root.setLevel(logging.DEBUG) > > Base = declarative_base() > > > class Msg(Base): > __tablename__ = 'msg' > > id = Column(Integer, primary_key=True, autoincrement=True) > content = Column(VARCHAR, nullable=False) > > > user = 'sa' > pwd = 'P@ssw0rd' > host = 'localhost' > port = 1433 > db = 'test' > > logging.info('started.') > > engine = sqlalchemy.create_engine( > f'mssql+pyodbc://{user}:{pwd}@ > {host}:{port}/{db}?driver=ODBC+Driver+17+for+SQL+Server', > pool_size=5, > max_overflow=10, > pool_pre_ping=True, > isolation_level='READ_UNCOMMITTED', > pool_recycle=900, > echo=False, > connect_args={'connect_timeout': 10}) > > Base.metadata.create_all(engine) > > Session = sessionmaker(bind=engine, autoflush=False, autocommit=False) > session = Session() > > for i in range(10000): > try: > msg = Msg(content='A' * i) > session.add(msg) > session.commit() > except Exception as exc: > logging.exception(f'fail: {i=}: {exc}') > break > else: > logging.info(f'success: {i=}') > > logging.info('done.') > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/ca549391-4360-480e-8c58-06577f6d92dan%40googlegroups.com > <https://groups.google.com/d/msgid/sqlalchemy/ca549391-4360-480e-8c58-06577f6d92dan%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/Kk6DkPNWlR4/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/b4a135ef-1c1e-476c-a3f0-60326dca76ea%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/b4a135ef-1c1e-476c-a3f0-60326dca76ea%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAE%2B5CJABrO_459MHtDePMXah8mb67TFvQB8rUwgVgk6%2By4v-ow%40mail.gmail.com.
