GatewayException while running a SQL Server stored procedure

Ignition
1

Hi everybody.

We have a simple stored procedure that successfully return a recordset, like this:

Inside Ignition we bind a dataset to this SP in this manner:

At this point Ignition systematically raise the following error:

[size=85]java.lang.Exception: Error running query:
SQLQuery(query=EXEC dbo.[sp14012IndicatorofTypeOfOrder] ‘2015-01-01’, ‘2015-12-12’, database=IgnitionSQL)@6000ms
On: Indicators.Root Container.dtsKpiOrderType
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:317)
at java.lang.Thread.run(Unknown Source)
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:328)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:302)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:259)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:719)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:688)
at com.inductiveautomation.factorypmi.application.gateway.SQLQuery.execute(SQLQuery.java:48)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:690)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.access$500(QueryManager.java:663)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:314)
… 1 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:708)
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:343)
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:769)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1125)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1059)
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:97)
at org.eclipse.jetty.server.Server.handle(Server.java:497)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:248)
at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:610)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:539)
at java.lang.Thread.run(null)

Ignition v7.7.4 (b2015033012)
Java: Oracle Corporation 1.8.0_66[/size]

We absolutely don’t understand why!
Thanks in avance.

2

One thing might be execute rights on the stored procedure.
I see in the SQL Studio you are logged in as SA, do you also use SA with the Ignition connection?

If not, make sure the account you use has execute rights on that stored procedure.

3

Hi MMaynardUSG.

Of course: SQL server and Ignition share the same connection properties.
In fact we have several queries and SP that successfully works fine.

Diego.

4

Does it execute properly in the Tools-Database Query Browser?

5

Try this with database browser tool inside the designer…?

6

Hi.

Very strange: running the SP inside the Ignition Database Query Browser we see the number of rows instead the dataset grid:

But after setting "set nocount on/off " inside the SP, Ignition magically starts to works fine.
Note that after the SP modification the Ignition Database Query Browser raise the following error:

GatewayException: SQL error for “EXEC dbo.[sp14012IndicatorofTypeOfOrder] ‘2015-01-01’, ‘2015-12-12’”: A result set was generated for update.
caused by SQLServerException: A result set was generated for update.

Bit it doesn’t matter because we test our DB only with MS SQL Server Management Studio.

Diego.

1 Like
7

Got to love the forums. This post may have been 2015, but it just fixed a prickly issue we were running into with calling a stored procedure that creates temp tables and then returns a resultset.