I have got the following error SCCM Application Request Failed – SqlException caught in SubmitNewRequest deadlock or retry count exceeded. This post will discuss the issue we were facing a few weeks ago when trying to request applications from “Company Portal” and “Software Center“.
I started getting different errors at different places. The error message that I received in Software Center was different from the one I got in Company Portal. I think the issue was interconnected. We will see both the error messages in this post.
First, we will discuss the issue while requesting the application from the software center because that’s my primary domain. I have seen a different issue related to Software Center can not be loaded. I also cover the company portal error message in this post.
There was also an error related approve software requests via email. You will learn about the root cause of these issues and resolve them. You will learn how to fix the following problems in this post:
- Issue 1 :- Unable to approve software requests via e-mail.
- Issue 2:- Very few users getting attached errors while requesting software.
- SCCM Software Center vs ConfigMgr Client Applet | Differences
- FIX Software Center Crash Error SCNotification Has Stopped Working
- Application Request | Company Portal Failed
Software Center Error Application eMail Approval Request
You can see more details about Software Center Error Application eMail Approval Request. As soon as we clicked on application details and selected request, we got the below error in the software center.
The software center can not be loaded. There is a problem loading the required components for Software Center. You can try launching Software Center at a later time. If the problem continues, you can contact your helpdesk.
The application requires administrator approval to be installed.
Submitting Request and status -> Approval Required.
Version: 87
The screenshot of the error given above is not much help because the error printed is generic. Now, it’s time to look at the log files as well.
That’s where our experts come into the picture. Anoop Nair has described everything we need to know about logs, and if you notice the blog is getting updated very frequently (If any addition in the logging mechanism of SCCM) https://www.anoopcnair.com/sccm-logs-files-list-of-configmgr-log-files/
We have to check 2 logs at the client end. Log names along with functions they report. There are server-side and client-side logs for the Software center that can not be loaded.
SCCM Client Side – Logs for Softwware Center Related Issues.
| SCClient_<domain>@<username>_1.log | Records the activity in Software Center for the specified user on the client computer. |
| SCClient_<domain>@<username>_2.log | Records the historical activity in Software Center for the specified user on the client computer. |
SCCM Server-side logs for Software Center Related issues.
| SCNotify_<domain>@<username>_1.log | Records the activity for notifying users about software for the specified user. |
| SCNotify_<domain>@<username>_1-<date_time>.log | Records the historical information for notifying users about software for the specified user. |
Company Portal Error Requesting the application
We were shifting the focus to Company Portal. We saw the below error while requesting the application from Company Portal. I tried asking the same application that we did via Software Center for better understanding. Error Screenshot below:
Company portal Application Error: Error Requesting the application. An error occurred while attempting to request the application.
Troubleshooting
Now we will check the UserService.log on the Management Point to which this client is reporting. Go running – Type “Control smscfgrc.” It will have an “Assigned Management Point.” On that MP, we have to check the UserService log.
It clearly shows the deadlock error for multiple entries for an EventID, due to which it does not process the request.
Now we know what’s the problem so let’s check in to SCCM DB. Connect to the SCCM DB using SQL Management Studio and run the below query. You can see many other SQL queries in the previous post.
Select * from AlertEvents
The result of the SQL query will look like the screenshot below:
We can see there are multiple AlertID’s as duplicates, which was causing this issue.
NOTE! – Make sure you have a good backup of the site DB before you proceed further.
Solution to Fix Failed SqlException caught in SubmitNewRequest deadlock or retry count exceeded
Let’s try to fix the problem Failed SqlException caught in SubmitNewRequest deadlock or retried count exceeded.
The solution is very simple. Run the SQL Query Select * from AlertEvents
Now, it’s time to delete the duplicate entry of the alert ID from the database. Make sure you have taken the backup of the SQL database before you do the following SQL command.
Note the duplicate “AlertID’s.” In my case, it’s “16777224”. It will be different for your environment.
- Open the SQL Management Studio.
- Connect your Database Engine.
- Right Click on your database CM_XXX and click on ‘New Query’
- Copy the following SQL query to fix the issue (SqlException caught in SubmitNewRequest deadlock or retry count exceeded).
- Click on the Execute button.
Now execute -> Delete from AlertEvents where AlertID = ‘16777224’
Now try requesting the application, and it should work without any issues. We noted that the email approval process for application requests was also not working due to this issue, and it was fixed later once we implemented this fix.
NOTE: MS Doesn’t support this DB tweaking, and you should involve MS Engineer before executing these SQL statements.
NOTE: Suddenly the error came back after few days and this time the fix mentioned in this article doesn’t fix it. I am working on it and will update as soon as i get something.
Even considering that the error was clear, we were not sure what was the statement that was causing such situation and because of that we had to capture additional details. To help on the investigation we captured a set of events by using Extended Events. The definition of the Extended Session we used is indicated following:
CREATE EVENT SESSION [Std_XE] ON SERVER
ADD EVENT sqlserver.attention( ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.error_reported( ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.query_post_execution_showplan,
ADD EVENT sqlserver.rpc_completed( ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.rpc_starting( ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sp_statement_completed( ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sp_statement_starting( ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_completed( ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_starting( ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_completed( ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_starting( ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N’C:\MS_DATA\Std_XE.xel’)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
— start a session
ALTER EVENT SESSION [Std_XE] ON SERVER STATE = START;
— stop a session
ALTER EVENT SESSION [Std_XE] ON SERVER STATE = STOP;
The trace was gathered by using the above XE Sessions and the results were analyzed. From the analysis we could understand that the statement that was causing the error was the one that is highlighted below:
From the same trace, it was possible to understand the statement (INSERT INTO) that was causing the error. The INSERT is part of the Stored Procedure ([dbo].[usp_CreateUserApplicationRequest]) [Object ID 270057510). The relevant part of the Stored Procedure (the one on which the error is being thrown is below):
SQL QUERY
The INSERT statement highlighted above tries to INSERT data based on the results of a SELECT statement. To confirm if the SELECT was returning any data (which was the expected behavior) we tried to run the SELECT statement directly on SSMS as indicated below (the [?] was replaced by a proper UserID value (obtained from the User_DISC table) but there weren’t returned results
SELECT 0, GETDATE(), ” from UserTargetedAppModelSoftware us
WHERE us.AppID = N’ScopeId_BBBB1F95-B838-4F26-A1FA-680AE77EDD68/Application_fe0ed63a-a8d0-4e63-830c-ba261f9ef24e’ and us.UserID = [?]
This means there was no information being passed to the DB related to this application and hence when trying to request it was failing.
Now we moved towards deployment because as we can see in the above lines and the statement below where insert fails which means it’s happening as soon as we are trying to request the application from the software center.
| Violation of UNIQUE KEY constraint ‘AlertEvents_AK’. Cannot insert duplicate key in object ‘dbo.AlertEvents’. The duplicate key value is (16777968, 1, 90B6EA29-ACA1-4120-884C-8D72E50C6FE4, Dec 11 2021 6:30AM). |
We noticed the collection to which this application was deployed had both users and user groups into the same collection so the request was being processed twice because few users in this collection were part of the user groups as well and once we try requesting application both the functions trying to request at the same time causing duplicate entry.
I have uploaded the Query to GitHub – Please refer to the same https://github.com/AnoopCNair/Fix-SCCM-Application-Request-Failed-SqlException/blob/main/SQL.docx
Solution
Created a new collection and included only domain users. Deployed the application to this collection along with the “Approval required” option enabled and we were able to request it without any errors.
Disclaimer – The information provided on the site is for general informational purposes only. All information on the site is provided in good faith. However, we make no representation or warranty of any kind, express or implied, regarding the accuracy, adequacy, validity, reliability, availability, or completeness of any information on the website.
Author
My name is Deepak Rai and I am a Technical Lead on SCCM and Intune with more than 13 years of experience in IT. My main domain is SCCM 2012, CB, MECM, Intune, and Azure (Runbooks). I have worked on several platforms (Active Directory, Exchange, Veritas NETBACKUP, Symantec Backup Exec NDMP devices Like Netapp, EMC Data Domain, Quantum using Backup Exec 2010 and 2012, HP storage works 4048 MSL G3, Data Deduplication related troubleshooting.) in these 13 years but at last ended up to the technology from which I started as IT Engineer (SCCM).