Forum Discussion
SCHEDULED REFRESH ERROR
Power BI accepts both (ServerName) and (ServerName:Database) as data source when setting up a data query from SQL Server. In Advanced Editor, there are 2 possible setups:
let
Source = Sql.Database("Server", "DB1"),
Query_Data = Source{[Schema=" Schema 1",Item="Tbl_ Schema 1_Data"]}[Data]
in
Query_Data
and
let
Source = Sql.Database("Server "),
DB1= Source{[Name=" DB1"]}[Data],
Query_Data = ClinOps{[Schema=" Schema 1",Item=" Tbl_ Schema 1_Data "]}[Data]
in
Query_Data
Both setups work exactly the same in power bi desktop i.e. no exceptions. However, the exception of IDbCommand interface will be thrown if there were more than one query and at least one of these queries used different setup to the rest of queries.
Solution
Open Advanced Editor to ensure all queries are setup using the same format.
How to open Advanced Editor:
Edit Queries --> select the query you want to modify --> Advanced Editor (small button next to Refresh Preview)
RLSmartHelper I
Mine are both defined the same way but still throw the error.
Just for fun, I deleted the report from the pbrs server, then saved it back up there. The Data Source 1 had no credentials saved so I set that up, but I cannot access Data Source 2 for the second query. I assume this is the cause of the error. Even though both are set up correctly with the credentials in the desktop, it seems they are wiped out upon deploying/publishing the report to the server.
Both data sources use the same database service account and connect to the same database. The queries are sql queries, not full table imports, if that has anything to do with it.
Any other ideas?
Thanks!!
