'unable To Obtain A List Of Tables From The Datasource' Error From Excel

Hi,
This is a very detailed question, I hope this is the best forum to address it. It is more related to general ODBC access, and less to SQL Server data access.

Calling all ODBC experts!

I am a developer using an ODBC toolkit to connect to my companies metadata management product, which in turn communicates to SQL Server, Oracle, DB2 and other databases. The ODBC toolkit we are using is very old, and has been very stable in accessing data with clients such as Access, Excel, Visio and others. It is a read-only ODBC driver that does not support a lot of advanced query and data manipulation features, such as catalog.

I'm using Visual Studio 2005 to debug the ODBC calls that Excel is making when attempting to load data through the External Data Wizard. The Excel version I am using is 2003. The method that I am using to attempt to load data is with 'Data | Import External Data | Import Data'. When that dialog appears, I select 'New Source...' then 'ODBC DSN'. I then select my DSN and click Next.

At this point, I get back the error 'unable to obtain a list of tables from the datasource'.

What is frustrating is, if I do 'Data | New Database Query' and use MSQuery to load the data, everything works fine.

As I mentioned I am using VC2005 and debugging, I believe the problem has to do with the capabilities of our driver and the columns that Excel is binding to return data. In this call, Excel is binding two columns - Table Qualifer (

TABLE_QUALIFIER (1)) and Remarks (TABLE_REMARKS(5)). Our driver does not support qualifiers, so we return a NULL for that value:




case TABLE_QUALIFIER:

fSqlTypeIn = SQL_CHAR;


rgbValueIn = NULL;

cbValueIn = SQL_NULL_DATA;

Excel makes the SQLTables() call, which successfully returns the entire list of tables from our server product. It then attempts to SQLBind the two columns I mentioned above. The first call to SQLFetch returns with SQL_SUCCESS but the ODBCGetData call fills the QUALFIER column with a NULL. So obviously, Excel doesn't like this value and then ends up quiting and displaying the message above (I think....) This is confirmed by looking at the ODBC trace calls:




EXCEL 1500-1b18 ENTER SQLTablesW
HSTMT 01F120E0
WCHAR * 0x4DE3B000 [ 1] "%"
SWORD 1
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x00000000
SWORD 0
EXCEL 1500-1b18 EXIT SQLTablesW with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
WCHAR * 0x4DE3B000 [ 1] "%"
SWORD 1
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x00000000
SWORD 0
EXCEL 1500-1b18 ENTER SQLBindCol
HSTMT 01F120E0
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD74C0
SQLLEN 256
SQLLEN * 0x01DD74BC
EXCEL 1500-1b18 EXIT SQLBindCol with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD74C0
SQLLEN 256
SQLLEN * 0x01DD74BC (-1163005939)
EXCEL 1500-1b18 ENTER SQLBindCol
HSTMT 01F120E0
UWORD 5
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD75C8
SQLLEN 510
SQLLEN * 0x01DD75C4
EXCEL 1500-1b18 EXIT SQLBindCol with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
UWORD 5
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD75C8
SQLLEN 510
SQLLEN * 0x01DD75C4 (-1163005939)
EXCEL 1500-1b18 ENTER SQLFetch
HSTMT 01F120E0
EXCEL 1500-1b18 EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
EXCEL 1500-1b18 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 01F120E0
SQLSMALLINT 1
SQLWCHAR * 0x0013531C (NYI)
SQLINTEGER * 0x00134F0C
SQLWCHAR * 0x00134F1C (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x00134F04
EXCEL 1500-1b18 EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
SQLSMALLINT 3
SQLHANDLE 01F120E0
SQLSMALLINT 1
SQLWCHAR * 0x0013531C (NYI)
SQLINTEGER * 0x00134F0C
SQLWCHAR * 0x00134F1C (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x00134F04

So I began to examine the GetInfo calls. There are probably 100 or so that Excel makes. Anything related to qualifiers or cataloging seems to indicate (according to MSDN) that we do not support it. So my thinking was, if Excel properly identifies that we don't support qualifers, why does it attempt to bind the column? Is this a flaw in Excel, or are there some other GetInfo properties that I am not properly setting?

The attributes I am setting (that seem related) are:

SQL_QUALIFIER_LOCATION = 0
SQL_QUALIFIER_USAGE = 0
SQL_QUALIFER_TERM = ""
SQL_CATALOG_LOCATION = 0
SQL_CATALOG_NAME = "N"
SQL_CATALOG_TERM=""
SQL_CATALOG_USAGE=0

Looking at ODBC trace logs, the only GetInfo values that Excel is quering is SQL_QUALIFIER_LOCATION, SQL_QUALIFIER_TERM and SQL_QUALIFIER_USAGE. According to the MSDN documentation, an application is supposed to query SQL_CATALOG_NAME to determine if catalogs are supported. I know it is not, because I don't see it in the ODBC trace log, or my breakpoints in VC2005 are never hit.

I realize this is a HUGE question. I hope that I came across clear and that my question is understandable. I guess what it comes down to is - (maybe some Excel engineers can answer this one) - is this method of loading data into Excel require a Table Qualifier? If not, how can I configure my settings so that it asks for Table Name (as MsQuery does) instead?

Thank you so much for your time and consideration!