search

andialbrecht / crunchyfrog

Head over to RunSQLRun, the successor of CrunchyFrog
http://runsqlrun.org
GNU General Public License v3.0
5 stars 2 forks source link

[patch]Cannot connect to SQL2005 - information_schema case sensitive #80

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
In sql 2005, the system tables are hidden and cannot be directly queried,
so the INITIAL_SQL query throws:

Invalid object name 'information_schema.columns'.

Simple fix though - the data is available through built in views, the names
of which are upper case and case sensitive. Patch attached works on sql2000
and sql2005

Original issue reported on code.google.com by darren.w...@gmail.com on 9 Dec 2009 at 10:09

Attachments:

GoogleCodeExporter commented 9 years ago 
This issue was closed by revision a1ffddd1f6.

Original comment by albrecht.andi on 10 Dec 2009 at 5:13

GoogleCodeExporter commented 9 years ago

Original comment by albrecht.andi on 10 Dec 2009 at 5:14

GoogleCodeExporter commented 9 years ago 
Hi, with the revision of albrecht.andi it didn't work for me. 

I had to do it like this:

INITIAL_SQL = """SELECT t.TABLE_CATALOG as table_catalog,
t.TABLE_NAME as table_name, c.COLUMN_NAME as column_name, t.TABLE_TYPE as 
table_type
FROM INFORMATION_SCHEMA.COLUMNS c,
    INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME = c.TABLE_NAME
      AND t.TABLE_CATALOG = c.TABLE_CATALOG
ORDER BY c.TABLE_CATALOG,
         c.TABLE_NAME,
         c.ORDINAL_POSITION
"""

Original comment by heed...@gmail.com on 29 Apr 2011 at 4:50

GoogleCodeExporter commented 9 years ago 
So, the column names are all uppercase too? Which version of SQL Server are you 
using?

Original comment by albrecht.andi on 29 Apr 2011 at 7:35