This is a short post where I am sharing an issue which I phased after upgrading to SQL Server 2019 for my Configuration Manager Database. After upgrade, I wasn’t able to open any report because of the following error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'CollectionInfo'. (rsErrorExecutingCommand)
The EXECUTE permission was denied on the object 'fnIsCas', database 'CM_MAN', schema 'dbo'.
Initially it seems like that there are some permissions issue, however I didn’t make any changes while upgrade and all the reports were working fine previously. And I was getting this error with all the reports, not just 1 single report
Root cause of the issue
The problem occurs because of the Scalar UDF Inlining issues with SQL Server 2019. You may follow the link to know more about it which explains in detail about the error and unexpected results which I got.
Solution
Microsoft has released KB5000642 – cumulative update 9 which resolves the issue. This KB article KB4538581 explains that cumulative update 9 or later resolves this issue.
At the time of writing this post, cumulative update 12 is the latest version available for SQL Server 2019, following is the list, any of the update can be installed (though latest one is preferred):
- SQL Server 2019 RTM Cumulative Update (CU) 12 KB5004524
- SQL Server 2019 RTM Cumulative Update (CU) 11 KB5003249
- SQL Server 2019 RTM Cumulative Update (CU) 10 KB5001090
- SQL Server 2019 RTM Cumulative Update (CU) 9 KB5000642
Download the Update
Install Cumulative Update for SQL Server 2019
- Use following link for Cumulative Update Package 12 for SQL Server 2019 – KB5004524.
- This update is also available through Windows Server Update Services (WSUS), you may also use following link to download it Microsoft Catalog Update – SQL Server 2019
Once downloaded, install SQL Serer 2019 update.
While updating it you might get following error:
The 'WmiPrvSE.exe' process is not controlled by the update wizard. You have to manually stop this process to avoid a computer restart.
For this you need to manually stop the Windows management instrument service. Once you try stopping this service, it will also stop following services:
- User Access Logging Service
- IP Helper
- SMS Agent Host
I also got another error
The 'WAPCSvc' service is not controlled by the update wizard. You have to manually stop this service to avoid a computer restart.
For this I had to stop the service Microsoft AAD Application Proxy Connector.
Once done, update went fine without any further issues
For installation you may check the logs located at C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log
When I launch the ConfigMgr report, it opened the report as expected.
