MSSQL Version
Microsoft SQL Server 2022 (RTM-CU2) (KB5023127) - 16.0.4015.1 (X64)
Feb 27 2023 15:40:01
Copyright (C) 2022 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor)
All database with compatibility_level=160
Sometimes when connecting the application via mssql-jdbc-9.4.1.jre8 errors "incorrect syntax ')'" appear, although the prepared query and procedure definition are correct.
Errors appear randomly. Stored Procedures can execute correctly several dozen times, and that an error appears in 2 or 3 subsequent executions. After that, the procedure works fine again (object definition hasn't changed)
The problem occurs on various procedures, but has never occurred with database compatibility_level=150.
Any ideas what could be causing the problems ?
Example:
Error caught by XE
StatementText5
select
c1.Table1Id
from
[dbo].[Table1] c1 with (nolock)
join [dbo].[Table5] c5 with(nolock) on c1.ColEntId is null and c5.TableId = c1.ColEntElId and c5.UsrId = @usr and c5.Nb = @nb and c5.IsD = 0
where
(c5.Dest = 1 and c1.IsDel = 1 and c1.ModDate > @dt)
or (c5.Dest = 0)
union
select
c1.Table1Id
from
[dbo].[Table1] c1 with (nolock)
join [dbo].[Table2] c2 with (nolock) on c2.Table2Id = c1.ColEntElId and c1.ColEntId = @Table1EntId
join [dbo].[Table3] c3 with (nolock) on c2.Table3Id = c3.Table3Id
join [dbo].[Table4] c4 with (nolock) on c4.Table4Id = c3.Table4Id
join [dbo].[Table5] c5 with(nolock) on c5.TableId = c4.TableId and c5.UsrId = @usr and c5.Nb = @nb and c5.IsD = 0
where
(c5.Dest = 1
and ((c1.IsDel = 1 and c1.ModDate > @dt) or (c2.IsDel = 1 and c2.ModDate > @dt) or (c3.IsDel = 1 and c3.ModDate > @dt) or (c4.IsDel = 1 and c4.ModDate > @dt))
)
or (c5.Dest = 0)
Procedure definition
create PROCEDURE [schema].[StoredProcedure]
@usr int,
@Nb varchar(100),
@dt datetime
AS
BEGIN
StatementText 3
StatementText 4
StatementText 5
END