Error preparing statement on japserserver not present in jasper studio

[s.desfontaines]

Hi,

I have an issue with my report.
When executing on studio or the query on sql server everything is running fine.

When i am publishing on jasperserver and lauching I am getting a straight error which is this one:

364 ERROR JRFillSubreport,pool-6-thread-1:948 - Fill 1: exception net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:

Here is the query:

WITH LISTE_TIR_PROD (COMPOSANT, COLORIS, FOURNISSEUR, POSITION, DATE_CREATION, VALIDATION_FINALE, DATE_VALIDATION_FINALE,
                     COMMENTAIRE_VALIDATION_FINALE, NUM_RAPPORT, DATE_ESTIMATIVE, COMMENTAIRE_PACK_TEST,COMMENTAIRE_BIB_LABO_PROD) AS (
    SELECT    EchantillonComposantCode,
            LEFT(EchantillonCouleurComposantNodeName,3),
            EchantillonFournisseurCode,
            RANK() OVER (
                PARTITION BY EchantillonComposantCode,LEFT(EchantillonCouleurComposantNodeName,3),EchantillonFournisseurCode
                ORDER BY EchantillonDateCreation) AS POSITION,
            EchantillonDateCreation,
            COALESCE(TirelleValidation,''),
            TirelleDateDecision,
            COALESCE(TirelleCommentaires,''),
            COALESCE(EchantillonNumeroRapportTest,''),
            TirelleDateEstimative,
            (CASE WHEN EchantillonCommentairePackTest IS NULL THEN NULL ELSE dbo.parseJSON(EchantillonCommentairePackTest) END),
            EchantillonBibLaboProd
    FROM REP_Qualite_Echantillon
    WHERE EchantillonPhase = 'PROD'
      AND EchantillonType = 'Tissu'
            AND LEFT(EchantillonSaisonNodeName,CHARINDEX('-',EchantillonSaisonNodeName)-1) = 'H20'
             -- AND LEFT(EchantillonSaisonNodeName,CHARINDEX('-',EchantillonSaisonNodeName)-1) = ''+?+''
)
SELECT
    MAX(MODELE.ModeleSaisonNodeName) AS [saison / Season],
    MAX(MODELE.ModeleMarqueNodeName) AS [Marque / Brand],
    (CASE
        WHEN MAX(BOM.NomenclatureFournisseurComposantCode) = '027011' THEN MAX(MODELE.ModelePaysFournisseurProd)
        ELSE MAX(BOM.NomenclatureFournisseurPays)
     END) AS [Pays / Country],
    (CASE
        WHEN MAX(BOM.NomenclatureFournisseurComposantCode) = '027011' THEN MAX(MODELE.ModeleNomFournisseurProd)
        ELSE MAX(BOM.NomenclatureFournisseurComposantNom)
     END) AS [Nom Fournisseur Composant / Material Supplier Name],
    MAX(BOM.NomenclatureComposantCodeFournisseur) AS [Codes Chez le Fournisseur / Supplier's Material Code],
    MAX(BOM.NomenclatureComposantLibelle) AS [Composant / Material],
    MAX(BOM.__MaterialColor) AS [Composant Coloré / Colored Material],
    CONVERT(DECIMAL(20,2),MAX(NomenclatureComposantPoids)) AS [Poids / Weight],
    MAX(BOM.NomenclatureComposantComposition) AS [Composition],
    (CASE WHEN MAX(BOM.NomenclatureComposantFourniParCWF) = 'true' THEN 'X' ELSE '' END) AS [Fourni CWF / Provided by CWF],
    MAX(MODELE.ModeleNodeName) AS [Modèle / Style],
    BOM.__StyleColor AS [Couleur Modèle / Style Color],
    MAX(MODELE.ModeleNomFournisseurProd) AS [Fournisseur PF / Style Supplier],
    MAX(MODELE.ModelePaysFournisseurProd) AS [Pays Fourn PF / Country of Style Supplier],
    MAX(b.BureauNom) AS [Equipe Bureau / Office team],
    (CASE WHEN MAX(COULEUR.CouleurModeleDelaiTheme) IS NULL THEN NULL
         ELSE 'D'+MAX(COULEUR.CouleurModeleDelaiTheme)
     END) AS [Délai Coloris Modèle / Style Color Deadline],

    MAX(t.EchantillonPSRValidation) AS [PSR/SMS: Validation Finale / Final Validation],
    CAST(MAX(t.EchantillonPSRDateValidation) AS DATE) AS [PSR/SMS: Date Validation Finale / Final Validation Date],
    MAX(t.EchantillonPSRCommentaireValidation) AS [PSR/SMS: Commentaires Validation Finale / Final Comments],

    CAST(MAX(t.BibLaboDateEstimative) AS DATE) AS [PROD/BULK: Date Estimative Bib Labo / Lab Dip Estimated Date],
    MAX(t.BibLaboValidation) AS [PROD/BULK: Validation Bib Labo / Lab Dip Validation],
    CAST(MAX(t.BibLaboDateDecision) AS DATE) AS [PROD/BULK: Date Décision Bib Labo / Lab Dip Decision Date],

    CAST(MAX(t.TirelleDateEstimative) AS DATE) AS [PROD/BULK: 1er lot/1th Batch Date Estimative Tirelle / Yardage Estimative Date],
    MAX(t.TirelleValidation) AS [PROD/BULK: 1er lot/1th Batch Validation Finale Tirelle / Yardage Final Validation],
    CAST(MAX(t.TirelleDateDecision) AS DATE) AS [PROD/BULK: 1er lot/1th Batch Date Validation Finale Tirelle / Yardage Final Validation Date],
    MAX(t.TirelleCommentaires) AS [PROD/BULK: 1er lot/1th Batch Commentaires Validation Finale Tirelle / Yardage Final Comments],
    MAX((CASE WHEN t.EchantillonCommentairePackTest IS NULL THEN NULL ELSE dbo.parseJSON(t.EchantillonCommentairePackTest) END)) AS [PROD/BULK: 1er lot/1th Batch Commentaires pack Test / Test Run comments],
    MAX(t.EchantillonBibLaboProd) AS [PROD/BULK: 1er lot/1th Batch Commentaires Bib Labo Prod / Bib Labo Prod comments],
    MAX(t.EchantillonNumeroRapportTest) AS [PROD/BULK: 1er lot/1th Batch N° de Rapport de Tests/ Tests Report Number],

    CAST(MAX(T2.DATE_ESTIMATIVE) AS DATE) AS [PROD/BULK: 2° lot/2° Batch Date Estimative Tirelle / Yardage Estimative Date],
    MAX(T2.VALIDATION_FINALE) AS [PROD/BULK: 2° lot/2° Batch Validation Finale Tirelle / Yardage Final Validation],
    CAST(MAX(T2.DATE_VALIDATION_FINALE) AS DATE) AS [PROD/BULK: 2° lot/2° Batch Date Validation Finale Tirelle / Yardage Final Validation Date],
    MAX(T2.COMMENTAIRE_VALIDATION_FINALE) AS [PROD/BULK: 2° lot/2° Batch Commentaires Validation Finale Tirelle / Yardage Final Comments],
    MAX(T2.COMMENTAIRE_PACK_TEST) AS [PROD/BULK: 2° lot/2° Batch Commentaires pack Test / Test Run comments],
    MAX(T2.COMMENTAIRE_BIB_LABO_PROD) AS [PROD/BULK: 2° lot/2° Batch Commentaires Bib Labo Prod / Bib Labo Prod comments],
    MAX(T2.NUM_RAPPORT) AS [PROD/BULK: 2° lot/2° Batch N° de Rapport de Tests/ Tests Report Number],

    CAST(MAX(T3.DATE_ESTIMATIVE) AS DATE) AS [PROD/BULK: 3ème lot/3rd Batch Date Estimative Tirelle / Yardage Estimative Date],
    MAX(T3.VALIDATION_FINALE) AS [PROD/BULK: 3ème lot/3rd Batch Validation Finale Tirelle / Yardage Final Validation],
    CAST(MAX(T3.DATE_VALIDATION_FINALE) AS DATE) AS [PROD/BULK: 3ème lot/3rd Batch Date Validation Finale Tirelle / Yardage Final Validation Date],
    MAX(T3.COMMENTAIRE_VALIDATION_FINALE) AS [PROD/BULK: 3ème lot/3rd Batch Commentaires Validation Finale Tirelle / Yardage Final Comments],
    MAX(T3.COMMENTAIRE_PACK_TEST) AS [PROD/BULK: 3ème lot/3rd Batch Commentaires pack Test / Test Run comments],
    MAX(T3.COMMENTAIRE_BIB_LABO_PROD) AS [PROD/BULK: 3ème lot/3rd Batch Commentaires Bib Labo Prod / Bib Labo Prod comments],
    MAX(T3.NUM_RAPPORT) AS [PROD/BULK: 3ème lot/3rd Batch N° de Rapport de Tests/ Tests Report Number],
    MAX(u.UserNomComplet) AS [Acheteur / Buyer]

FROM REP_Qualite_Placement AS BOM
INNER JOIN REP_Qualite_Modele AS MODELE ON MODELE.ModeleCode = BOM.NomenclatureModeleCode
      AND LEFT(MODELE.ModeleSaisonNodeName,CHARINDEX('-',MODELE.ModeleSaisonNodeName)-1) = ''+?+''
      --AND LEFT(MODELE.ModeleSaisonNodeName,CHARINDEX('-',MODELE.ModeleSaisonNodeName)-1) = 'H20'
INNER JOIN REP_Qualite_CouleurModele AS COULEUR ON COULEUR.CouleurModeleActif = 'true' AND MODELE.ModeleCode = COULEUR.ModeleCode
  AND BOM.__StyleColor = COULEUR.CouleurModeleNodeName
LEFT JOIN REP_Qualite_Echantillon AS t ON t.EchantillonFournisseurCode = (CASE WHEN LEFT(BOM.NomenclatureFournisseurComposantCode,6)='027011'
                                                                                   THEN MODELE.ModeleCodeFournisseurProd
                                                                                   ELSE BOM.[NomenclatureFournisseurComposantCode] END)
 AND t.EchantillonComposantCode = BOM.NomenclatureComposantCode AND LEFT(t.EchantillonComposantColoris,3) = LEFT(BOM.__MaterialColor,3)
 AND t.EchantillonPhase = 'PROD' AND t.EchantillonType = 'Tissu' AND LEFT(t.EchantillonSaisonNodeName,CHARINDEX('-',t.EchantillonSaisonNodeName)-1) = ''+?+''
 --AND t.EchantillonPhase = 'PROD' AND t.EchantillonType = 'Tissu' AND LEFT(t.EchantillonSaisonNodeName,CHARINDEX('-',t.EchantillonSaisonNodeName)-1) = 'H20'
LEFT JOIN LISTE_TIR_PROD AS T2 ON T2.POSITION = 2 AND T2.COMPOSANT = BOM.NomenclatureComposantCode AND T2.COLORIS = LEFT(BOM.__MaterialColor,3)
 AND T2.FOURNISSEUR = (CASE WHEN LEFT(BOM.NomenclatureFournisseurComposantCode,6)='027011'
                            THEN MODELE.ModeleCodeFournisseurProd
                            ELSE BOM.NomenclatureFournisseurComposantCode END)
LEFT JOIN LISTE_TIR_PROD AS T3 ON T3.POSITION = 3 AND T3.COMPOSANT = BOM.NomenclatureComposantCode AND T3.COLORIS = LEFT(BOM.__MaterialColor,3)
 AND T3.FOURNISSEUR = (CASE WHEN LEFT(BOM.NomenclatureFournisseurComposantCode,6)='027011'
                            THEN MODELE.ModeleCodeFournisseurProd
                            ELSE BOM.NomenclatureFournisseurComposantCode END)
LEFT JOIN REP_Users AS u ON u.UserID=MODELE.ModeleAcheteur
LEFT JOIN REP_Qualite_Modele_refs AS ModeleRef ON ModeleRef.fromID=MODELE._id_ AND ModeleRef.attrName='ModeleBureau'
LEFT JOIN REP_Bureaux AS b ON b._id_=ModeleRef.toID
WHERE (BOM.NomenclatureTypeDeFT = 'EDT' OR BOM.NomenclatureTypeDeFT = 'TDS')
  AND ISNUMERIC(BOM.NomenclatureComposantCode) >0
  AND (CONVERT(int, LEFT(BOM.NomenclatureComposantCode,2)) < 48 OR CONVERT(int, LEFT(BOM.NomenclatureComposantCode,2)) IN (82, 83))
  AND LEFT(BOM.NomenclatureComposantCode,2) != '42'
  AND LEFT(BOM.NomenclatureComposantCode,2) != '43'
  AND BOM.__MaterialColor != ''
  AND BOM.__MaterialColor IS NOT NULL
GROUP BY BOM.NomenclatureModeleCode, BOM.NomenclatureComposantCode, BOM.__StyleColor, BOM.__MaterialColor

 

Do you have any clue of whats happening?

 

Rgds Simon