Version 0.8.1 can't reflect a table in mssql server 2000

[Clovis Fabricio]

This is accessing a SQL Server 2000 from Linux using FreeTDS + Unixodbc + pyodbc.

Using version 0.8.0 it works works fine. But when trying the new 0.8.1 version got a sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '42000 FreeTDSServerStatement(s) could not be prepared. (8180) (SQLExecDirectW)')

By examining both generated queries, it seems the new version generates a CAST(? AS NVARCHAR(max)) and that doesn't work on SQL SERVER 2000. Please provide a way to disable this cast when sql version is 8 or lower.

Full code and traceback output is below:

Code:

import sqlalchemy
import urllib

table_name = 'lancamentos_passivo'
con_string = 'servername=mg7684sr300;uid=rsa;pwd=some_pwd;database=Passivo;driver={FreeTDS};app=testapp'
con_string = urllib.quote_plus(con_string)

engine = sqlalchemy.create_engine('mssql:///?odbc_connect=' + con_string, echo=True)
meta = sqlalchemy.MetaData(bind=engine)
tb = sqlalchemy.Table(table_name, meta, autoload=True)

Version rel_0_8_1 output:

2013-06-06 11:51:29,925 INFO sqlalchemy.engine.base.Engine SELECT user_name()
2013-06-06 11:51:29,925 INFO sqlalchemy.engine.base.Engine ()
2013-06-06 11:51:29,927 INFO sqlalchemy.engine.base.Engine 
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE name = ?
            AND type = 'S'

2013-06-06 11:51:29,927 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2013-06-06 11:51:29,936 INFO sqlalchemy.engine.base.Engine SELECT [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA), [COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME), [COLUMNS_1](COLUMNS_1).[COLUMN_NAME](COLUMN_NAME), [COLUMNS_1](COLUMNS_1).[IS_NULLABLE](IS_NULLABLE), [COLUMNS_1](COLUMNS_1).[DATA_TYPE](DATA_TYPE), [COLUMNS_1](COLUMNS_1).[ORDINAL_POSITION](ORDINAL_POSITION), [COLUMNS_1](COLUMNS_1).[CHARACTER_MAXIMUM_LENGTH](CHARACTER_MAXIMUM_LENGTH), [COLUMNS_1](COLUMNS_1).[NUMERIC_PRECISION](NUMERIC_PRECISION), [COLUMNS_1](COLUMNS_1).[NUMERIC_SCALE](NUMERIC_SCALE), [COLUMNS_1](COLUMNS_1).[COLUMN_DEFAULT](COLUMN_DEFAULT), [COLUMNS_1](COLUMNS_1).[COLLATION_NAME](COLLATION_NAME) 
FROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[COLUMNS](COLUMNS) AS [COLUMNS_1](COLUMNS_1) 
WHERE [COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME) = CAST(? AS NVARCHAR(max)) AND [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA) = CAST(? AS NVARCHAR(max)) ORDER BY [COLUMNS_1](COLUMNS_1).[ORDINAL_POSITION](ORDINAL_POSITION)
2013-06-06 11:51:29,936 INFO sqlalchemy.engine.base.Engine (u'lancamentos_passivo', u'dbo')
2013-06-06 11:51:29,953 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "/tmp/teste.py", line 10, in <module>
    tb = sqlalchemy.Table(table_name, meta, autoload=True)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/schema.py", line 332, in __new__
    table._init(name, metadata, *args, **kw)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/schema.py", line 396, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/schema.py", line 424, in _autoload
    self, include_columns, exclude_columns
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1595, in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1118, in run_callable
    return callable_(self, *args, **kwargs)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 262, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 397, in reflecttable
    for col_d in self.get_columns(table_name, schema, **tblkw):
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 254, in get_columns
    **kw)
  File "<string>", line 1, in <lambda>
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 49, in cache
    ret = fn(self, con, *args, **kw)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/base.py", line 1050, in wrap
    tablename, dbname, owner, schema, **kw)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/base.py", line 1059, in _switch_db
    return fn(*arg, **kw)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/base.py", line 1310, in get_columns
    c = connection.execute(s)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 163, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/home/c036337/.local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000](42000) [FreeTDS](FreeTDS)[Server](SQL)Statement(s) could not be prepared. (8180) (SQLExecDirectW)') 'SELECT [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA), [COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME), [COLUMNS_1](COLUMNS_1).[COLUMN_NAME](COLUMN_NAME), [COLUMNS_1](COLUMNS_1).[IS_NULLABLE](IS_NULLABLE), [COLUMNS_1](COLUMNS_1).[DATA_TYPE](DATA_TYPE), [COLUMNS_1](COLUMNS_1).[ORDINAL_POSITION](ORDINAL_POSITION), [COLUMNS_1](COLUMNS_1).[CHARACTER_MAXIMUM_LENGTH](CHARACTER_MAXIMUM_LENGTH), [COLUMNS_1](COLUMNS_1).[NUMERIC_PRECISION](NUMERIC_PRECISION), [COLUMNS_1](COLUMNS_1).[NUMERIC_SCALE](NUMERIC_SCALE), [COLUMNS_1](COLUMNS_1).[COLUMN_DEFAULT](COLUMN_DEFAULT), [COLUMNS_1](COLUMNS_1).[COLLATION_NAME](COLLATION_NAME) \nFROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[COLUMNS](COLUMNS) AS [COLUMNS_1](COLUMNS_1) \nWHERE [COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME) = CAST(? AS NVARCHAR(max)) AND [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA) = CAST(? AS NVARCHAR(max)) ORDER BY [COLUMNS_1](COLUMNS_1).[ORDINAL_POSITION](ORDINAL_POSITION)' (u'lancamentos_passivo', u'dbo')

Version rel_0_8_0 output:

2013-06-06 11:50:11,313 INFO sqlalchemy.engine.base.Engine SELECT user_name()
2013-06-06 11:50:11,313 INFO sqlalchemy.engine.base.Engine ()
2013-06-06 11:50:11,316 INFO sqlalchemy.engine.base.Engine 
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE name = ?
            AND type = 'S'

2013-06-06 11:50:11,316 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2013-06-06 11:50:11,331 INFO sqlalchemy.engine.base.Engine SELECT [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA), [COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME), [COLUMNS_1](COLUMNS_1).[COLUMN_NAME](COLUMN_NAME), [COLUMNS_1](COLUMNS_1).[IS_NULLABLE](IS_NULLABLE), [COLUMNS_1](COLUMNS_1).[DATA_TYPE](DATA_TYPE), [COLUMNS_1](COLUMNS_1).[ORDINAL_POSITION](ORDINAL_POSITION), [COLUMNS_1](COLUMNS_1).[CHARACTER_MAXIMUM_LENGTH](CHARACTER_MAXIMUM_LENGTH), [COLUMNS_1](COLUMNS_1).[NUMERIC_PRECISION](NUMERIC_PRECISION), [COLUMNS_1](COLUMNS_1).[NUMERIC_SCALE](NUMERIC_SCALE), [COLUMNS_1](COLUMNS_1).[COLUMN_DEFAULT](COLUMN_DEFAULT), [COLUMNS_1](COLUMNS_1).[COLLATION_NAME](COLLATION_NAME) 
FROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[COLUMNS](COLUMNS) AS [COLUMNS_1](COLUMNS_1) 
WHERE [COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME) = ? AND [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA) = ? ORDER BY [COLUMNS_1](COLUMNS_1).[ORDINAL_POSITION](ORDINAL_POSITION)
2013-06-06 11:50:11,331 INFO sqlalchemy.engine.base.Engine (u'lancamentos_passivo', u'dbo')
2013-06-06 11:50:11,405 INFO sqlalchemy.engine.base.Engine sp_columns @table_name = 'lancamentos_passivo', @table_owner = 'dbo'
2013-06-06 11:50:11,406 INFO sqlalchemy.engine.base.Engine ()
2013-06-06 11:50:11,424 INFO sqlalchemy.engine.base.Engine SELECT [C](C).[COLUMN_NAME](COLUMN_NAME), [TABLE_CONSTRAINTS_1](TABLE_CONSTRAINTS_1).[CONSTRAINT_TYPE](CONSTRAINT_TYPE), [C](C).[CONSTRAINT_NAME](CONSTRAINT_NAME) 
FROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[KEY_COLUMN_USAGE](KEY_COLUMN_USAGE) AS [C](C), [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[TABLE_CONSTRAINTS](TABLE_CONSTRAINTS) AS [TABLE_CONSTRAINTS_1](TABLE_CONSTRAINTS_1) 
WHERE [TABLE_CONSTRAINTS_1](TABLE_CONSTRAINTS_1).[CONSTRAINT_NAME](CONSTRAINT_NAME) = [C](C).[CONSTRAINT_NAME](CONSTRAINT_NAME) AND [TABLE_CONSTRAINTS_1](TABLE_CONSTRAINTS_1).[TABLE_SCHEMA](TABLE_SCHEMA) = [C](C).[TABLE_SCHEMA](TABLE_SCHEMA) AND [C](C).[TABLE_NAME](TABLE_NAME) = ? AND [C](C).[TABLE_SCHEMA](TABLE_SCHEMA) = ?
2013-06-06 11:50:11,424 INFO sqlalchemy.engine.base.Engine (u'lancamentos_passivo', u'dbo')
2013-06-06 11:50:11,517 INFO sqlalchemy.engine.base.Engine SELECT [C](C).[COLUMN_NAME](COLUMN_NAME), [R](R).[TABLE_SCHEMA](TABLE_SCHEMA), [R](R).[TABLE_NAME](TABLE_NAME), [R](R).[COLUMN_NAME](COLUMN_NAME), [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[CONSTRAINT_NAME](CONSTRAINT_NAME), [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[MATCH_OPTION](MATCH_OPTION), [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[UPDATE_RULE](UPDATE_RULE), [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[DELETE_RULE](DELETE_RULE) 
FROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[KEY_COLUMN_USAGE](KEY_COLUMN_USAGE) AS [C](C), [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[KEY_COLUMN_USAGE](KEY_COLUMN_USAGE) AS [R](R), [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[REFERENTIAL_CONSTRAINTS](REFERENTIAL_CONSTRAINTS) AS [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1) 
WHERE [C](C).[TABLE_NAME](TABLE_NAME) = ? AND [C](C).[TABLE_SCHEMA](TABLE_SCHEMA) = ? AND [C](C).[CONSTRAINT_NAME](CONSTRAINT_NAME) = [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[CONSTRAINT_NAME](CONSTRAINT_NAME) AND [R](R).[CONSTRAINT_NAME](CONSTRAINT_NAME) = [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[UNIQUE_CONSTRAINT_NAME](UNIQUE_CONSTRAINT_NAME) AND [C](C).[ORDINAL_POSITION](ORDINAL_POSITION) = [R](R).[ORDINAL_POSITION](ORDINAL_POSITION) ORDER BY [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[CONSTRAINT_NAME](CONSTRAINT_NAME), [R](R).[ORDINAL_POSITION](ORDINAL_POSITION)
2013-06-06 11:50:11,517 INFO sqlalchemy.engine.base.Engine (u'lancamentos_passivo', u'dbo')

[Mike Bayer]

ah OK was wondering if that was going to impact some versions. a workaround to disable the cast on versions below 2005 is in 45f8ff88c920937458 (0.8) 555f30d64c23558a13bb (master)

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)