Forum Discussion
SCHEDULED REFRESH ERROR
I have searched for any resolutions regarding scheduled refresh errors but haven't read any. I am getting the following:
Credentials are required to connect to the SQL source.
The exception was raised by the IDbCommand interface.
[1] -1055784828: The database operation was cancelled because of an earlier failure.
[2] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.
I have refreshed and reentered the the credentials multiple times to no avail. This is only happening with one of my reports. All other reports are refreshing as expected.
15 Replies
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?
- houston39
Hi,
Have any passwords recently expired and or changed?
Thanks!!
Exactly the same.
Refreshing works in desktop, and in service with standard refreshing. But fail when trying to update with incremental refresh.
Hi! I am having the same error. 😞 Have you found a solution for this?
Datasource connectionstrings in PBRS are case sensative.
Make sure your data source connections in Power Bi Desktop are correct.
Thank you GuldagerHerDK for this.
Datasource connectionstrings in PBRS are case sensative.
Already checked.
I first had a report already published in PBI services, that I wanted to upgrade with the incremential refresh method.
It was working perfectly on desktop and on service before. Crashed after modifying for incremential refresh.
Works again if I turn off incremential refresh.
I checked database access directly in PBI services, and everything is fine.
I tried on another report, same issue.
smeleiAdvocate I
We've had occasional issues when we've modified the datasource configuration of existing reports.
In most cases, this was resolved by deleting and then re-deploying the report. On a few occasions even this failed with the only work-around being to re-upload the report with a slightly different name
I solved a similar issue recently with a very similar error message.
In that case, one of the data sources in the report tried to connect at the server level, then the second step connected to the specific database/table. The account we use on our server only had permissions against the specific database therefore the refresh was failing. Everything worked when opening the report in PBI Desktop, as analysts have read permissions at the server level, so the issue was not obvious to find.
Not sure if this helps in your case, good luck in fixing the issue!
Hi Anonymous
I've just had the same issue and was able to fix it and hope the same steps help you to fix your issue too:
I opened the report in Power BI Desktop and went to Power Query (edit queries) and clicked on 'Data Source Settings' in the ribbon. I clicked on 'Edit Permissions' on the source in question and noticed I'd set 'Alternate' credentials rather than Windows credentials (at the time my own credentials weren't working. I set this back to Windows credentials and refreshed everything in Power Query and then, for belts and braces, in PBIRS I deleted the report (be careful if you want to save the previous auditing etc as this will get lost if deleting) and added it back in along with the data source credentials and schedule. Voila! It refreshed successfully!
Hope this helps you too.
