Error: Beyond SQL_ACTIVE_STATEMENTS limit

[karl-schelhammer-td]

I am running into an error while running Base.prepare(engine). The base itself is associated with 15 tables each of which having multiple columns, so it is conceivable that there are a large number of SQL statements that result from this step.

Code steps are essentially:

Base = declarative_base(cls=DeferredReflection)

class table1(Base):
    __tablename__ = "table1"
    __table_args__ = {'schema': 'schema1'}
    id = Column(DECIMAL(precision=15), primary_key=True)

class table2(Base):
    __tablename__ = "table2"
    __table_args__ = {'schema': 'schema1'}
    id = Column(DECIMAL(precision=15), primary_key=True)

...
there are 13 more tables
...

Base.prepare(engine)

And the error is:

DatabaseError: (teradata.api.DatabaseError) (0, '[HY000] [Teradata][ODBC Teradata Driver] Beyond SQL_ACTIVE_STATEMENTS limit') [SQL: 'SELECT columnname, columntype, columnlength, chartype, decimaltotaldigits, decimalfractionaldigits, columnformat, nullable, defaultvalue, idcoltype \nFROM dbc.ColumnsV \nWHERE DatabaseName=? AND TableName=?'] [parameters: ('MKTBCIVIEWS', 'bci_acct')] (Background on this error at: http://sqlalche.me/e/4xp6)

I did a bit of looking around and it looks like the error happens when the pyodbc connection isn't properly releasing result sets and the pyodbc connection eventually throws this error. See here: https://www.mathworks.com/matlabcentral/answers/385192-why-am-i-getting-the-error-beyond-sql_active_statements-limit-when-querying-from-sql-database-with.

If someone could help point me to the part of the module where the Base.prepare() step occurs I would be happy to try to dig a little deeper.

[sandan]

I believe this is the module you are looking for: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/ext/declarative/api.py#L286.

I would try to reflect the tables one by one and see if that helps: http://docs.sqlalchemy.org/en/latest/core/reflection.html.

You can turn pass echo = 'debug' to the create_engine call to see the SQL being executed along with parameters.

[FrankWangAU]

I have the same problem and the issue is on following line https://github.com/Teradata/sqlalchemy-teradata/blob/24ab0ec39f6a9d63185aad90267fc815c5a63a97/sqlalchemy_teradata/dialect.py#L120 ResultSet is not closed. Fix:

        result = connection.execute(stmt, schema=schema, table_name=table_name)
        res = result.fetchone()
        # close cursor to fix error: Beyond SQL_ACTIVE_STATEMENTS limit
        result.close()
        return res is not None