Sql queries containing temp tables or table variables

Ignition
1

I’m experimenting problems when executing SQL queries containing either temp tables or variable tables, this queries run perfectly on the SQL server, however in Ignition I get this message:

Exception: Error running query:
SQLQuery(query=

CREATE TABLE #kk(t_stamp datetime, EA_FUERZA_P5 bigint,Incremento bigint)
INSERT INTO #kk SELECT t_stamp,EA_FUERZA_P5,Incremento=
LEAD(EA_FUERZA_P5) OVER (ORDER BY t_stamp)-EA_FUERZA_P5
FROM Consumos

SELECT DATEPART(Hour,t_stamp),SUM(Incremento)
FROM #kk
WHERE DAY(t_stamp)=DAY(‘2013-4-9’)
GROUP BY DATEPART(Hour,t_stamp)

DROP TABLE #kk, database=TORRELAGUNA_HIST)@5000ms
On: Main Window.Root Container.Table 1.data
caused by GatewayException: La instrucción no devolvió un conjunto de resultados.
caused by SQLServerException: La instrucción no devolvió un conjunto de resultados.

Ignition v7.5.6 (b1317)
Java: Oracle Corporation 1.7.0_17

I need to clarify as soon as possible if these tables are supported in Ignition, since they’re vital for our development.

2

They are supported. There is a difference of running that query in Ignition versus the database. You should try to make a stored procedure for that function. You might be able to put:SET NOCOUNT ONat the top of your query.