I ran into an interesting issue where the customer indicated that they were not seeing the correct number of items for a Database that had a large number of tables, however Power Pivot and the Excel Data Tab were showing the items. To illustrate this, I scripted out a database that had 2500 tables and 2500 views. Here was the result:
And, indeed, within Power Pivot we can see the full listing. Something was obviously different here. Fortunately, I know something about our underlying providers for connecting to SQL. One difference I’m aware of is that Power Query uses the Managed (.NET) SQL Provider (SqlClient) to reach out to SQL, while Power Pivot uses our native providers. And getting a table listing is just getting metadata about the Database itself. We don’t actually touch the table objects when we do that. To do this, we have to talk to the Database Server, so we can see what it is actually doing via a Profiler Trace. So, lets have a look.
Power Query
exec sp_executesql N’select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES where (TABLE_CATALOG = @Catalog or (@Catalog is null)) and (TABLE_SCHEMA = @Owner or (@Owner is null)) and (TABLE_NAME = @Name or (@Name is null)) and (TABLE_TYPE = @TableType or (@TableType is null))’,N’@Catalog nvarchar(4000),@Owner nvarchar(4000),@Name nvarchar(4000),@TableType nvarchar(4000)’,@Catalog=NULL,@Owner=NULL,@Name=NULL,@TableType=NULL <—5000 records
Power Pivot
exec [BunchOTables].[sys].sp_tables_rowset2 NULL,N’TABLE’ <— 2500 Recordsexec [BunchOTables].[sys].sp_tables_rowset2 NULL,N’VIEW’ <— 2500 Records
So, in both cases, I’m getting 5000 records, which is what I expected, but Power Query is only showing 4000. Of note, the customer had opened the case right before we released the March 2014 update to Power Query. So, I asked him to update to the latest release which is 2.10.3598.81. You can always get the latest version of Power Query from the following link.
Microsoft Power Query for Excelhttps://www.microsoft.com/en-us/download/details.aspx?id=39379
After installing that build, the issue was corrected.
There was an issue with the paging algorithm when retrieving the list of Tables/Views for the Database. This was caught internally and fixed and released as part of the March Update. This was only an issue if you had a large number of tables. So, most people probably won’t notice the issue. If you do notice that your Table count doesn’t look right from what you expect, make sure you are on at least Build 2.10.3598.81 of the Power Query Add-in for Excel.
Adam W. Saxton | Microsoft SQL Server Escalation Serviceshttps://twitter.com/awsaxton