cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Apinto64 Frequent Visitor
Frequent Visitor

Data Update on SQL Server using a PowerBI Enterprise Gateway

We define a source data to a SQL Server on a PowerBI Enterprise Gateway with sucess.

But when we try to update the data using the following source data:

let
    Source = Sql.Database("<SQL server>", "<database>", [Query="EXECUTE dbo.ExcelActividadesPrevisãoFaturação @beginDate = '2013-01-01', @endDate = '2022-12-31', @query = 5"])
in
    Source


When we try to update the data we get the follwoing error:

The following exception occurred while the managed IDbConnection interface was being used: The keyword isn't supported: version. Parameter name: keyword. A connection could not be made to the data source with the Name of 'f0f5d6f1-4059-4d39-b324-a7e98bbd9cfc'.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Data Update on SQL Server using a PowerBI Enterprise Gateway

@Apinto64 Have you tried dynamically generating the parameters in the stored procedure so you don't have to hardcode them?

For instance. Build a calling sproc that handles the hardcoded parameters or dynamically generate them in Sproc 1, and have sproc 1 call sproc 2.

 

A psuedo code example:

 

CREATE PROCEDURE dbo.RunExcelActividadesPrevisaoFaturacao

 

as

 

declare @begindate date = begindate

declare @enddate date = enddate

declare @query int = 5

 

Execute dbo.ExcelActividadesPrevisãoFaturação @begindate, @enddate, @query


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

6 REPLIES 6
ankitpatira Super Contributor
Super Contributor

Re: Data Update on SQL Server using a PowerBI Enterprise Gateway

@Apinto64 can you please provide some more information? I am guessing you're trying to update from powerbi desktop if you're using source data because you can't manually type in powerbi.com ? But if you are using powerbi desktop then why you need enterprise gateway? 

 

please provide some more information and then i will be able to help.

Apinto64 Frequent Visitor
Frequent Visitor

Re: Data Update on SQL Server using a PowerBI Enterprise Gateway

Hi

 

We tried this conection using a Personal Gateway and it worked. When we try to move it to the Enterprise Gateway it give us the error.

 

The solution was created with Power BI desktop, publish to online and the data update was made usinf Personal Gateway with sucess. When we tried to use our Enterprise gateway it didn't work.

dparkinson Regular Visitor
Regular Visitor

Re: Data Update on SQL Server using a PowerBI Enterprise Gateway

Did you get this fixed at all?  I'm getting exactly the same issue at my end.

 

Thanks.

Apinto64 Frequent Visitor
Frequent Visitor

Re: Data Update on SQL Server using a PowerBI Enterprise Gateway

No! Not yet. I think the problem is executing a SQL stored procedure with paramenters 😞

Super User
Super User

Re: Data Update on SQL Server using a PowerBI Enterprise Gateway

@Apinto64 Have you tried dynamically generating the parameters in the stored procedure so you don't have to hardcode them?

For instance. Build a calling sproc that handles the hardcoded parameters or dynamically generate them in Sproc 1, and have sproc 1 call sproc 2.

 

A psuedo code example:

 

CREATE PROCEDURE dbo.RunExcelActividadesPrevisaoFaturacao

 

as

 

declare @begindate date = begindate

declare @enddate date = enddate

declare @query int = 5

 

Execute dbo.ExcelActividadesPrevisãoFaturação @begindate, @enddate, @query


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

Apinto64 Frequent Visitor
Frequent Visitor

Re: Data Update on SQL Server using a PowerBI Enterprise Gateway

Thanks, That's a good workaround, hardcoded is limited but its a way to solve it! As you can understand I would like that the Enterprise Gateway had the same behavior as the Personal, accpting the call of a SQL stored procedure with parameters.

 

Thanks again

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)