topic Re: Scheduled Refresh failing immediately with COM error: "not found" in Report Server

Scheduled Refresh failing immediately with COM error: "not found"

arnaudcoel — Mon, 02 Nov 2020 13:03:39 GMT

Hi all,

We've started testing Power BI Report Server recently and are trying to configure automated refreshes. Our report is currently hosted on powerbi.microsoft.com and automated refreshes work without issues. Unfortunately, when trying to configure refreshes with the same credentials we receive the following error:

[0] -1056505856: COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource {"protocol":"tds","address":{"server":"xxx.database.windows.net","database":"xxx"},"authentication":null,"query":"IF OBJECT_ID(\u0027tempdb..#xxx\u0027) IS NOT NULL\n\tdrop table #xxx\n\nIF OBJECT_ID(\u0027tempdb..#last_info_date\u0027) IS NOT NULL\n\tdrop table #last_info_date\n\nSELECT xxx[long SQL query]} is not found..

Does anyone have any experience with this error? I am not sure why this seems to be happening.

Re: Scheduled Refresh failing immediately with COM error: "not found"

Anonymous — Mon, 02 Nov 2020 17:38:08 GMT

Hi,

Would you be able to privately share wither a .pbit created from your report, or the full query that you used below when importing the data?

Thanks

-Boreki

ThanRe: Scheduled Refresh failing immediately with COM error: "not found"

arnaudcoel — Tue, 03 Nov 2020 08:01:04 GMT

Thanks for your response @Anonymous - unfortunately, confidentiality doesn't allow me to share any concent or queries.

Is there anything else I might be able to share to pinpoint this issue?

Re: Scheduled Refresh failing immediately with COM error: "not found"

Anonymous — Tue, 03 Nov 2020 17:42:19 GMT

Looking at some details related to the query might be helpful. What is the size, any special characters, anything different about it?

You say this is running and refreshing in powerbi.com service, but just to confirm with you there were'nt any changes made in the service and then downloaded correct? The file was only edited in Power Bi Desktop For Report Server.

Re: Scheduled Refresh failing immediately with COM error: "not found"

arnaudcoel — Wed, 04 Nov 2020 08:38:13 GMT

The query is quite long. It's basically a select with 6 joins and a few clauses in the WHERE.

It's correctly refreshing on powerbi.com. We took the same PBIX and put it on the Report Server. We didn't make any edits through Report Server, only through Power BI Desktop.

Re: Scheduled Refresh failing immediately with COM error: "not found"

Anonymous — Wed, 04 Nov 2020 12:00:26 GMT

I'm getting the same error after upgraded from May 2020 release to October 2020.
Now, as a workaround, i will get a restore for the VM before the upgrade and test.

Re: Scheduled Refresh failing immediately with COM error: "not found"

MLechner — Wed, 04 Nov 2020 12:40:10 GMT

We getting the same error. We need a solution for this !!!

Re: Scheduled Refresh failing immediately with COM error: "not found"

Anonymous — Wed, 04 Nov 2020 12:43:37 GMT

Hi,

I'm having the same issue. Couple days ago i wrote Usage Statistic Report for Report Server (i was using PB Desktop May Release) and everything used to work fine (visualizations, model and automatic refresh). After upgrading PB Server to October Release automatic refresh isn't working any more. On the refresh pane there is a message: Several errors occured during data refresh. Please try again later or contact your administrator. , in details you can find this explanation:

SessionID: 5626e868-608c-4d52-94c6-610b4a08c942
[0] -1056505856: COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource {"protocol":"tds","address":{"server":"mdmdev","database":"ReportServer"},"authentication":null,"query":"SELECT \n\t\t[Path]\n      ,[Name]\n      \n      ,[Type]\n      ,U.UserName [CreatedBy]\n      ,[CreationDate]\n      ,U2.UserName [ModyfiedBy]\n\t  ,C.[ModifiedDate]\n      ,[ContentSize]\n\t  FROM [ReportServer].[dbo].[Catalog] C\n  LEFT JOIN dbo.Users U on C.[CreatedByID] = U.UserID\n  LEFT JOIN dbo.Users U2 on C.[ModifiedByID] = U2.UserID\n  WHERE Type =13\n"} is not found..
[1] -1055784828: The database operation was cancelled because of an earlier failure.

I can share either pbit file or log file or both. I also install clean version of PBi Server on separate machine and automatic refresh is still not working there. I'm able to refresh report only using PB Desktop.

Best regards,

Re: Scheduled Refresh failing immediately with COM error: "not found"

Anonymous — Wed, 04 Nov 2020 17:19:28 GMT

Thanks @Anonymous I sent a separate private message to get details about the pbit and log files.

Re: Scheduled Refresh failing immediately with COM error: "not found"

Anonymous — Wed, 04 Nov 2020 20:55:46 GMT

As i told, i tried restoring database and Power BI VM backup.
It solved the problem. But we need a real solution for this !!!

Re: Scheduled Refresh failing immediately with COM error: "not found"

arnaudcoel — Thu, 05 Nov 2020 07:05:31 GMT

We don't have a version to restore to -- we are currently trying out Power BI Report Server, so a solution would be very welcome indeed.

Re: Scheduled Refresh failing immediately with COM error: "not found"

TOK — Thu, 05 Nov 2020 10:10:14 GMT

Hi, same issue here as we updated our report server version from May release to October.

Looks like a general problem to me. Any bug fixes to be released soon?

Re: Scheduled Refresh failing immediately with COM error: "not found"

Boka — Fri, 06 Nov 2020 10:38:10 GMT

Hi,

The same problem with existing model. I found the reason and I will try to explain.

Model contain data source with Date column. For visualisation I am using matrix with date in column. I wanted to sort columns by date descending and I did it creating another query with distinct date from source, then I sort rows by Date and added custom index column starting from some big int with -1 for increment. Finally I sorted Date by Index column, created relationship with basic table and got it sorted by Date desc. After installing October 2020 scheduld refresh is failing with the same error. Failed to save modifications to the server. Error returned: 'COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, "My Select distinct date query is here" Datasource is not found.

Editing model in desktop I can do refresh. Removing query with distinct date scheduled refresh will work on server.

In general I want Power BI working as it was. And If something is working in desktop it must work on server too!

Re: Scheduled Refresh failing immediately with COM error: "not found"

Boka — Fri, 06 Nov 2020 11:14:26 GMT

Another problematic COM error I found. Just edit model from server in desktop and save it back to the server and it stop working. All visuals are with error.

Couldn't load the data for this visual
COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource {...query details...} is not found..
Please try again later or contact support. If you contact support, please provide these details.
Request ID: 0e5ab927-b117-d93c-405e-a1304a1672b8
Time: Fri Nov 06 2020 11:54:13 GMT+0100 (Central Europe Standard Time)
Service version: /powerbi/libs

ASManagedRoot_2020_11_06_09_29_36.log
2020-11-06 11:54:08.6826|INFO|12|Building Mashup connection string
2020-11-06 11:54:08.6826|ERROR|12|Data source {...query details...} not found

RSPowerBI_2020_11_06_09_29_36.log
2020-11-06 11:54:08.7608|ERROR|18|An error occurred running the query.Details: Message=Failed to execute the DAX query., HResult=0x80004005, ErrorCode=QuerySystemError, Language=en-US, ProviderErrorCode=0xC1070000, ProviderErrorMessage=[<pi>COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource {...query details...} is not found..</pi>], ProviderGenericMessage=[COM error: COM error.], ErrorSource=PowerBI, ErrorSourceOrigin=MsolapWrapper, OnPremErrorCode=, InnerErrorDetails=[Type=MsolapWrapper.MsolapWrapperException, Message=Failure encountered while executing the DAX query.]| RequestID = f7170578-0e62-8d38-48f6-8df5d71ab570 ClientSessionID = 339ed0da-fdf1-a7e0-a873-827f72df5188

First try was to create new model with the same query and just one simple visual. And it produces error on server. Something is wrong with query which works fine on May 2020 Server. What's wrong with AS Engine??

Re: Scheduled Refresh failing immediately with COM error: "not found"

ribaldo — Fri, 06 Nov 2020 12:07:54 GMT

Same Issue.

When I try to edit the query in a Direct Query report, it shows this error:

COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource {"protocol":"tds","address":{"server":"ITMIWSQLOLTP01","database":"DATACHECK_BI_Platform"},"authentication":null,"query":" ... query details... "} is not found..
Please try again later or contact support. If you contact support, please provide these details.

The only thing I was able to do was convert the report in Import Mode and upload the new report but when i try to schedule it, same error.

A solution is required immediatly.

Re: Scheduled Refresh failing immediately with COM error: "not found"

Mike_Mellis — Mon, 09 Nov 2020 15:53:10 GMT

@Anonymous responded in another thread with a similar error. His solution worked for me. I was getting the same errors above and it seems to be a result of the Desktop October 2020 update preview feature for enhanced meta data. I was able to revert to the last version (last save from May 2020), turn off the preview feature, and save. This solved my problem and scheduled refresh is working again. The link to the other thread is here: https://community.powerbi.com/t5/Report-Server/Report-was-created-with-recent-version-of-Power-BI-Desktop-that/td-p/1466731/page/2

Re: Scheduled Refresh failing immediately with COM error: "not found"

Boka — Tue, 10 Nov 2020 08:08:04 GMT

Thank you @Mike_Mellis After restoring original files from backup and downgrading desktop to May version both problems I reported here are solved.

Re: Scheduled Refresh failing immediately with COM error: "not found"

Mike_Mellis — Tue, 10 Nov 2020 16:02:35 GMT

Great - I'm glad it was useful. One thing to note though, I believe this error is a result of the preview feature enhanced metadata in the October 2020 Desktop update. You can still use October 2020 Desktop as long as you turn off the preview feature first before saving any files.

Re: Scheduled Refresh failing immediately with COM error: "not found"

blairblair — Tue, 10 Nov 2020 18:40:18 GMT

@Mike_Mellis / @Boka / @Anonymous

Disabling preview features and saving the file again is not a catch all.. It worked for one file with multiple SQL Query datasources but none of the other files I tried re-saving after disabling the feature were able to return to normal refresh behavior.

COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource {"protocol":"tds","address":{"server":"SERVER_NAME","database":"DB_NAME"},"authentication":null,"query":

Re: Scheduled Refresh failing immediately with COM error: "not found"

Mike_Mellis — Tue, 10 Nov 2020 19:18:55 GMT

For the files that continue to fail after disabling the preview feature, were they saved prior with the feature turned on? @Anonymous mentioned that the pbix file once saved cannot be reverted. I needed to find a saved copy before it was saved with the preview feature on. I use Subversion for my pbix files, so I was able to revert a couple commits to the last time I saved it with the May 2020 Desktop.