Temporary tables in Dataset

CubicalMan

Hello,

I'm a bit new to BIRT and SQL, and I have what I hope is a basic question. I'm using MSSQL 2008 R2 and the JDBC driver from Microsoft. Everything works great, except when I try and use multiple SQL statements in a Data Set.

What I'd like do to is something like this:

CREATE TABLE #TEMPTABLE(
--code
);

INSERT INTO #TEMPTABLE
SELECT
--code;

SELECT
--code
FROM #TEMPTABLE;

DROP TABLE #TEMPTABLE;

First I tried this with MSSQL table variables (@TEMPTABLE) and I've also tried it not using temp tables at all but just regular tables. Everything I do works great in SQL studio, but fails in BIRT with

org.eclipse.birt.report.engine.api.EngineException: Error happened while running the report.
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

I know the sql code works, so I think I'm missing some sort of separator between statements or something. Is what I'm trying to do possible? And yes, I understand using temp tables like this is not good form in SQL and I should try to get it all in one query if possible. I'm learning fast, but right now I have a powerful need to "get it done".

Thanks again.

johnw

i am unclear as to why you need a temporary table to begin with. The sub-query in the insert into the temp table should work just as well, without needing a temporary table. But if you do need to go through all that, create a stored procedure, and use the the stored procedure data set option. JDBC queries should only be used on single select statements that return a recordset, not a sequence such as you have listed. That is what stored procedures are for.