# system.db.runQuery fails when inserting into temporary table

**URL:** <https://forum.inductiveautomation.com/t/system-db-runquery-fails-when-inserting-into-temporary-table/11573>\
**Category:** Archived Topics\
**Created:** [April 25, 2016, 12:27pm UTC](https://forum.inductiveautomation.com/t/system-db-runquery-fails-when-inserting-into-temporary-table/11573 "2016-04-25T12:27:10Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![DavidWisely](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davidwisely/32/10482_2.png) [@DavidWisely](https://forum.inductiveautomation.com/u/DavidWisely)\
**Post date:** [April 25, 2016, 12:27pm UTC](https://forum.inductiveautomation.com/t/system-db-runquery-fails-when-inserting-into-temporary-table/11573/1 "2016-04-25T12:27:10Z")

</div>

I have the script shown below that tries to run an SQL query against an MS SQL database.  
The query runs fine in Management Studio.

The code fails every time with the error shown below.  
The problem line is:

```auto
insert into @timeRanges values(@baseDate, @baseDate + @interval)
```

If I get rid of the ‘Insert’ statement the rest of the query works fine.  
I suspect it’s something to do with having an ‘Insert’ statement in the ‘runQuery’ function.  
Even though one of the error lines says "The statement did not return a result set.’ It actually does!  
Any help appreciated.

Here is the script exert:

```auto
query = """declare @baseDate datetime, @interval datetime
					select @baseDate = '%s', @interval = '%s'
					
					declare @timeRanges table (beginIntervalInclusive datetime, endIntervalExclusive datetime)
					declare @i int
					set @i = 1
					while @i <= %d
					begin
						insert into @timeRanges values(@baseDate, @baseDate + @interval)
						set @baseDate = @baseDate + @interval
						set @i = @i + 1
					end
					
					SELECT
						tr.beginIntervalInclusive,
						tr.endIntervalExclusive,
						COUNT(*) AS [count]
					FROM %s join @timeRanges as tr
						on %s.[timestamp] >= tr.beginIntervalInclusive
							and %s.[timestamp] < tr.endIntervalExclusive
					GROUP BY tr.beginIntervalInclusive, tr.endIntervalExclusive
					ORDER BY tr.beginIntervalInclusive""" % (sDateStart, sInterval, int(numOfBuckets), table, table, table)
					
		groupedTotals = system.db.runQuery(query, dataDB)
```

And here is the error I get when run in the Script Console:

```auto
Java Traceback:

	at org.python.core.Py.JavaError(Py.java:495)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:335)

	at sun.reflect.GeneratedMethodAccessor350.invoke(Unknown Source)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.lang.reflect.Method.invoke(Unknown Source)

	at org.python.core.PyReflectedFunction. __call__ (PyReflectedFunction.java:186)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction. __call__ (ScriptManager.java:427)

	at org.python.core.PyObject. __call__ (PyObject.java:404)

	at org.python.core.PyObject. __call__ (PyObject.java:408)

	at org.python.pycode._pyx477.GetData$3(<module:shared.CheckWeigher.CW_Rates>:144)

	at org.python.pycode._pyx477.call_function(<module:shared.CheckWeigher.CW_Rates>)

	at org.python.core.PyTableCode.call(PyTableCode.java:165)

	at org.python.core.PyBaseCode.call(PyBaseCode.java:166)

	at org.python.core.PyFunction. __call__ (PyFunction.java:338)

	at org.python.core.PyMethod. __call__ (PyMethod.java:139)

	at org.python.pycode._pyx475.f$0(<buffer>:5)

	at org.python.pycode._pyx475.call_function(<buffer>)

	at org.python.core.PyTableCode.call(PyTableCode.java:165)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1275)

	at org.python.core.Py.exec(Py.java:1319)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:215)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:89)

	at org.python.util.InteractiveInterpreter.runsource(InteractiveInterpreter.java:70)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:417)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:405)

	at javax.swing.SwingWorker$1.call(Unknown Source)

	at java.util.concurrent.FutureTask.run(Unknown Source)

	at javax.swing.SwingWorker.run(Unknown Source)

	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.lang.Thread.run(Unknown Source)

Caused by: java.lang.Exception: Error executing system.db.runQuery(declare @baseDate datetime, @interval datetime
					select @baseDate = '2016-04-24 00:00:00:000', @interval = '1900-01-01 00:15:00:000'
					
					declare @timeRanges table (beginIntervalInclusive datetime, endIntervalExclusive datetime)
					declare @i int
					set @i = 1
					while @i <= 96
					begin
						insert into @timeRanges values(@baseDate, @baseDate + @interval)
						set @baseDate = @baseDate + @interval
						set @i = @i + 1
					end
					
					SELECT
						tr.beginIntervalInclusive,
						tr.endIntervalExclusive,
						COUNT(*) AS [count]
					FROM P70330_bags join @timeRanges as tr
						on P70330_bags.[timestamp] >= tr.beginIntervalInclusive
							and P70330_bags.[timestamp] < tr.endIntervalExclusive
					GROUP BY tr.beginIntervalInclusive, tr.endIntervalExclusive
					ORDER BY tr.beginIntervalInclusive, KEN_Packing_REP, )

	... 32 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: The statement did not return a result set.

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:327)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:301)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:258)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:803)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:767)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runQuery(ClientDBUtilities.java:185)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:331)

	... 30 more

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)

	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:794)

	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:685)

	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)

	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)

	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)

	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)

	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:620)

	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

	at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeQuery(DelegatingStatement.java:63)

	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeQuery(SRConnectionWrapper.java:711)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:98)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:76)

	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:345)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)

	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:816)

	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)

	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)

	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1156)

	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)

	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)

	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1088)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:119)

	at org.eclipse.jetty.server.Server.handle(Server.java:517)

	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:306)

	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:242)

	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:245)

	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)

	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:75)

	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:213)

	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:147)

	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)

	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)

	at java.lang.Thread.run(null)

Traceback (most recent call last):
  File "<buffer>", line 5, in <module>
  File "<module:shared.CheckWeigher.CW_Rates>", line 142, in GetData
					select @baseDate = '2016-04-24 00:00:00:000', @interval = '1900-01-01 00:15:00:000'
					
					declare @timeRanges table (beginIntervalInclusive datetime, endIntervalExclusive datetime)
					declare @i int
					set @i = 1
					while @i <= 96
					begin
						insert into @timeRanges values(@baseDate, @baseDate + @interval)
						set @baseDate = @baseDate + @interval
						set @i = @i + 1
					end
					
					SELECT
						tr.beginIntervalInclusive,
						tr.endIntervalExclusive,
						COUNT(*) AS [count]
					FROM P70330_bags join @timeRanges as tr
						on P70330_bags.[timestamp] >= tr.beginIntervalInclusive
							and P70330_bags.[timestamp] < tr.endIntervalExclusive
					GROUP BY tr.beginIntervalInclusive, tr.endIntervalExclusive
					ORDER BY tr.beginIntervalInclusive, KEN_Packing_REP, )

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:335)

	at sun.reflect.GeneratedMethodAccessor350.invoke(Unknown Source)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(declare @baseDate datetime, @interval datetime
					select @baseDate = '2016-04-24 00:00:00:000', @interval = '1900-01-01 00:15:00:000'
					
					declare @timeRanges table (beginIntervalInclusive datetime, endIntervalExclusive datetime)
					declare @i int
					set @i = 1
					while @i <= 96
					begin
						insert into @timeRanges values(@baseDate, @baseDate + @interval)
						set @baseDate = @baseDate + @interval
						set @i = @i + 1
					end
					
					SELECT
						tr.beginIntervalInclusive,
						tr.endIntervalExclusive,
						COUNT(*) AS [count]
					FROM P70330_bags join @timeRanges as tr
						on P70330_bags.[timestamp] >= tr.beginIntervalInclusive
							and P70330_bags.[timestamp] < tr.endIntervalExclusive
					GROUP BY tr.beginIntervalInclusive, tr.endIntervalExclusive
					ORDER BY tr.beginIntervalInclusive, KEN_Packing_REP, )
>>> >>> 
```