none
Specifying a SQL native query command

    Question

  • I enjoyed this:

    let

        var = "select * from ttirou003475" ,

        Quelle = Sql.Database("itlog-pc", "baandb", [Query= var])

    in

        Quelle

    I also could insert   a longer sql script into editor window with copy paste.

    Works fine also , wow !

    Q: max length of var  ?

    Current I use ADODB.Connection and fill  excec variable like this: strexec = strexec & strline

    Could you give  an example complete with powerquery from scratch :

    1) read a longer sql scriptfile   into powerquery variable

    2) exec powerquery per variable as parameter

    You work hard, very good results

    I'm excited

    Burkhard

    Wednesday, November 13, 2013 7:48 PM

Answers

  • I believe you are correct.

    Here are two ways:

    1) Use Text.FromBinary instead of using the From Text source directly (there is no way to access this function directly from the UI at this moment). In your case this would be:

    let
       Q1 = Text.FromBinary(File.Contents("j:\myscript.txt"))
       , Q2 = Sql.Database("itlog-pc","AdventureWorks",[Query=Q1])
    in
        Q2

    2) Use List.Accumulate to build up the string line by line. The method used here may not be efficient if done on a large file.

    let
         Q1 = Table.FromColumns({Lines.FromBinary(File.Contents("j:\myscript.txt"),null,null,1252)})

        , var = List.Accumulate(Q1[Column1], "", (state, current) => state & "#(000d)#(000a)" & current)
         , Q2 = Sql.Database("itlog-pc","AdventureWorks",[Query=var])
    in
        Q2

    Note: #(000d)#(000a) represents /r/n (newline in Windows).

    Thursday, November 14, 2013 10:46 PM

All replies

  • The maximum length of a native query is 500,000 characters.

    1) You can use the "From Text" data source and drill down into the first cell of the table to get a text value. You can then use that query as the parameter for the native query.

    For example, if you opened the text file in Query1 and then drilled down into the table cell, you could use it in your SQL query with the following formula:

    = Sql.Database("myserver", "mydatabase", [Query=Query1])

    Query1 would probably look like:

    let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\myfile"),null,null,1252)}),
        Column1 = Source{0}[Column1]
    in
        Column1

    2) Do you have an example?

    Thanks.

    -Alejandro (MSFT)

    Thursday, November 14, 2013 1:31 AM
  • Hi Alejandro,
    Thank you for your hint , 

    am I right ?

    I recognized :

    Step a)  get all script lines into cells of column1

    step b) select cell 1 in  column 1

    therefore it works with a single line script as follows
    myscript.txt :
    select top 10 * FROM AdventureWorks.HumanResources.Employee

    let
    a)    Q1 = Table.FromColumns({Lines.FromBinary(File.Contents("j:\myscript.txt"),null,null,1252)})
    b)    , var = Q1{0}[Column1]  
        , Q2 = Sql.Database("itlog-pc","AdventureWorks",[Query=var])
    in
        Q2

    Question: how to concatenate all cells from column1 (until eof /empty cell ) into var ?

    my sample :

    /* --------------------- */

    select top 10 *

    FROM AdventureWorks.HumanResources.Employee

    /* ------------------- */

    Best Regards

    Burkhard

    Thursday, November 14, 2013 9:02 PM
  • I believe you are correct.

    Here are two ways:

    1) Use Text.FromBinary instead of using the From Text source directly (there is no way to access this function directly from the UI at this moment). In your case this would be:

    let
       Q1 = Text.FromBinary(File.Contents("j:\myscript.txt"))
       , Q2 = Sql.Database("itlog-pc","AdventureWorks",[Query=Q1])
    in
        Q2

    2) Use List.Accumulate to build up the string line by line. The method used here may not be efficient if done on a large file.

    let
         Q1 = Table.FromColumns({Lines.FromBinary(File.Contents("j:\myscript.txt"),null,null,1252)})

        , var = List.Accumulate(Q1[Column1], "", (state, current) => state & "#(000d)#(000a)" & current)
         , Q2 = Sql.Database("itlog-pc","AdventureWorks",[Query=var])
    in
        Q2

    Note: #(000d)#(000a) represents /r/n (newline in Windows).

    Thursday, November 14, 2013 10:46 PM
  •     Hi Alejandro ,
        Thank you very much !
        Both versions work perfect with longer scripts over several pages.
        With your help my problems are resolved.
        I can use same versions as in vba/vbscript before.
        I execute my native sql code
         by  txtfile ,
         by code in excel column  ,
         by stored procedure call.
     
        At last I would ask for your recommendation.
        I often deal with hierachies / trees i.e. bom explosion, cpm network calculation
        in ERP systems.
        Recursive explode of  bom is managed by SQL Server CTE and iterative explode of
        smaller bom is managed in excel sheets by mix of vba and sql.
        I'm looking for a  powerquery parent/child sample.
        Is there a adequate thread ?
        
        Kind regards
        Burkhard
    Friday, November 15, 2013 8:33 PM
  • I am personally unaware of any threads which cover this.  Sorry.

    -Alejandro (MSFT)

    Wednesday, November 20, 2013 12:36 AM