On-Prem SQL Server Error: "You can't schedule refresh for this dataset..."

[BZ_AF]

Hi there

I am having issues with getting a scheduled refresh on a on-prem SQL Server through a gateway.

It is clear that from this link that SQL Server is supported by the Power BI Gateway On-Prem so it should work. However, when I publish the dataset, this is the error when I try to configure the gateway on Power BI Service:

Query code is here:

let
    Source = Sql.Database("LAPTOP-NGLJT7V1", "SBODemoSG"),
    dbo_ORDR = Source{[Schema="dbo",Item="ORDR"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_ORDR,{"DocNum", "DocEntry", "DocDate", "CardName", "DocCur", "DocTotal", "DocTotalFC"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"DocDate", type date}, {"DocTotal", Currency.Type}, {"DocTotalFC", Currency.Type}})
in
    #"Changed Type"

Interestingly, one workaround I managed to do was copy / paste the code into a new Dataflow. The dataflow works and I can configure the Power BI Gateway easily. Would like to know if this is a known bug or what I can do to avoid using a dataflow and just use a dataset instead.

Proof of dataflow working:

[BZ_AF]

Hi there

The links are not helpful because they are still for SAP HANA. You are providing me SAP HANA info for a SQL Server issue.

Here is my configuration under data source settings:

There is no credential information in this case.

[BZ_AF]

Hi there

The links provided are not helpful because I am not connecting to a SAP HANA database.

I am connecting to a SQL Server database, SAP HANA is a different SAP Product which I am not connecting to.

[v-henryk-mstf]

Hi @BZ_AF ,

For such data sources, you need to configure the corresponding credential information in the data source setting.

Refer to the related documentation and similar solutions.

Manage your data source - SAP HANA - Power BI | Microsoft Docs

Power BI service dataset refresh failure: SAP HANA... - Microsoft Power BI Community

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.