Power BI Report Server frequently getting error when loading report
I often encounter an error while loading reports on Power BI Report Server. Although it sometimes resolves on its own, I have noticed that the following URL generates an error 500: /powerbi/api/explore/reports/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxx/modelsAndExploration. I also added related logs in below.
I'm using version May 2023, I had same problem with previous versions but I could fix it by modifying some scripts on report servers' database, unfortunately now I can't find that solution in internet.
Can you please provide guidance on how to fix this error?
2023-07-25 15:16:41.2911|INFO|59|Received request GET /api/explore/reports/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxx/modelsAndExploration| RequestID = e56c0d34-6bf1-00e6-2982-d429a746d07d ClientSessionID = b2655a34-c9d1-8ac7-eac4-1174b1846876
2023-07-25 15:16:41.3067|ERROR|59|Unhandled error in the Web API|GET https://example.com/powerbi/api/explore/reports/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxx/modelsAndExploration?p... - Response Unknown| RequestID = e56c0d34-6bf1-00e6-2982-d429a746d07d ClientSessionID = b2655a34-c9d1-8ac7-eac4-1174b1846876 System.IO.IOException: There is no data in the database for @CatalogItemId=e6e41eba-a41c-425d-a7d0-33a53a22e578;@ContentType=PowerBIReportDefinition
at Microsoft.ReportingServices.CatalogAccess.Streams.VarbinaryReadableStream.InitializeDatabaseAccess()
at Microsoft.ReportingServices.CatalogAccess.Streams.VarbinaryReadableStreamFactory.CreateExtendedContentReadableStream(Guid catalogItemId, ExtendedContentType contentType)
at Microsoft.ReportingServices.CatalogAccess.CatalogItemAccessor.GetExtendedContentReadable(Guid catalogItemId, ExtendedContentType contentType)
at Microsoft.PowerBI.ReportServer.WebApi.Catalog.CatalogService.<GetPowerBIReportDefinitionStream>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.ReportServer.WebApi.Catalog.CatalogService.<GetPbixComponentsAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.ReportServer.WebApi.PbiApi.PbiApiController.<GetModelsAndExplorationAsync>d__22.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Threading.Tasks.TaskHelpersExtensions.<CastToObject>d__1`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__6.MoveNext()
Solution
I fixed my problem by following instruction in this article: https://community.fabric.microsoft.com/t5/Report-Server/There-is-no-data-in-the-database-for-catalogitemid/td-p/1716998
ROOT CAUSE
===========
Change to GetCatalogExtendedContentData stored procedure via fix in May 2020
ICM 179612428
User Story 324871: Incident 179612428 : CSS – PBIRS/PBIX Reports display a blank page when executed during the SaveToCatalog Step during Schedule/OnDemand Refresh (3/13)
WORKAROUND
=============
To revert back to the previous version of the GetCatalogExtendedContentData stored procedure, you can replace this:
SELECT
DATALENGTH([Content]) AS ContentLength,
[Content]
FROM
[CatalogItemExtendedContent] WITH (NOWAIT) -- DevNote: Using NOWAIT here because for large models the row might be locked for long durations. Fail fast and let the client retry.
WHERE
[ItemID] = @CatalogItemID AND ContentType = @ContentType
With this:
SELECT
DATALENGTH([Content]) AS ContentLength,
[Content]
FROM
--[CatalogItemExtendedContent] WITH (NOWAIT) -- DevNote: Using NOWAIT here because for large models the row might be locked for long durations. Fail fast and let the client retry.
[CatalogItemExtendedContent] WITH (READPAST) -- Pre-May 2020 code
WHERE
[ItemID] = @CatalogItemID AND ContentType = @ContentType
- Microsoft Power BI REST API PowerBINotAuthorizedException
- Filtering values on one visual affects filtering on second visual
- Displaying a Fixed Timeline in a Power BI Line and Clustered Column Chart with Slicers
- Integrate PowerBI report using 'embed for customer approach' to angular application which use OKTA to authenticate
- Why do fields still show in PowerBI when I have them filtered in the model?
- How to sort a line chart X-axis by a different column than displayed
- Row Comparison in PowerBI
- PowerBI Slicer showing single value from group of values
- Sorting Months in a Power BI Visual by Fiscal Year and Month
- Power BI report of excel data source
- PowerBI REST API get reports
- Reference another table when getting data from Python in Power BI
- Power BI - Powerpoint Export is Not even Showing
- Is it possible to schedule refresh in Power BI with Windows Task Schedule?
- PowerBi measure where the total is based on the legend and adjusts total for selected slicer
- DAX problem - The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value
- How do I create a bar plot histogram of a parameter in power BI
- How to show a colum total in a Power BI matrix visualisation that has implicit columns based on values?
- power Bi count the number of completed tasks per bucket
- How to display the absolute difference between two values on a dumbbell plot
- How would you get category share across time in PBI?
- How to update a column's values in Power BI M Language
- power query design for performance of queries (using SOAP connector) for Power BI/Excel
- Filtering for all records where date is less than slicer value in Power BI
- Calculate Activity based on logon-logoff times
- Power BI Metadata using Azure Data Factory (ADF)
- How to keep DAX Studio connection credentials between Power BI and Excel unchanged?
- Power BI Custom Visual - Visual Tab Not Visible in Formatting Pane
- dax measure percentage of cost
- What's the difference between DAX and Power Query (or M)?