R.Rebouh R.Rebouh - 6 months ago 24
SQL Question

Can't execute sql server query in BIRT

I have recently started using BIRT for some very simple reporting in a Java\SQL Server application. Now I have a dynamic sql stmt that I'm trying to use in the DataSet on BIRT ( sorry for my english i"am Frensh ) the probleme is that BIRT can't create data set, i didn't find solution for this report. Birt told me that sql server doesn't return sql statement does not return a ResultSet object. SQL error #1, i also want to know if we can use sql variables in birt to create our data set
this is my sql query :

DECLARE @ListeAffaires TABLE(Nom_APPEL nvarchar(MAX))

INSERT INTO @ListeAffaires(Nom_APPEL)

SELECT DISTINCT AF_NOMAPPEL
FROM F_AFFAIRES INNER JOIN T_P_SOCIETE ON AF_CODE_SOCIETE = SOC_CODE
WHERE SOC_CODE = 'RUS01'

declare @AffairesChaine NVARCHAR(MAX)
SET @AffairesChaine = STUFF(
( SELECT distinct ',ISNULL(' + QUOTENAME(AF_NOMAPPEL) + ', ''0'') ' + QUOTENAME(AF_NOMAPPEL)
FROM F_AFFAIRES INNER JOIN T_P_SOCIETE ON AF_CODE_SOCIETE = SOC_CODE
INNER JOIN VUE_VALORISATION_TEMPS_PASSES ON VTPS_CODE_AFFAIRE = AF_CODE_AFFAIRE
WHERE SOC_CODE = 'RUS01' AND VTPS_date_FICHE >= '02/11/2015'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')



DECLARE @AffairesChaines NVARCHAR(MAX)
SET @AffairesChaines = ''
SELECT @AffairesChaines = @AffairesChaines +'['+ Nom_APPEL + '],' FROM @ListeAffaires
SET @AffairesChaines = LEFT (@AffairesChaines, LEN(@AffairesChaines) - 1)

DECLARE @sqlCommand varchar(MAX)
SET @sqlCommand = 'DECLARE @SYNTHESETEMPS TABLE ( VTPS_CODE_COLLAB varchar(250)
, VTPS_NOM_COLLAB varchar(250)
, VTPS_PRENOM_COLLAB varchar(250)
, TSRV_LIBELLE varchar(250)
, VTPS_COL_ALPHANUM01 varchar(10)
, AF_NOMAPPEL NVARCHAR(MAX)
, VTPS_DUREE float(24)
)

INSERT INTO @SYNTHESETEMPS (
VTPS_CODE_COLLAB
, VTPS_NOM_COLLAB
, VTPS_PRENOM_COLLAB
, TSRV_LIBELLE
, VTPS_COL_ALPHANUM01
, AF_NOMAPPEL
, VTPS_DUREE
)
SELECT VTPS_CODE_COLLAB
, VTPS_NOM_COLLAB
, VTPS_PRENOM_COLLAB
, TSRV_LIBELLE
, VTPS_COL_ALPHANUM01
, AF_NOMAPPEL
, VTPS_DUREE
FROM VUE_VALORISATION_TEMPS_PASSES
INNER JOIN F_P_COLLABORATEUR ON VTPS_CODE_COLLAB = CLB_CODE
INNER JOIN F_AFFAIRES ON VTPS_CODE_AFFAIRE = AF_CODE_AFFAIRE
INNER JOIN T_COL_SERVICE ON CLB_TSRV_IDENT = TSRV_IDENT
INNER JOIN T_P_SOCIETE ON AF_CODE_SOCIETE = SOC_CODE
WHERE SOC_CODE = ''RUS01'' AND VTPS_date_FICHE >= ''02/11/2015''

SELECT VTPS_NOM_COLLAB +'' '' + VTPS_PRENOM_COLLAB AS "Remaining hours"
, TSRV_LIBELLE AS "Service"
, VTPS_COL_ALPHANUM01 AS "Categories"
,'+ @AffairesChaine +'
FROM @SYNTHESETEMPS
PIVOT (SUM(VTPS_DUREE)
FOR
AF_NOMAPPEL IN ('+@AffairesChaines+')) PVT '
EXEC(@sqlCommand)

Answer

As far as I can relate, BIRT is generally not able to get any metadata from your dynamic query because, exactly it is dynamic, so it basically could change at runtime. There are workarounds, but your problem is that you use a pivot function in your dynsql, resulting in variable amount of columns.

This will not work for your query, BIRT needs the exact amount of columns, names and types from your dataset.

Instead shift the pivot to BIRT and make your dataset return exact metadata. BIRT is able use pivot.

You can however use dynamic sql, as long as you make sure that the resultset is consistent. First of all pack your dynamic sql into a stored procedure on the sql server, only pass the parameter.

For BIRT to recognize dataset metadata, you have the options:

For SQL Server 2012+ define your resultset for example like this:

EXEC(@sqlCommand)
WITH RESULT SETS
(
  (
    RemainingHours INT,
    Service NVARCHAR(255),
    VTPS_COL_ALPHANUM01 NVARCHAR(255),
    AF_NOMAPPEL NVARCHAR(255)
  )
);

If you don't own an SQL Server 2012 and above, turn the SET FMTLONLY ON in your StoredProc, add the SP as your dataset source, retain the metadata and finally alter your SP with SET FMTONLY OFF. FMTLONLY ON will return only metadata to the BIRT.

If above doesnt work, try to ALTER your StoredProcedure with a simple dummy Statement. Example:

SELECT CAST(0 as INT) as RemainingHours,
    CAST('test' as NVARCHAR(255)) as Service,
    CAST('test' as NVARCHAR(255)) as VTPS_COL_ALPHANUM01,
    CAST('test' as NVARCHAR(255)) as AF_NOMAPPEL

Create your dataset, get the metadata and afterwards alter your SP with your dynamic sql. For this you could also add some sort of debug parameter "ReturnMetadataOnly" or something similar.