none
Getting data via SQL into Power Query RRS feed

  • Question

  • Hi
    In Power Query, how do I get data from a database?
    Below are tree examples where I get data. I am able to get the data via Power Pivot and in VBA – but how do I get it via Power Query.

    Note. I use ODBC.

    Example 1 | SQL Server | SQL
    Connection string: Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=AAAA;UID=BBBB;PWD=CCCC;APP=Microsoft Office 2003;Database=DDDD";Initial Catalog=DDDD
    SQL string: SELECT * FROM table

    Example 2 | SQL Server | Stored procedure
    Connection string: Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=AAAA;UID=BBBB;PWD=CCCC;APP=Microsoft Office 2003;Database=DDDD";Initial Catalog=DDDD
    Stored procedure call:  exec AAAA.BBBB ‘1’, ‘”HEY”

    Example 1 | Oracle | SQL
    Connection string: Provider=MSDASQL;Persist Security Info=False;Extended Properties="DSN=AAAA;UID=BBBB;PWD=CCCC;DBQ=DDDD"
    SQL string: SELECT * FROM table

    Sunday, September 8, 2013 9:00 AM

Answers

  • I see. I'm pretty sure that you could connect by going through the Power Query prompts if you knew the:

    • Type of database that you're connecting to
    • Name of the server that the database is on
    • Name of the database

    I'm pretty sure that the username and password that you're using in the ODBC connection would work for the Power Query connection.

    Could you find these things out from your IT department?

    The database is: SQL Server

    The servername is: XXX.rezidor.com

    The name of the database is: XXXAccounting

    I think what I need is the M (Power Query Formula Language) syntax for Power Query that allows me to call a stored procedure or execute an SQL string. 

    Ah, yes, I think you're right. I'm not sure how to call a stored procedure in the M language. I did a quick search in the Power Query Formula Library Specification (August 2013).pdf and Power Query Formula Language Specification (July 2013).pdf but couldn't find anything.
    Wednesday, September 11, 2013 3:13 PM

All replies

  • If you only have an answer for one of the examples, it would still be of great help. 
    Monday, September 9, 2013 7:16 AM
  • Martin,

    Just curious, what kind of database does MSDASQL refer to? Is it something that can only be accessed through ODBC?

    Tim

    Monday, September 9, 2013 2:11 PM
  • Martin,

    Just curious, what kind of database does MSDASQL refer to? Is it something that can only be accessed through ODBC?

    Tim

    Hmm...

    I don't know what the meaning of "Provider=MSDASQL" is. The connection string I posted is working in Power Pivot. "Provider=MSDASQL" is used both for connection to Oracle and SQL Server.

    As for ODBC, yes it is the only access I have to the databases. ODBC off cause has its limitations, but I don't know enough about databases to make new recommendations. 


    Monday, September 9, 2013 7:23 PM
  • I see. I'm pretty sure that you could connect by going through the Power Query prompts if you knew the:

    • Type of database that you're connecting to
    • Name of the server that the database is on
    • Name of the database

    I'm pretty sure that the username and password that you're using in the ODBC connection would work for the Power Query connection.

    Could you find these things out from your IT department?

    Tuesday, September 10, 2013 12:47 AM
  • I see. I'm pretty sure that you could connect by going through the Power Query prompts if you knew the:

    • Type of database that you're connecting to
    • Name of the server that the database is on
    • Name of the database

    I'm pretty sure that the username and password that you're using in the ODBC connection would work for the Power Query connection.

    Could you find these things out from your IT department?

    The database is: SQL Server

    The servername is: XXX.rezidor.com

    The name of the database is: XXXAccounting

    I think what I need is the M (Power Query Formula Language) syntax for Power Query that allows me to call a stored procedure or execute an SQL string. 

    Wednesday, September 11, 2013 8:40 AM
  • I see. I'm pretty sure that you could connect by going through the Power Query prompts if you knew the:

    • Type of database that you're connecting to
    • Name of the server that the database is on
    • Name of the database

    I'm pretty sure that the username and password that you're using in the ODBC connection would work for the Power Query connection.

    Could you find these things out from your IT department?

    The database is: SQL Server

    The servername is: XXX.rezidor.com

    The name of the database is: XXXAccounting

    I think what I need is the M (Power Query Formula Language) syntax for Power Query that allows me to call a stored procedure or execute an SQL string. 

    Ah, yes, I think you're right. I'm not sure how to call a stored procedure in the M language. I did a quick search in the Power Query Formula Library Specification (August 2013).pdf and Power Query Formula Language Specification (July 2013).pdf but couldn't find anything.
    Wednesday, September 11, 2013 3:13 PM
  • Ah, yes, I think you're right. I'm not sure how to call a stored procedure in the M language. I did a quick search in the Power Query Formula Library Specification (August 2013).pdf and Power Query Formula Language Specification (July 2013).pdf but couldn't find anything.

    I've read them both and I also didn't find anything.

    DEAR AWESOME POWER BI PROGRAMMERS:

    This post is a point on the wishlist for the next update :)

    Friday, September 13, 2013 7:48 AM
  • I'm having this exact same issue - I can connect fine to my SQL server via the standard Data connection in Excel and through PowerPivot's data model, but when I try to connect (entering the exact same details into the Power Query connection), I am unable to connect.  The documentation for this is scant and I can't locate anything relevant anywhere...

    When I initially try to connect, Power Query only asks me for the server name and it spits out this in the Power Query field (once I've entered my login credentials):

    = Sql.Databases("xxx.mySQLserverAddress.com")

    If I change the function to: = Sql.Database("xxx.mySQLserverAddress.com","database_name") I get a separate error, but the source credentials field now shows a secondary field for inputting the database name when I try to adjust it.

    I've honed it down to a couple of potential items: either there needs to be an additional command/function that points PQ to the right server/DB combo, or there may be an issue with the SQL Server version - I'm running a 2000 DB (very, very old), so I wonder if there may be legacy compatibility issues.  Do you know what version of SQL you're running?

    I'm positive of the server address and the DB name, because (asI mentioned before) I connect just fine through the standard data connection in Excel…so frustrating.  Hopefully this is resolved soon or at least someone points us in the right direction.

    Saturday, September 14, 2013 6:12 AM
  • I'm having this exact same issue - I can connect fine to my SQL server via the standard Data connection in Excel and through PowerPivot's data model, but when I try to connect (entering the exact same details into the Power Query connection), I am unable to connect.  The documentation for this is scant and I can't locate anything relevant anywhere...

    When I initially try to connect, Power Query only asks me for the server name and it spits out this in the Power Query field (once I've entered my login credentials):

    = Sql.Databases("xxx.mySQLserverAddress.com")

    If I change the function to: = Sql.Database("xxx.mySQLserverAddress.com","database_name") I get a separate error, but the source credentials field now shows a secondary field for inputting the database name when I try to adjust it.

    I've honed it down to a couple of potential items: either there needs to be an additional command/function that points PQ to the right server/DB combo, or there may be an issue with the SQL Server version - I'm running a 2000 DB (very, very old), so I wonder if there may be legacy compatibility issues.  Do you know what version of SQL you're running?

    I'm positive of the server address and the DB name, because (asI mentioned before) I connect just fine through the standard data connection in Excel…so frustrating.  Hopefully this is resolved soon or at least someone points us in the right direction.

    When you enter the server name and Power Query puts in the =Sql.Databases("xxx.mySQLserverAddress.com") formula, it doesn't give you a list of databases in the Navigator section on the left-hand side?

    You're running the From Database -> From SQL Server Database option right?

    I also wonder if it could have to do with you're using a domain name. I connect using the machine name which works because it's on my local network.

    Wednesday, September 18, 2013 8:54 PM
  • Yes, I connect through the From Database->From SQL Server Database option...No, the DBs do not show up on the left hand side…I've tested this against other DBs, too (SQL and mySQL) and it works like a charm with multiple DBs and/or tables showing up that way…for whatever reason, this particular server just won't connect.  

    I've also tested it on those other servers using just the domain name…still works.

    I can't connect locally because the SQL server is at a co-lo in another state.  The only things that are different between the other servers and this one are that I connect through a VPN for this server and it's running SQL Server 2000...

    I still can't get over the fact that I can connect through the Data connection (through the Data tab->SQL Server connection) from Excel (but not Power Query, however).  What I have to do is grab the table through the Data connection, then run Power Query against the file from which I pulled the information.  It's not convenient at all, and it requires multiple file refreshes to make sure the data is updated.  Plus, I'm basically doubling the size of my data set…

    EDIT:  When I try to connect through Power Query, I get the following error: 

    DataSource.Error: Microsoft SQL: Invalid object name 'sys.databases'. Details: Message=Invalid object..., Number=208, Class=16

    • Edited by Jimmy Haley Wednesday, September 18, 2013 9:10 PM
    Wednesday, September 18, 2013 9:09 PM
  • Yes, I connect through the From Database->From SQL Server Database option...No, the DBs do not show up on the left hand side…I've tested this against other DBs, too (SQL and mySQL) and it works like a charm with multiple DBs and/or tables showing up that way…for whatever reason, this particular server just won't connect.  

    I've also tested it on those other servers using just the domain name…still works.

    I can't connect locally because the SQL server is at a co-lo in another state.  The only things that are different between the other servers and this one are that I connect through a VPN for this server and it's running SQL Server 2000...

    I still can't get over the fact that I can connect through the Data connection (through the Data tab->SQL Server connection) from Excel (but not Power Query, however).  What I have to do is grab the table through the Data connection, then run Power Query against the file from which I pulled the information.  It's not convenient at all, and it requires multiple file refreshes to make sure the data is updated.  Plus, I'm basically doubling the size of my data set…

    EDIT:  When I try to connect through Power Query, I get the following error: 

    DataSource.Error: Microsoft SQL: Invalid object name 'sys.databases'. Details: Message=Invalid object..., Number=208, Class=16

    I agree with you that it probably has to do with the server being 2000. If we had a 2000 server in our farm I would try it and let you know, but we don't.
    Thursday, September 19, 2013 4:49 AM
  • I could swear it worked a few weeks agoo... but....

    When using older databases (2000 2005) and using views with references in other tables it seems to fail if I select all columns (*) in the view. When I tried to add a specific SQL SELECT statement with a restricted set of colums it started working again.

    I could not read the whole shebang and select the colums within PowerQuery any more.

    PowerPivot and regular excel data connection worked though.

    Something weird going on . I tried 2.10, 2.11 and the latest 2.12 release


    Friday, June 20, 2014 2:39 PM
  • It seems strange and I don't offhand know of anything that could explain this. But Power Query doesn't officially support any version of SQL Server older than 2008 (which is different than either PowerPivot or the Excel data tab).

    Is there a specific error message that might help identify the cause of the problem?

    Friday, June 20, 2014 3:00 PM
  • I just ran it against SQL 2005 SP4 and I was able to see the tables show up and work properly.

    Based on the error you showed with sys.databases, that makes me believe that was against SQL 2000, because sys.databases was available in SQL 2005.

    The reason that we hit issues against SQL 2000 is that Power Query will issue queries for certain Catalog Views that showed up with SQL 2005. There may be specific things that Power Query will look at, depending on your environment, that doesn't exist in SQL 2005, that was introduced in SQL 2008 and later. I just tried some basic test and had no issues with SQL 2005 and later.

    The SQL 2000 errors are expected and should not work.  SQL 2000 is an unsupported version in itself from a Database perspective.


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

    Tuesday, July 1, 2014 8:29 PM