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.
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.
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.
However, for this case, the list was empty and we were using a 3rd party ODBC Driver.
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
. 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):
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.
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.