can't check if table exists on sql server 2000

[Former user]

Ubuntu + FreeTDS + SQL Server 2000

import sqlalchemy
from sqlalchemy import Table, Column, Integer, create_engine, MetaData, String
assert sqlalchemy.__version__ == '0.7.4'

engine = create_engine('mssql:///?odbc_connect=uid%3Drsa%3Bdatabase%3DContabil%3B'
    'app%3Dtestapp%3Bdriver%3D%7BFreeTDS%7D%3Bpwd%3Dmozilla%3B'
    'servername%3Dmg7684sr001')
meta = MetaData(bind=engine)

t = Table('testing_table', meta, 
    Column('id', Integer(), primary_key=True),
    Column('name', String(50))
)

t.create(checkfirst=True) # If I change to False it works

Error Output:

Traceback (most recent call last):
  File "/tmp/test_sqlalchemy.py", line 15, in <module>
    t.create(checkfirst=True)
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/schema.py", line 564, in create
    checkfirst=checkfirst)
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2234, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1904, in _run_visitor
    **kwargs).traverse_single(element)
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 86, in traverse_single
    return meth(obj, **kw)
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 75, in visit_table
    if not create_ok and not self._can_create_table(table):
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 32, in _can_create_table
    table.name, schema=table.schema)
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/base.py", line 1169, in has_table
    c = connection.execute(s)
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1405, in execute
    params)
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1538, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1646, in _execute_context
    context)
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_context
    context)
  File "/home/nosklo/.local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 330, 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) (SQLPrepare)') '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 CAST([COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME) AS NVARCHAR(max)) = ? AND [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA) = ?' ('testing_table', 'dbo')

nosklo

[Former user]

I have checked the query generated by older sqlalchemy 0.6, where it works. There's only one thing different in the query, more specfically in the WHERE clause:

sqlalchemy 0.6:

WHERE [COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME) = ? AND [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA) = ?

New sqlalchemy 0.7:

WHERE CAST([COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME) AS NVARCHAR(max)) = ? AND [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA) = ?

I think you can't use 'NVARCHAR(max)' in a CAST in sql server 2000. It raises a Incorrect Syntax when I try it on query analyzer.

[Mike Bayer]

well we're a bit screwed since recent FreeTDS/PyODBC needs the NVARCHAR thing. I'm really, really angry at how crappy FreeTDS and/or PyODBC are.

[Former user]

Ok, further research revealed that NVARCHAR(max) was added in sql server 2005. Maybe we can use NVARCHAR(8000) in sql server version < 2005 to satisfy FreeTDS weirdness?

[Mike Bayer]

can you please try this patch as soon as you possibly can, I cannot test on SQL server 2000 but I can commit this if it works in your environment:

diff -r e8180bb7180b9ea1e14fa97c6de3745596cb2c90 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Tue Dec 06 14:28:54 2011 -0500
+++ b/lib/sqlalchemy/dialects/mssql/base.py Tue Dec 06 15:09:24 2011 -0500
@@ -1157,11 +1157,17 @@
                 pass
         return self.schema_name

+    def _unicode_cast(self, column):
+        if self.server_version_info >= MS_2005_VERSION:
+            return cast(column, NVARCHAR(_warn_on_bytestring=False))
+        else:
+            return column

     def has_table(self, connection, tablename, schema=None):
         current_schema = schema or self.default_schema_name
         columns = ischema.columns
-        whereclause = cast(columns.c.table_name, NVARCHAR(_warn_on_bytestring=False))==tablename
+
+        whereclause = self._unicode_cast(columns.c.table_name)==tablename
         if current_schema:
             whereclause = sql.and_(whereclause,
                                    columns.c.table_schema==current_schema)

[Former user]

Replying to guest:

Ok, further research revealed that NVARCHAR(max) was added in sql server 2005. Maybe we can use NVARCHAR(8000) in sql server version < 2005 to satisfy FreeTDS weirdness?

er, sorry, 4000 would be the number if we go that route, 8000 is not allowed (typo)

[Former user]

Ok, your patch works on sql alchemy 2000.

[Former user]

er, I'm getting crazy. I mean, it works on sqlalchemy trunk with your patch, testing against sql server 2000

[Former user]

So it works on * Ubuntu Oneiric * freetds 0.82 * sqlalchemy tip + patch above * sql server 2000

I didn't test against the new 0.91 freetds on ubuntu precise. Don't know whether the recent FreeTDS/PyODBC changes you're mentioning will be a bother. I can test against ubuntu precise later this week and reopen this if needed.

[Mike Bayer]

I'm going to commit it as is, for the moment, since its better than before. the pyodbc/freetds issues have to do with freetds 0.91, 0.82 always worked much better for me.

[Mike Bayer]

0e256c28bc4cf2fdc512845d3869fe39563ad253

[Mike Bayer]

Removing milestone: 0.7.4 (automated comment)