none
PowerQuery TimeOut connect to Microsoft SQL Server RRS feed

  • Question

  • 10 min timeout, Power Query connect to Microsoft SQL Server. I'm need 30 min
    • Edited by Rainur Monday, October 2, 2017 6:42 AM
    Monday, October 2, 2017 6:19 AM

Answers

  • Hi Rainur. Please try removing the space between # and duration. It should be:

    = Sql.Database("192.168.2.7", "nefco",[Query="select * from MyTable",CommandTimeout = #duration(0,1,0,0)])

    Also, note that once you're no longer getting the Expression.Error, you'll want to replace the "select * from MyTable" query with the actual SQL statement you want to execute.

    Ehren

    Monday, October 9, 2017 5:22 PM
    Owner

All replies

  • Hi Rainur. Please see the CommandTimeout option, as explained here:

    https://blog.crossjoin.co.uk/2014/09/29/advanced-options-for-loading-data-from-sql-server-with-power-query/

    Ehren

    Monday, October 2, 2017 4:50 PM
    Owner
  • = Sql.Database("192.168.2.7", "nefco",[Query="UserQuery"],[CommandTimeout=#duration(0,1,0,0)])

    [Exspression.Error] Arguments 4 was passed to a function which expects values between 2 and 3.

    It gives an error. How?


    • Edited by Rainur Tuesday, October 3, 2017 9:56 AM
    Tuesday, October 3, 2017 9:48 AM
  • Hi Rainur. Try this:

    = Sql.Database("192.168.2.7", "nefco", [Query="UserQuery", CommandTimeout=#duration(0,1,0,0)])

    The issue with what you pasted above is that you're providing two separate options records, instead of a single options record with multiple fields inside it.

    Ehren

    Tuesday, October 3, 2017 4:52 PM
    Owner
  • Then do not quite understand how to make all I need in one query
    Wednesday, October 4, 2017 6:57 AM
  • Did you try the example I provided above?

    Ehren

    Wednesday, October 4, 2017 4:33 PM
    Owner
  • Yes. But did not understand how to combine both a query and a timeout. In the database to create something forbidden. and to work with all the tables too hard, that's why is a custom query
    Thursday, October 5, 2017 5:55 AM
  • The example I provided above combines both a query and a timeout, via the options record.

    Here it is, formatted on multiple lines for clarity:

    [
        Query = "select * from MyTable",
        CommandTimeout=#duration(0,1,0,0)
    ]

    Hope that helps. You only need to pass the Query option if you have a very specific SQL script you want to run. If that's not the case, I'd recommend connecting to the table(s) and using the Power Query UI to do all your filtering and shaping. Behind the scenes, PQ will generate SQL for you, based on the actions you perform.

    Ehren

    Thursday, October 5, 2017 4:18 PM
    Owner
  • [Exspression.Error] Expected token Literal

    What am I doing wrong?

    Friday, October 6, 2017 10:52 AM
  • There's an error in one of your M formulas. Please click Advanced Editor in the PQ ribbon and copy/paste your formulas here.

    Ehren

    Friday, October 6, 2017 4:46 PM
    Owner
  • = Sql.Database("192.168.2.7", "nefco",[Query="select * from MyTable",CommandTimeout = # duration (0,1,0,0)])
    Monday, October 9, 2017 5:36 AM
  • Hi Rainur. Please try removing the space between # and duration. It should be:

    = Sql.Database("192.168.2.7", "nefco",[Query="select * from MyTable",CommandTimeout = #duration(0,1,0,0)])

    Also, note that once you're no longer getting the Expression.Error, you'll want to replace the "select * from MyTable" query with the actual SQL statement you want to execute.

    Ehren

    Monday, October 9, 2017 5:22 PM
    Owner
  • Appreciate it. Sorry I did not respond earlier

    Thank you.


    • Edited by Rainur Thursday, October 26, 2017 12:41 PM
    Thursday, October 26, 2017 12:40 PM