error with paging on SqlServer 2012

Posts   
 
    
virginia
User
Posts: 24
Joined: 29-Nov-2016
# Posted on: 17-May-2018 10:01:25   

Hello. Using LLBL 5.3 (5.3.0) RTM and .net 4.5.2, connecting to sql server 2005, sql server 2012 and ora11g databases, db first, adapter model.

Paging works fine on oracle and sql server 2005, but when connecting to a sql server 2012 server there's an error Incorrect syntax near With. Taking the query from the exception I've got

declare @p1_param DateTime
declare @p2_param Char
declare @p3_param Char(2)
declare @p4_param Char(2)
declare @p5_param Int
declare @p6_param Int

set @p1_param = '20140101'
set @p2_param = 'I'
set @p3_param = 'L9'
set @p4_param = 'LI'
set @p5_param = 50
set @p6_param = 100

/*set @p1_param = @p1
set @p2_param = @p2
set @p3_param = @p3
set @p4_param = @p4
set @p5_param = @p5
set @p6_param = @p6*/

WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM (SELECT DISTINCT TOP(@p6_param) [dbo].[INTERVENTIONS].[SSIG_NUM], [dbo].[INTERVENTIONS].[SSIG_ARBO], [dbo].[INTERVENTIONS].[SSIG_TYP], [dbo].[INTERVENTIONS].[SSIG_DEG], [dbo].[INTERVENTIONS].[SSIG_SSERV], [dbo].[INTERVENTIONS].[SSIG_CODDEM], [dbo].[INTERVENTIONS].[SSIG_DEM], [dbo].[INTERVENTIONS].[SSIG_NUMDEM], [dbo].[INTERVENTIONS].[SSIG_DAT], [dbo].[INTERVENTIONS].[SSIG_DATDEM], [dbo].[INTERVENTIONS].[SSIG_DATINT], [dbo].[INTERVENTIONS].[SSIG_NDT], [dbo].[INTERVENTIONS].[SSIG_CHANT], [dbo].[INTERVENTIONS].[SSIG_EMAIL], [dbo].[INTERVENTIONS].[SSIG_TEL], [dbo].[INTERVENTIONS].[SSIG_CONTACT], [dbo].[INTERVENTIONS].[SSIG_COMPT], [dbo].[INTERVENTIONS].[SSIG_AFF], [dbo].[INTERVENTIONS].[SSIG_REFSTO], [dbo].[INTERVENTIONS].[SSIG_COMPTF], [dbo].[INTERVENTIONS].[SSIG_ATTRIB], [dbo].[INTERVENTIONS].[SSIG_LIBD], [dbo].[INTERVENTIONS].[SSIG_QTEPSTO], [dbo].[INTERVENTIONS].[SSIG_QTERSTO], [dbo].[INTERVENTIONS].[SSIG_SOC], [dbo].[INTERVENTIONS].[SSIG_TYPMVT], [dbo].[INTERVENTIONS].[SSIG_DEBPER], [dbo].[INTERVENTIONS].[SSIG_DATMONT], [dbo].[INTERVENTIONS].[SSIG_FINPER], [dbo].[INTERVENTIONS].[SSIG_DATDEMONT], [dbo].[INTERVENTIONS].[SSIG_ADRLIVR1], [dbo].[INTERVENTIONS].[SSIG_ADRLIVR2], [dbo].[INTERVENTIONS].[SSIG_ADRLIVR3], [dbo].[INTERVENTIONS].[SSIG_VILLE], [dbo].[INTERVENTIONS].[SSIG_CAUTION], [dbo].[INTERVENTIONS].[SSIG_ATEL], [dbo].[INTERVENTIONS].[SSIG_SOCSTO], [dbo].[INTERVENTIONS].[SSIG_CORRES], [dbo].[INTERVENTIONS].[SSIG_ANCHANT], [dbo].[INTERVENTIONS].[SSIG_SECTION], [dbo].[INTERVENTIONS].[SSIG_CODCONTACT], [dbo].[INTERVENTIONS].[SSIG_TYPLOC], [dbo].[INTERVENTIONS].[SSIG_REGROUP], [dbo].[INTERVENTIONS].[SSIG_LIQUIDATION], [dbo].[INTERVENTIONS].[SSIG_DOTATION], [dbo].[INTERVENTIONS].[SSIG_ENTID], [dbo].[INTERVENTIONS].[SSIG_RETOUR], [dbo].[INTERVENTIONS].[SSIG_LIBDEF], [dbo].[INTERVENTIONS].[SSIG_NUM] AS [ALLAFF], [dbo].[INTERVENTIONS].[SSIG_NUM] AS [COMMANDES], [dbo].[INTERVENTIONS].[SSIG_ETAPE], [dbo].[INTERVENTIONS].[SSIG_PHASE], [dbo].[INTERVENTIONS].[SSIG_LIBELLE], [dbo].[INTERVENTIONS].[SSIG_SWA], [dbo].[INTERVENTIONS].[SSIG_OBSSATISF], [dbo].[ARBO].[ARB_DES], [dbo].[ARBO].[ARB_REF], [dbo].[LIGNBUDG].[LB_TVA], [dbo].[AFFECTATIONINTERVENTION].[SAFF_DATP], [dbo].[AFFECTATIONINTERVENTION].[SAFF_DATCLOT], [dbo].[AFFECTATIONINTERVENTION].[SAFF_POS], [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTPCER_T], [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTPCER], [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTPCER_T] AS [SAFF_MTTOT_T], (([dbo].[AFFECTATIONINTERVENTION].[SAFF_MTPCER] + [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTMOR]) + ([dbo].[AFFECTATIONINTERVENTION].[SAFF_MTSTRR] + [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTDIVR])) AS [SAFF_MTTOT], [dbo].[AFFECTATIONINTERVENTION].[SAFF_TEMPSR_C], [dbo].[AFFECTATIONINTERVENTION].[SAFF_TEMPSR], [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTMOR_T], [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTMOR], [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTSTRR_T], [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTSTRR], [dbo].[AFFECTATIONINTERVENTION].[SAFF_DATF], [dbo].[AFFECTATIONINTERVENTION].[SAFF_POS] AS [POSITION], [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTDIVR_T], [dbo].[AFFECTATIONINTERVENTION].[SAFF_MTDIVR], [dbo].[AFFECTATIONINTERVENTION].[SAFF_ATEL], [dbo].[AFFECTATIONINTERVENTION].[SAFF_SIG], [dbo].[AFFECTATIONINTERVENTION].[SAFF_DATP] AS [DATP], [dbo].[AFFECTATIONINTERVENTION].[SAFF_TEMPS], [dbo].[AFFECTATIONINTERVENTION].[SAFF_SOC], [dbo].[AFFECTATIONINTERVENTION].[SAFF_FINPREV], [dbo].[AFFECTATIONINTERVENTION].[SAFF_DATEDETAIL], [dbo].[AFFECTATIONINTERVENTION].[SAFF_SWA] FROM ((([dbo].[INTERVENTIONS] LEFT JOIN [dbo].[AFFECTATIONINTERVENTION] ON [dbo].[INTERVENTIONS].[SSIG_TYPMVT]=[dbo].[AFFECTATIONINTERVENTION].[SAFF_TYPMVT] AND [dbo].[INTERVENTIONS].[SSIG_NUM]=[dbo].[AFFECTATIONINTERVENTION].[SAFF_NUMSIG]) LEFT JOIN [dbo].[ARBO] ON [dbo].[INTERVENTIONS].[SSIG_ARBO]=[dbo].[ARBO].[ARB_ID]) LEFT JOIN [dbo].[LIGNBUDG] ON [dbo].[INTERVENTIONS].[SSIG_LIBD]=[dbo].[LIGNBUDG].[LB_ID]) WHERE ( ( ( [dbo].[INTERVENTIONS].[SSIG_DAT] >= @p1_param) AND ( [dbo].[INTERVENTIONS].[SSIG_TYPMVT] = @p2_param) AND ( [dbo].[AFFECTATIONINTERVENTION].[SAFF_SIG] = @p3_param AND [dbo].[AFFECTATIONINTERVENTION].[SAFF_ATEL] = @p4_param))) ORDER BY [dbo].[INTERVENTIONS].[SSIG_NUM] ASC) AS _tmpSet) SELECT [SSIG_NUM], [SSIG_ARBO], [SSIG_TYP], [SSIG_DEG], [SSIG_SSERV], [SSIG_CODDEM], [SSIG_DEM], [SSIG_NUMDEM], [SSIG_DAT], [SSIG_DATDEM], [SSIG_DATINT], [SSIG_NDT], [SSIG_CHANT], [SSIG_EMAIL], [SSIG_TEL], [SSIG_CONTACT], [SSIG_COMPT], [SSIG_AFF], [SSIG_REFSTO], [SSIG_COMPTF], [SSIG_ATTRIB], [SSIG_LIBD], [SSIG_QTEPSTO], [SSIG_QTERSTO], [SSIG_SOC], [SSIG_TYPMVT], [SSIG_DEBPER], [SSIG_DATMONT], [SSIG_FINPER], [SSIG_DATDEMONT], [SSIG_ADRLIVR1], [SSIG_ADRLIVR2], [SSIG_ADRLIVR3], [SSIG_VILLE], [SSIG_CAUTION], [SSIG_ATEL], [SSIG_SOCSTO], [SSIG_CORRES], [SSIG_ANCHANT], [SSIG_SECTION], [SSIG_CODCONTACT], [SSIG_TYPLOC], [SSIG_REGROUP], [SSIG_LIQUIDATION], [SSIG_DOTATION], [SSIG_ENTID], [SSIG_RETOUR], [SSIG_LIBDEF], [ALLAFF], [COMMANDES], [SSIG_ETAPE], [SSIG_PHASE], [SSIG_LIBELLE], [SSIG_SWA], [SSIG_OBSSATISF], [ARB_DES], [ARB_REF], [LB_TVA], [SAFF_DATP], [SAFF_DATCLOT], [SAFF_POS], [SAFF_MTPCER_T], [SAFF_MTPCER], [SAFF_MTTOT_T], [SAFF_MTTOT], [SAFF_TEMPSR_C], [SAFF_TEMPSR], [SAFF_MTMOR_T], [SAFF_MTMOR], [SAFF_MTSTRR_T], [SAFF_MTSTRR], [SAFF_DATF], [POSITION], [SAFF_MTDIVR_T], [SAFF_MTDIVR], [SAFF_ATEL], [SAFF_SIG], [DATP], [SAFF_TEMPS], [SAFF_SOC], [SAFF_FINPREV], [SAFF_DATEDETAIL], [SAFF_SWA] FROM __actualSet WHERE [__rowcnt] > @p5_param AND [__rowcnt] <= @p6_param ORDER BY [__rowcnt] ASC

        

Executed on Sql Server 2012 this gives "Syntaxe incorrecte près du mot clé 'with'. Si l'instruction est une expression de table commune, une clause xmlnamespaces ou une clause de contexte de suivi des modifications, l'instruction précédente doit se terminer par un point-virgule."

I understand that not terminating T-SQL statements with a semicolon is a deprecated feature. Could you tell me if there's an option in LLBL to use a semicolon? Thanks

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-May-2018 11:52:48   

I don't really know what the comment section /* set... */ is doing there... do you use code to augment the query?

Frans Bouma | Lead developer LLBLGen Pro
virginia
User
Posts: 24
Joined: 29-Nov-2016
# Posted on: 18-May-2018 10:11:15   

/* set... */ - that was the code in the query in the exception. I replaced the parameters with the values to keep it simple, but that is the only change I made

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-May-2018 10:29:54   

Could you try the latest v5.3 build (5.3.5) to see if that fixes things? I don't know what's wrong with the query. The query we generate and execute starts with "WITH", all elements before that are created by ADO.NET (SqlClient's SqlCommand), and are basically the parameter declarations. All our tests pass, so it's not something wrong with the code in all cases. There hasn't been a change in this piece of code since 2013, so it's odd you run into an issue here.

The query you pasted is the only one that fails? Smaller queries fail too? (Which might be easier to spot the error, this one is so massive I can't see it) You use the default Sqlserver DQE we ship or do you use a custom one which alters generated queries?

The query you pasted is the one you obtained from sql profiler? Or from tracing? If the query you pasted is from the exception, your system might use custom code to augment the query to avoid parameter sniffing issues, as the query at the ADO.NET level starts with 'WITH', the parameter declarations aren't in the generated SQL

Frans Bouma | Lead developer LLBLGen Pro
virginia
User
Posts: 24
Joined: 29-Nov-2016
# Posted on: 04-Jun-2018 13:40:16   

Thanks again. I followed you tips for investigating and, once again, I found the cause deep in our own code.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Jun-2018 18:31:37   

Thanks for getting back to us and I'm glad it's solved! simple_smile

Frans Bouma | Lead developer LLBLGen Pro