topic Re: Refreshing data - The command has been canceled.. The exception was raised by the IDbCommand interfa in Report Server

Refreshing data - The command has been canceled.. The exception was raised by the IDbCommand interfa

RobThrive — Tue, 08 Oct 2019 08:39:49 GMT

There appears to be lots of older posts about this problem but I feel that most of them are out of date or not the right answer.

I'm using Power BI Report Server released September 2019, and PBI RS Desktop to match.

The report sources it's data from SQL Server (on an azure VM).

In PBI Desktop I can quickly and easily refresh the data to get the latest. But once the report is uploaded to the report server it keeps failing to refresh. If I manually hit the refresh button it does nothing (a circle Icon for 2-3 seconds only) and when I tried to setup a schedule refesh I get the erro message seen here:

The command has been canceled.. The exception was raised by the IDbCommand interface.

There are multiple lines of this and I assume that represents each table/dataset that needs refreshing.

edit: UPDATE-

Looking in the RS logs I came across this, but I have already gone into the data source tab on the report (under manage) and added relevent details for accessing that SQL server.

2019-10-08 09:35:05.6352|ERROR|80|Error Processing Data Model Refresh: 
SessionId: d3de9365-95d2-4bb9-bbe9-6b0308777e91, Status: Error Failed Data Refresh, 
Exception Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. 
Error returned: 'Credentials are required to connect to the SQL source. (Source at <SQL-SERVER-NAME>;<db nAME>.). 
The exception was raised by the IDbCommand interface.
The database operation was cancelled because of an earlier failure.

I've tried 2 logins (one SQL login and one windows login, both have access to the data).

I should add that all data is sourced using "import" and nothing uses "directquery". I go to "Advanced" and put in the exact SQL query I need to run.

Any help would be really appreciated, thanks

Re: Refreshing data - The command has been canceled.. The exception was raised by the IDbCommand interfa

RobThrive — Tue, 08 Oct 2019 09:27:22 GMT

Found a (the) solution to my own problem.

Open the PBIX file in PBI desktop application
Go to "edit queries"
Go to Data Source Settings button (5 from left on home menu ribbon)
In here, I had multiple data sources all to the same location, but this I mean I had "ServerName;table", "servername;table", "SERVERname;Table" I needed to select change source, and make sure they all had the same spelling. This moved them all into one source!
Hit the down arrow under refresh preview button and select refresh all
You will probably then get some "need permissions" or need to confirm the login details to the data source. correct as needed.
Save and apply your changes then upload your report

Note

I did notice in one case when I uploaded and overwrote a report on the report server, that in the data source tab (under manage) it still showed an old spelling of the data source. To fix this I simply deleted the file off the server and re-uploaded one more time.

Set the data source credentials, set the schedule refresh, and then it was all running perfectly.

Hope this helps some people. I've seen lots of similar postings to my own query but not seen this answer anywhere yet.

Re: Refreshing data - The command has been canceled.. The exception was raised by the IDbCommand interfa

SimonSeez — Wed, 08 Jul 2020 14:02:28 GMT

Thank you for this.

I had a similar problem and it solved it.