Forum Discussion

David0802's avatar
David0802
Frequent Visitor
2 years ago

Gateway fails with a SQL db function call

I have a PowerBI report that connects to a SQL database and pulls data from database views. When I deploy this PowerBI report, it deploys and works fine on the PowerBI service. 

  • If I then add a database function as a data source within PowerBI Designer, in the same database using the same connection string, the report still works well, sending my parameters to the function and returning the results of the function ok. 
  • When I go to publish the report however, the report is published but the data source is disconnected. If I remove the function as a datasource it works ok again.

I'm clearly having issues enabling the PowerBI Service to execute my database function, but PowerBI Designer is fine. I've reinstalled the Data Gateway (on-premise, personal mode) but without resolution. 

 

Does anyone know any special settings to use to enable database function calls via the PowerBI service? Thanks in advance. 

Need Help

2 Replies

    • David0802's avatar
      David0802
      Frequent Visitor
      2 years ago

      Thanks GilbertQ , I've tried to wrap this into a DirectQuery SQL submission rather than just the function, but still no luck. I think you might be right. I researched further today and can see a Microsoft link that doesn't allow Stored Procedures to be called, but not specifically ruling out Functions...which I think should still be allowed but perhaps only in Import mode. 

       

      I'll persevere, would be a good outcome if I can get working.