Forum Discussion

eferreira's avatar
eferreira
New Member
10 years ago

Use SQL Store Procedure in Power BI

I have created a basic stored procedure without parameters in SQL Server, when I use the store procedure in Power Bi Desktop the return is ok in design but when I click on Save and Close in datasourc...
nirajdubey's avatar
nirajdubey
Advocate I
10 years ago

Its a late reply, but it may help some body else, you can use openquery. Follwoing syntax will work with both import data and direct query

 


SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @parametername = ''R1''');

  • stangellapally's avatar
    stangellapally
    Frequent Visitor
    10 years ago

    I am having similar issue and tried both the solutions posted:

     

    1. tried to change from Direct query to import but the Power BI doesnt give me that option to change.

     

    2. Also tried the OPENQUERY OPTION and I am now getting the below message:

     

    Details: "Microsoft SQL: Server 'sv375002\lt1201' is not configured for DATA ACCESS."

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      stangellapallyeferreiranirajdubeyasocorro As a follow up to my previous post, i was testing this further due to another thread, and have discovered that you don't need to change any database settings or use OpenQuery. If you wrap your stored procedure in a variable you can import the data using a sproc (Doesn't work in Direct Query, only import)

       

      example:

       

      DECLARE @sqlCommand varchar(1000)

      SET @sqlCommand = 'dbo.Testproc'
      EXEC (@sqlCommand)

      • Baskarved's avatar
        Baskarved
        Regular Visitor
        10 years ago

        Small query over here,If we need to pass parameters to the procedure then how can we use this here?Could you share the syntax so that it would be helpful for beginners.

         

        Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      stangellapally 

      1. tried to change from Direct query to import but the Power BI doesnt give me that option to change.

         It is most likely because you already have a connection to a SQL source in Direct Query mode. If that is the case, I believe this gets greyed out.

       

      2. Also tried the OPENQUERY OPTION and I am now getting the below message:

       

      Details: "Microsoft SQL: Server 'sv375002\lt1201' is not configured for DATA ACCESS."

       

      You would need to enable direct access on your SQL Server. You can check what it is currently set at by running this:

      select server_id, name, is_data_access_enabled from sys.servers

      But I would definitely check with your DB team (if you aren't) before switching anything.

      To enable you would execute EXEC sp_serveroption '<Your ServerName>', 'DATA ACCESS', TRUE

       

      After enabling that feature you will be able to successfully execute the OpenQuery.

      As noted, be sure you understand what you are enabling and what it is doing before executing anything.

  • ZhiqiangZhou's avatar
    ZhiqiangZhou
    Microsoft Employee
    10 years ago

    nirajdubey Thank you. I succeeded in using the SP by OpenQuery as you introduced.

  • Zak2815's avatar
    Zak2815
    Regular Visitor
    9 years ago

    nirajdubey, I can't for the life of me get this to work for Direct Query mode:

     

    SELECT *
    FROM OPENQUERY ([server name],
    'EXEC dbname.dbo.spname @parametername = ''R1''');

     

    I've tried each of these below:

     

    SELECT *
    FROM OPENQUERY ([server name],
    'EXEC dbname.dbo.spname @AsOfDate = ''GETDATE()''');

     

    SELECT *
    FROM OPENQUERY ([server name],
    'EXEC dbname.dbo.spname @AsOfDate =     ''03-16-2017 23:59:59'' ');

     

    SELECT *
    FROM OPENQUERY ([server name],
    'EXEC dbname.dbo.spname @AsOfDate = CONVERT(DATETIME,''03-16-2017 23:59:59'',101) ');

     

    None seem to work.  The times I can get a preview loaded, there's always an error on the "Apply".

     

    Please help if anyone has succeeded in calling a Stored Proceedure in Direct Import Mode.