You’re offline. This is a read only version of the page.
65
Hello guys,
We have a CRM 2011 server which starts to log a lot of the following error in Event Viewer :
Host [XXXXX].MSCRMAsyncService$maintenance.8d438353-6e31-48c5-be5f-72ae4c408103: a config database error occured. Exception: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '-'.
Incorrect syntax near the keyword 'AND'.
Incorrect syntax near the keyword 'AND'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Microsoft.Crm.Asynchronous.JobDataAccess.GetNextJob(IList`1 orgsAvailableForMaintenance, DateTime startCycleTime, Int32 maxJobsToReturn)
at Microsoft.Crm.Asynchronous.JobDataAccess.GetNextJobInAppLock(IList`1 orgsAvailableForMaintenance, DateTime startCycleTime, Int32 maxJobsToReturn)
at Microsoft.Crm.Asynchronous.JobDataAccess.SelectJobs(DateTime startCycleTime, Int32 maxJobsToRetrieve)
ClientConnectionId:34489585-57af-49e2-bf64-2118b909f0ee
Error Number:102,State:1,Class:15
After a trace on CRM DB server we get to the following sql batch, on MSCRM_CONFIG, that use "select top -1", which generates the problem. It seems that it is a system generated sql but we can't figured it out how to solved this. Any idea?
exec sp_executesql N'
DECLARE @priorityTable table
(
OperationType int not null,
Priority int not null,
CapacityType int not null
)
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
1
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
2
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
3
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
4
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
5
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
6
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
7
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
8
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
9
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
10
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
11
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
12
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
13
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
14
,5
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
15
,3
,2 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
16
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
17
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
18
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
19
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
20
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
21
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
22
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
23
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
24
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
25
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
26
,25
,2 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
27
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
28
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
29
,7
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
30
,4
,2 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
31
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
32
,1
,2 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
35
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
38
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
40
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
41
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
42
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
43
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
44
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
45
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
46
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
47
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
48
,1
,1 )
INSERT INTO @priorityTable (
OperationType
,Priority
,CapacityType
)
VALUES (
49
,1
,1 )
DECLARE @invalidIds table
(
Id uniqueidentifier not null
)
INSERT INTO @invalidIds
SELECT Id FROM ScaleGroupOrganizationMaintenanceJobs
INNER JOIN @priorityTable q ON q.OperationType = ScaleGroupOrganizationMaintenanceJobs.OperationType
WHERE CapacityType = @limitedServer
AND TargetServer IN
(
SELECT TargetServer
FROM ScaleGroupOrganizationMaintenanceJobs
INNER JOIN @priorityTable r ON r.OperationType = ScaleGroupOrganizationMaintenanceJobs.OperationType
GROUP BY TargetServer, CapacityType
HAVING CapacityType = @limitedServer AND TargetServer IS NOT NULL AND SUM(State) > 0
)
SELECT top -1
Id, OrganizationId, TargetServer, p.OperationType as OpType, RecurrenceStartTime, RecurrencePattern,
CapacityType , JobParameters
FROM
ScaleGroupOrganizationMaintenanceJobs j JOIN @priorityTable p
ON j.OperationType = p.OperationType
WHERE
j.NextRunTime < @startCycleTime
AND j.Enabled = 1
AND j.OrganizationId IN (''89b51cbc-9c4b-4c98-bdad-fc1c16213288'',''966aa974-0ab4-e311-b089-00155d2d4e03'',''e19fdfea-827a-e211-90a5-00155d2d4e03'')
AND j.TargetServer NOT IN
(
SELECT TargetServer from ScaleGroupOrganizationMaintenanceJobs
WHERE ScaleGroupOrganizationMaintenanceJobs.State = @lockedState AND TargetServer IS NOT NULL
GROUP BY TargetServer
HAVING Count(*) > @maxConcurrentServerJobs
)
AND j.OrganizationId NOT IN
(
SELECT DISTINCT OrganizationId FROM ScaleGroupOrganizationMaintenanceJobs
WHERE ScaleGroupOrganizationMaintenanceJobs.State = @lockedState
)
AND j.Id NOT IN
(
SELECT Id FROM @invalidIds
)
ORDER BY (p.Priority * @priorityCoefficient + DATEDIFF(MINUTE, j.NextRunTime, GETUTCDATE()) * @overdueCoefficient) DESC',N'@startCycleTime datetime,@readyState int,@lockedState int,@maxConcurrentServerJobs int,@priorityCoefficient int,@overdueCoefficient int,@limitedServer int',@startCycleTime='2019-05-29 16:01:17.593',@readyState=0,@lockedState=1,@maxConcurrentServerJobs=5,@priorityCoefficient=3,@overdueCoefficient=2,@limitedServer=1
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (