- Home
- LLBLGen Pro
- Bugs & Issues
error with paging on SqlServer 2012
Joined: 29-Nov-2016
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
Joined: 17-Aug-2003
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