I got pulled into an interest case this week that brought back some fond memories. The customer was trying to use Power Query to connect to SQL Server, but was getting errors. It was indicated that pulling in data via Power Pivot or the Excel Data Tab worked fine. The errors were the following depending on the operation they were trying.
DataSource.Error: Microsoft SQL: Invalid object name 'sys.databases'.Details: Message=Invalid object name 'sys.databases'. Number=208 Class=16
DataSource.Error: Microsoft SQL: Invalid object name 'sys.foreign_key_columns'.Invalid object name 'sys.foreign_keys'.Invalid object name 'sys.objects'.Invalid object name 'sys.schemas'.Invalid object name 'sys.objects'.Invalid object name 'sys.schemas'.Invalid object name 'sys.columns'.Invalid object name 'sys.columns'.Details: Message=Invalid object name 'sys.foreign_key_columns'.Invalid object name 'sys.foreign_keys'.Invalid object name 'sys.objects'.Invalid object name 'sys.schemas'.Invalid object name 'sys.objects'.Invalid object name 'sys.schemas'.Invalid object name 'sys.columns'.Invalid object name 'sys.columns'. Number=208 Class=16
Some of you may already know where this is leading. The issue is that they were trying to use this against a SQL 2000 database. The objects listed above are referred to as Catalog Views and were introduced in SQL 2005. So, they didn’t exist in SQL 2000. Hence the error.
Catalog Views (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms174365.aspx
These occur due to the fact that Power Query will query these views to gather metadata about the objects you are trying to reference. This could be the listing of databases, the structure of the table (including columns and data types) along with other pieces of data.
Error #1 (sys.databases):
This error popped when we tried to import data from a server and didn’t specify the database.
Error #2:
The second error is when we do specify a database. In my case we had a listing of tables and views in the navigator, but when I hovered over an object, or went to use an object I see the error.
So, what do we do?
You have a few options that will work depending on your situation. First, and probably the most important, is that you should look at upgrading your SQL Server. SQL 2000 is out of support and should be looked at to move to a more recent version. I understand that this may not be within your control though, or not even a possibility for your company. Realize though, that that should be seriously looked at.
There is an option to execute a SQL statement directly with Power Query. One way is through the UI.
This results in the following M Statement, if you look at the Advanced Editor.
let Source = Sql.Database("basestar\sql2000", "pubs", [Query="select * from employee"])in Source
One of the draw backs you face is that any manipulation you make to the Query will not take advantage of “folding” with the data source. This is because you are sending a static statement to the Database and Power Query won’t alter that as you intentionally said to execute that statement. Also, the statements are not parsed before sending the query to SQL, so make sure the statement actually runs. Another caveat is that there is no guarantee as to how often the query will be sent to SQL.
This also works with calling a Stored Procedure as well.
let Source = Sql.Database("basestar\sql2000", "pubs", [Query="exec reptq1"])in Source
You can always just create a blank query, under the “From Other Sources” drop down and add the M Syntax directly if you wanted.
While the errors were interesting, I think it would be rare for someone to still be using SQL 2000. However if you run into the error, then hopefully this will help explain why you are hitting it.
Adam W. Saxton | Microsoft SQL Server Escalation Serviceshttp://twitter.com/awsaxton