SQL Server - SQL statement does not return a ResultSet object [message #671353] |
Tue, 17 May 2011 15:34 |
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
|
|
|
Powered by
FUDForum. Page generated in 0.01772 seconds