mssql Table(..., autoload=true) - causes double square brackets around [[tablename]] and sp_columns stored procedure fails

Issue #880 resolved
Former user created an issue

When using mssql, if you attempt to autoload a table, it doubles up on the square brackets the table name causing the sp_columns stored procedure to fail and the following stack dump occurs:

-----code----- from sqlalchemy import * db = create_engine('mssql://sa:pwd@sqlserver/database') metadata = MetaData(db) table = Table('Table', metadata, autoload=True) -----end------

-----trace---- Traceback (most recent call last): File "mssql_test.py", line 9, in <module> users = Table('Table', metadata, autoload=True) File "c:\python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\schema.py", line 109, in call return type.call(self, name, metadata, args, *kwargs) File "c:\python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\schema.py", line 234, in init metadata._get_bind(raiseerr=True).reflecttable(self, include_columns=include_columns) File "C:\Python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 1193, in reflecttable self.dialect.reflecttable(conn, table, include_columns) File "C:\Python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\databases\mssql.py", line 597, in reflecttable cursor = connection.execute("sp_columns %s" % self.identifier_preparer.format_table(table)) File "C:\Python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 789, in execute return Connection.executorsc(self, object, multiparams, params) File "C:\Python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 799, in _execute_text self.__execute_raw(context) File "C:\Python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 864, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters0, context=context) File "C:\Python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 884, in _cursor_execute raise exceptions.DBAPIError.instance(statement, parameters, e) sqlalchemy.exceptions.DatabaseError: (DatabaseError) internal error: SQL Server message 105, severity 15, state 1, line 1: Unclosed quotation mark before the character string 'Table'. DB-Lib error message 10007, severity 5: General SQL Server error: Check messages from the SQL Server. 'sp_columns [Table]' {}

-----end-----

Note the last line that reads "sp_columns [Table]". I apologize in advance that I do not have svn setup on this machine, so I cannot run the patch diff. However, I did track down the potential source of this bug in line 596 of file /sqlalchemy-0.4.1-py2.5.egg/sqlalchemy/databases/mssql.py:

    cursor = connection.execute("sp_columns [%s](%s)" % self.identifier_preparer.format_table(table))

The square brackets are created by the format_table() function, so there is no need to add them again. I fixed this by removing the brackets around %s so it reads:

    cursor = connection.execute("sp_columns %s" % self.identifier_preparer.format_table(table))

Comments (2)

  1. Former user Account Deleted

    I did not escape the double square brackets above, so it attempted to run macros. It should read:

    sp_columns [ Table]

  2. paj

    Funny, I just noticed this last night and fixed it. If you use the latest trunk, it should work. Reopen if any probs.

  3. Log in to comment