# GatewayException while running a SQL Server stored procedure **URL:** \ **Category:** Ignition\ **Created:** [December 15, 2015, 2:53pm UTC](https://forum.inductiveautomation.com/t/gatewayexception-while-running-a-sql-server-stored-procedure/10918 "2015-12-15T14:53:57Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Didi71](https://avatars.discourse-cdn.com/v4/letter/d/46a35a/32.png) [@Didi71](https://forum.inductiveautomation.com/u/Didi71)\ **Post date:** [December 15, 2015, 2:53pm UTC](https://forum.inductiveautomation.com/t/gatewayexception-while-running-a-sql-server-stored-procedure/10918/1 "2015-12-15T14:53:57Z")
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. ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [December 15, 2015, 3:10pm UTC](https://forum.inductiveautomation.com/t/gatewayexception-while-running-a-sql-server-stored-procedure/10918/2 "2015-12-15T15:10:49Z")
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. ---
**Author:** ![Didi71](https://avatars.discourse-cdn.com/v4/letter/d/46a35a/32.png) [@Didi71](https://forum.inductiveautomation.com/u/Didi71)\ **Post date:** [December 15, 2015, 3:19pm UTC](https://forum.inductiveautomation.com/t/gatewayexception-while-running-a-sql-server-stored-procedure/10918/3 "2015-12-15T15:19:16Z")
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. ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [December 15, 2015, 3:40pm UTC](https://forum.inductiveautomation.com/t/gatewayexception-while-running-a-sql-server-stored-procedure/10918/4 "2015-12-15T15:40:55Z")
Does it execute properly in the Tools-Database Query Browser? ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [December 15, 2015, 3:43pm UTC](https://forum.inductiveautomation.com/t/gatewayexception-while-running-a-sql-server-stored-procedure/10918/5 "2015-12-15T15:43:58Z")
Try this with database browser tool inside the designer…? ---
**Author:** ![Didi71](https://avatars.discourse-cdn.com/v4/letter/d/46a35a/32.png) [@Didi71](https://forum.inductiveautomation.com/u/Didi71)\ **Post date:** [December 15, 2015, 4:37pm UTC](https://forum.inductiveautomation.com/t/gatewayexception-while-running-a-sql-server-stored-procedure/10918/6 "2015-12-15T16:37:11Z")
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. ---
**Author:** ![jasoncoope](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jasoncoope/32/18674_2.png) [@jasoncoope](https://forum.inductiveautomation.com/u/jasoncoope)\ **Post date:** [December 8, 2023, 10:55pm UTC](https://forum.inductiveautomation.com/t/gatewayexception-while-running-a-sql-server-stored-procedure/10918/7 "2023-12-08T22:55:59Z")
> [@Didi71](#): > > "set nocount on/off " 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.