Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » SQL Server - SQL statement does not return a ResultSet object(Error in resultset)
SQL Server - SQL statement does not return a ResultSet object [message #671353] Tue, 17 May 2011 11:34
eminaguil is currently offline eminaguil
Messages: 8
Registered: February 2011
Junior Member
Hi,

I have created a stored procedure that return a custom resultset, I have tested in SqlServer Managment Studio and it works fine but in Birt Viewer or eclipse I allways get the following error

org.eclipse.birt.report.engine.api.EngineException: Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1: Sintaxis incorrecta cerca de ')'.
;


I'm using Win2003 sp2, Eclipse Birt Version: 2.5.2, Microsoft SQL Server JDBC Driver 3.0, and Tomcat 7

here is the store proc.

CREATE PROCEDURE [dbo].[sp_rep_reporte_personalizado_preview] 
	@id_reporte int = 0,
	@fecha_ini datetime = null,
	@fecha_fin datetime = null
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @sql_tabla as varchar(4000)
	DECLARE @sql_ins as varchar(4000) 
	DECLARE @sql_cons as varchar(4000) 
	DECLARE @sep as varchar(2)
	DECLARE @alias as varchar(80)
	DECLARE @id_tipo_elemento_reporte as int
		
	SET @sql_tabla  = 'CREATE TABLE ##res('
	set @sql_ins = 'INSERT INTO ##res values('
	set @sql_cons = 'select '
	set @sep = ''
	
	DECLARE curCols CURSOR SCROLL
	FOR
		SELECT    alias, id_tipo_elemento_reporte
		FROM      elemento_reporte
		WHERE     id_reporte = @id_reporte
		ORDER BY  orden
   
   OPEN curCols
   
   FETCH NEXT FROM curCols
   INTO @alias, @id_tipo_elemento_reporte 
   
   WHILE @@FETCH_STATUS = 0
   BEGIN
              
		if (@id_tipo_elemento_reporte = 1) 
		begin
			SET @sql_tabla = @sql_tabla + @sep + @alias + ' int'
			SET @sql_ins = @sql_ins + @sep + '1'
		end
		if (@id_tipo_elemento_reporte = 2)  
		begin
			SET @sql_tabla = @sql_tabla + @sep + @alias + ' datetime NULL' 
			SET @sql_ins = @sql_ins + @sep + 'getdate()'
		end  

		if (@id_tipo_elemento_reporte = 3)  
		begin
			SET @sql_tabla = @sql_tabla + @sep + @alias + ' varchar(300) NULL'
			SET @sql_ins = @sql_ins + @sep + '''texto de prueba'''
		end   

		if (@id_tipo_elemento_reporte = 10)  
		begin
			SET @sql_tabla = @sql_tabla + @sep + @alias + ' decimal NULL'
			SET @sql_ins = @sql_ins + @sep + '1.1'
		end

		SET @sql_cons = @sql_cons + @sep + @alias		
		SET @sep = ', ' 

		FETCH NEXT FROM curCols
		INTO @alias, @id_tipo_elemento_reporte 
 
   END

	CLOSE curCols
	DEALLOCATE curCols
   
	IF OBJECT_ID('tempdb..##res') IS NOT NULL DROP TABLE ##res
   
	SET @sql_tabla = @sql_tabla + ')'
	SET @sql_ins = @sql_ins + ')'
	SET @sql_cons = @sql_cons + ' from #res'

	EXEC (@sql_tabla)
	EXEC (@sql_ins)

	select * from ##res
END


and I have atached the report
Previous Topic:view as pdf, the layout is distorted seriously
Next Topic:AxisFault
Goto Forum:
  


Current Time: Tue Sep 02 10:15:11 EDT 2014

Powered by FUDForum. Page generated in 0.02332 seconds