Forum Discussion
Calling Stored Procedure for Big Query as Power BI Data Source
Hello!
I have been struggling to get a Stored Procedure I created in BigQuery to load in as a dataset in PBI.
I have created the required ODBC connections, and I am able to run simple direct SQL statements like "SELECT * FROM '[projectid].[server].[storedprocedurename]." But when I try to "CALL '[projectid].[server].[storedprocedurename]'();" I keep getting syntax errors that I cannot verify (because of M?):
I have read elsewhere that it has something to do with Enable Folding? But I've tried this from another forum post and it gives another syntax error: https://community.fabric.microsoft.com/t5/Desktop/Call-Stored-Procedure-from-BigQuery/td-p/1698474
What am I missing here? Or, is this just not possible?
I have also tried using the stored procedure as a SQL Statement directly but still run into syntax errors.
Any help is greatly appreciated!
Yes, you can use stored procedures in Power BI Import mode by entering a native SQL query (e.g., EXEC MyProcedure), but only if the procedure returns a single result set and has no output parameters. For more stability, it's better to use a SQL view instead.
7 Replies
- mh2587
Super User
Power BI does support executing stored procedures in DirectQuery mode, but there are certain limitations. For instance, stored procedures that return multiple result sets or include output parameters are not supported.
I have not been trying to use DirectQuery mode: I have just been trying to connect to the datasource via Get Data > Google BigQuery > Advanced Options > SQL Statement.
After successful connection, I am able to select Import.My Stored Procedure as is currently creates a number of temp tables; would this be an issue?
- mh2587
Yes, you can use stored procedures in Power BI Import mode by entering a native SQL query (e.g., EXEC MyProcedure), but only if the procedure returns a single result set and has no output parameters. For more stability, it's better to use a SQL view instead.
Hi Anonymous ,
I wanted to follow up on our previous suggestions regarding the issue you are facing currently. We would like to hear back from you to ensure we can assist you further. If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.Hi Anonymous ,
Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.