Why the Query Designer Table Listing could be empty

  • Article

I just worked a case that took a little bit to figure out and involved some pretty deep debugging, but I figured I would get something out to explain the behavior to hopefully prevent someone else from having to go through the debugging that I just went through.

To give some background, when designing a report for Reporting Services in BIDS (Visual Studio), you may get to a point where you want to add a DataSet.  Within the DataSet Properties dialog box, there is an option for the query to use a query designer.

SNAGHTMLeba132

SNAGHTMLec11cf

This query designer is actually the Query Designer that ships with Visual Studio.  It is not actually a part of the Reporting Services Code base. This nice thing about this is, that if you encounter an issue with the Query Designer when in a Report Project, you can probably reproduce the issue outside of the Report Designer.  The Server Explorer Connections would help you expose this Query Designer Dialog when you try to make a query off of that connection.

image

When in the Query Designer, you can right click, in the upper area, and select “Add Table…”.  When you do that, you should see a listing of Tables.

image

However, for this case, the list was empty and we were using a 3rd party ODBC Driver.

image

When I first saw this, my thought was that the metadata call that we were making wasn’t returning the proper values.  From an ODBC standpoint, this call is SQLTables.  Luckily, with ODBC, we have an ODBC Trace that we can see what is coming back.

Looking at the ODBC Trace, I could actually see data coming back.  There were 5 items that came back.  Here is a sample of one (sanitized of course):

devenv 117c-1da0 EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 0C5249E0

devenv 117c-1da0 EXIT SQLGetData with return code 0 (SQL_SUCCESS) <-- TABLE_CAT
HSTMT 0C5249E0
UWORD 1
SWORD -8 <SQL_C_WCHAR>
PTR 0x09403008 <— NULL value
SQLLEN 4094
SQLLEN * 0x0012A7A4 (-1)

devenv 117c-1da0 EXIT SQLGetData with return code 0 (SQL_SUCCESS) <-- TABLE_SCHEM
HSTMT 0C5249E0
UWORD 2
SWORD -8 <SQL_C_WCHAR>
PTR 0x09403008 [ 6] "dbo"
SQLLEN 4094
SQLLEN * 0x0012A7A4 (6)

devenv 117c-1da0 EXIT SQLGetData with return code 0 (SQL_SUCCESS) <-- TABLE_NAME
HSTMT 0C5249E0
UWORD 3
SWORD -8 <SQL_C_WCHAR>
PTR 0x09403008 [ 8] "Test"
SQLLEN 4094
SQLLEN * 0x0012A7A4 (8)

devenv 117c-1da0 EXIT SQLGetData with return code 0 (SQL_SUCCESS) <-- TABLE_TYPE
HSTMT 0C5249E0
UWORD 4
SWORD -8 <SQL_C_WCHAR>
PTR 0x09403008 [ 10] "TABLE"
SQLLEN 4094
SQLLEN * 0x0012A7A4 (10)

devenv 117c-1da0 EXIT SQLGetData with return code 0 (SQL_SUCCESS) <-- REMARKS
HSTMT 0C5249E0
UWORD 5
SWORD -8 <SQL_C_WCHAR>
PTR 0x09403008
SQLLEN 4094
SQLLEN * 0x0012A7A4 (-1)

From the above, we can see that the TABLE_CAT value was empty (null).  From the documentation linked above though, this shouldn’t be a problem.  

TABLE_CAT Catalog name; NULL if not applicable to the data source. If a driver supports catalogs for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have catalogs.

The values coming back from the SQLTables call should be specific to the given Catalog that we passed in.  When passing in a Catalog, that is expected to be a search pattern for the results that come back.  We do pass in the Catalog value based on the catalog of the connection when we make the call.

The problem comes in to play when the Visual Studio Query Designer actually does another select off of the values that were returned.  That select, from a .NET perspective, is again matching the whatever values that came back with the Catalog that we have for the connection.  Because the TABLE_CAT values were empty, the Query Designer assumes it is not for this catalog, and will not add them to the list box.  This appears to be a redundant filter, especially being that the Catalog passed into the SQLTables call should have already done that.

There is no workaround to this behavior and it is present in both Visual Studio 2008 and 2010.  It is also not something I would classify as a Bug.  As it is explicit behavior based on how it was coded.

If you do run into a case where you do not get tables populated in the Query Designer list and you are using an ODBC Driver, hopefully you can use the above to at least validate if you are hitting the same behavior and can explain why it is happening.

Adam W. Saxton | Microsoft SQL Server Escalation Services
https://twitter.com/awsaxton