ConfigMgr SCCM Site Database Update Error
Fix ConfigMgr SCCM Site Database Update Error. You should be aware of the latest release, SCCM 2012 SP1 cumulative update 3 (CU3), released on September 20th.
Database server configuration errors belong to issues that occur due to misconfigurations or challenges in the settings and factors of the database server. These errors can lead to linking problems, running issues, or sudden behavior of the database server.
In this post, I will explain an issue faced during Cumulative Update 3 installation.
I tried to install ConfigMgr (SCCM) 2012 SP1 CU3 in a lab environment created by somebody else. The wizard took “more” time to complete. It finished with a warning at the “Update Site Database” step.
Table of Contents
FIX ConfigMgr SCCM Site Database Update Error
The log file “cm12-sp1cu3-kb2882125-x64-enu.log” helped get the following errors. Where can you find this log-in SCCM (ConfigMgr) 2012 site server? OK, try “C:\Windows\Temp”. FIX ConfigMgr SCCM Site Database Update Error.
The EXECUTE permission was denied on the object ‘fnIsPrimary’,database ‘CM_LAB’, schema ”dbo’
Task status detail added: A SqlException occurred, Line Number: 44Error: 229 Message: The EXECUTE permission was denied on the object ‘fnIsPrimary’, database ‘CM_LAB’, schema ‘dbo’
Task ‘Perform Database Update’ completed. state is ‘warning’ LastStatus: A SqlException occurred, 44Error: 229 Message: The EXECUTE permission was denied on the object ‘fnIsPrimary’, database ‘CM_LAB’, schema ‘dbo’
setup exit status 3001 (task State: warning)
- Fixed ConfigMgr SMS_SERVICE_CONNECTOR Issue
- Fix SCCM SCEP Related Client Side Issues Part 2 | ConfigMgr | Defender | Endpoint Protection
- Best Guide to Deploy Windows 11 22H2 using SCCM Task Sequence | ConfigMgr
- Best Method to Manage Bitlocker Using SCCM | ConfigMgr
Yes, the errors themselves indicate that this has to do with SQL permissions. So, I checked whether my account has all the permissions needed for the SQL DB update. FIX ConfigMgr SCCM Site Database Update Error.
The SysAdmin server role was missing, so I added it to my account. Now, how do I manually update the SCCM / ConfigMgr DB? ConfigMgr SCCM Site Database Update Error.
Okay, how do I update CU3 again? I’d like to reapply CU3 for the Site database update, which failed earlier. No! You can update the site database manually with the update.sql file.
This file is located in the following location: “Program FilesMicrosoft Configuration ManagerhotfixKB2882125update.sql.” ConfigMgr SCCM Site Database Update Error?
When you try to run a update.sql file from a remote SQL server, you may get the following error: The dll files are missing from the SQL server. In that case, you must edit the SQL file to point to the ddl file’s UNC path (on the primary server).
Msg 6502, Level 16, State 7, Line 2
CREATE ASSEMBLY failed because it could not read from the physical file ‘[[SMS_ROOT]]\bin\x64\MessageHandlerService.dll’: 50(failed to retrieve text for this error. Reason: 15105).
Msg 6528, Level 16, State 1, Procedure fnCompressData, Line 10
Assembly ‘MessageHandlerService’ was not found in the SQL catalog of database ‘CM_LAB’.
Msg 6528, Level 16, State 1, Procedure fnConvertBase64StringToBinary, Line 10
Assembly ‘MessageHandlerService’ was not found in the SQL catalog of database ‘CM_LAB’
We are on WhatsApp now. To get the latest step-by-step guides, news, and updates, Join our Channel. Click here – HTMD WhatsApp.
Author
Anoop C Nair is Microsoft MVP from 2015 onwards for consecutive 10 years! He is a Workplace Solution Architect with more than 22+ years of experience in Workplace technologies. He is Blogger, Speaker, and Local User Group Community leader. His main focus is on Device Management technologies like SCCM and Intune. He writes about technologies like Intune, SCCM, Windows, Cloud PC, Windows, Entra, Microsoft Security, Career etc.
I encountered the same issue. Thank you, very helpful.
StaffGlad to know
Most of update.sql ran, but I got the CREATE ASSEMBLY error. The SCCM database is on a remote server, so I did as you recommended and changed the SMS_ROOT path to a UNC path in update.sql, but received the following Access Denied message in the script results:
…
Msg 6501, Level 16, State 7, Line 2
CREATE ASSEMBLY failed because it could not open the physical file “\\xxxxmsrv\c$\Program Files\Microsoft Configuration Manager\bin\X64\messagehandlerservice.dll”: 5(Access is denied.).
Msg 6528, Level 16, State 1, Procedure fnCompressData, Line 10
Assembly ‘MessageHandlerService’ was not found in the SQL catalog of database ‘CM_XXX’.
…
I’m able to log on to the sql server using the same credentials under which update.sql script was run, and I’m able to manually access the file via UNC. Any ideas?
Staff@HeyAdmin – I hope, you did run as administrator? Check whether you’ve remote execute permission on the primary server etcc.
Yes, I did. The solution was to copy mwssagehandlerservice.dll to c:\temp on the remote sql server and modify update.sql to look local versus looking remove via unc. Thanks for all your posts anoop. You’ve saved me a lot of time. Appreciate your contributions to the community!
StaffThank you for sharing the solution. I hope this’ll help the community.
Thanks Anoop and Google! had this happen to me last night!
Much appreciated!
I don’t have access right to SQL database and have to ask the SQL administrator run the update.sql for me after modifying the path for messagehandlerservice.dll. I got emailed that the script has been successfully run but how do I check if the site databases actually updated?
Hi All,
environment: SCCM 2012R2 / SQL 2008R2
Just follow all the steps to apply CU4 but when it comes to updating SQL i’m getting following errors: Could someone please advise re solving the problem?
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘k’.
Msg 207, Level 16, State 1, Procedure spAddRecoveryTrackingRecord, Line 41
Invalid column name ‘TransportType’.
Msg 207, Level 16, State 1, Procedure spAddRecoveryTrackingRecord, Line 46
Invalid column name ‘TransportType’.
Msg 207, Level 16, State 1, Procedure spAddRecoveryTrackingRecord, Line 49
Invalid column name ‘TransportType’.
(0 row(s) affected)
Msg 208, Level 16, State 6, Procedure v_ContentDistributionReport_G, Line 2
Invalid object name ‘v_ContentDistributionReport_G’.
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
StaffSo what was the end result of the SCCM 2012 R2 CU4 update ? Is it failed or partially successful? Have you tried the SQL script which is available as .SQL file in the source of CU4?
Regards
Anoop
my post above seems to be the end result so far. did run the mgm console and everything looks OK.
my only concern are the ‘errors’
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘k’.
Msg 207, Level 16, State 1, Procedure spAddRecoveryTrackingRecord, Line 41
Invalid column name ‘TransportType’.
Msg 207, Level 16, State 1, Procedure spAddRecoveryTrackingRecord, Line 46
Invalid column name ‘TransportType’.
Msg 207, Level 16, State 1, Procedure spAddRecoveryTrackingRecord, Line 49
Invalid column name ‘TransportType’.
thanks
StaffSorry, I don’t have much details about the stored procedure “spAddRecoveryTrackingRecord” and column name “TransportType”. Two options left as per my knowledge. 1) if you think the DB upgrade didn’t go well then you can try running the SQL files which is available in CU4 source to update the DB. 2) call CSS for more help as editing DB manually is not a supported scenario.
Regards
Anoop