# Getting error while creating store procedure by named query

**URL:** <https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542>\
**Category:** Ignition\
**Created:** [June 18, 2025, 6:01pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542 "2025-06-18T18:01:30Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![dhiraj.pawar](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@dhiraj.pawar](https://forum.inductiveautomation.com/u/dhiraj.pawar)\
**Post date:** [June 18, 2025, 6:01pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/1 "2025-06-18T18:01:30Z")

</div>

Hello team,  
Im getting below error while creating store procedure by named query please help us with resolution.  
Error

```python
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

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

java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(

```

regards

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [June 18, 2025, 6:05pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/2 "2025-06-18T18:05:02Z")

</div>

Really? You're not going to show us the code?

---

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [June 18, 2025, 9:36pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/3 "2025-06-18T21:36:05Z")

</div>

Nice Error!

---

<div class="post-metadata">

**Author:** ![michael.flagler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.flagler/32/26272_2.png) [@michael.flagler](https://forum.inductiveautomation.com/u/michael.flagler)\
**Post date:** [June 18, 2025, 10:37pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/4 "2025-06-18T22:37:33Z")

</div>

You need to show your code but most likely you should be using runNamedQuery, not runUpdateQuery.

> **[system.db.runNamedQuery | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/appendix/scripting-functions/system-db/system-db-runNamedQuery)**
>
> Runs a named query and returns the results.

---

<div class="post-metadata">

**Author:** ![dhiraj.pawar](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@dhiraj.pawar](https://forum.inductiveautomation.com/u/dhiraj.pawar)\
**Post date:** [June 19, 2025, 4:56am UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/5 "2025-06-19T04:56:12Z")

</div>

hello,  
I have attached code for refence.  
I have used runUpdateQuery

```python
#Convert Procedure name into SQL Format
		procedure_nameWithFormat="'"+(procedure_name)+"'"
		
		ListCombinationParam=[]
		# Convert Multiple Parameters into SQL Format
		for ParamName,ParamType in (Parameters):
			CombinationParam= ParamName +' '+ParamType
			ListCombinationParam.append(CombinationParam)
			ParamWithComma=','.join(ListCombinationParam)
		ParamWithComma=str(ParamWithComma)+ ',@Result Decimal(16,4) output'
		
		# Drop the stored procedure if it exists
		drop_sql = """
		IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = """+str(procedure_nameWithFormat)+""")
		   DROP PROCEDURE """+procedure_name+""";
		"""
				
		# Create Store Procedure with Passing dynamic paramters
		create_sql = """
		DECLARE @sql NVARCHAR(MAX);
		SET @sql = '
		CREATE PROCEDURE """+str(procedure_name)+"""
			"""+ParamWithComma+"""
			
		AS
		BEGIN
		   SET NOCOUNT ON;
		    """+str(Query)+"""
		   Set @Result =( """+str(Calculation)+""" ) """+"""
		   return @Result """ + """
		END
		';
		EXEC (@sql);
		"""
		
		
		# First drop, then create
	
		DropSP= system.db.runUpdateQuery(drop_sql, DatabaseName)
# DropSP= system.db.runPrepQuery(drop_sql)
		
		CreateSP= system.db.runUpdateQuery(create_sql, DatabaseName)
# CreateSP= system.db.runPrepQuery(create_sql)
	
		print CreateSP
	   	print("Message: "+str("Stored procedure created successfully."))
		# system.gui.messageBox("Stored procedure created successfully.")
		return True

```

---

<div class="post-metadata">

**Author:** ![michael.flagler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.flagler/32/26272_2.png) [@michael.flagler](https://forum.inductiveautomation.com/u/michael.flagler)\
**Post date:** [June 19, 2025, 5:55am UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/6 "2025-06-19T05:55:34Z")

</div>

You should remove "named query" from your title and description then since you're not using named queries.

You should test this by printing the drop\_sql and create\_sql variables to make sure they're showing the text you're expecting.

You'll also want to include your full script, possibly an example of how you're calling it, and the full error message showing the line you're getting the error on.

My guess right now is that your create\_sql is overly complex. I think if you simplify it to just start with the CREATE PROCEDURE and end with the END and skip the DECLARE, SET, and EXEC commands, that may fix your problem.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [June 19, 2025, 10:46am UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/7 "2025-06-19T10:46:50Z")

</div>

Got to say this seems ill-advised. Why drop and create stored procedures on the fly? Stored procedures usually are just made once and then called as needed. Not to mention it seems like your SP is just storing the result of a single select query? Why not just use runPrepQuery or runNamedQuery? You're going to give yourself headaches doing things like this (if this issue doesn't already count as one).

---

<div class="post-metadata">

**Author:** ![michael.flagler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.flagler/32/26272_2.png) [@michael.flagler](https://forum.inductiveautomation.com/u/michael.flagler)\
**Post date:** [June 19, 2025, 1:28pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/8 "2025-06-19T13:28:03Z")

</div>

I was trying to figure out the same thing, but didn't know what all they were injecting into the procedure with their Query parameter. They could be trying to automate building out a database and stored procedures for manipulating tables, so didn't question the why and only focused on what could possibly be the problem.

---

<div class="post-metadata">

**Author:** ![dhiraj.pawar](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@dhiraj.pawar](https://forum.inductiveautomation.com/u/dhiraj.pawar)\
**Post date:** [June 20, 2025, 5:03am UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/9 "2025-06-20T05:03:01Z")

</div>

Hello @bkarabinchak.psi ,  
Thanks for suggestion..  
This case we are creating store procedure by UI.  
Answer for your first question is we are checking if same store procedure already created or not if created we are deleting.  
We had used "runPrepQuery " but it is also not working for us.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [June 20, 2025, 10:39am UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/10 "2025-06-20T10:39:40Z")

</div>

There's already a UI to make stored procedures though and that's your workbench for your database, in your case SSMS and it is much more appropriate with contextual highlighting and auto complete that will be completely lacking in trying to make this in Ignition vision/perspective.

You also don't want regular users to be able to modify your database in such a way. Even if the screen is limited to only administrators - if I was a DBA on this project I would just be using SSMS anyways.

If you are just trying to autogenerate the text - that makes a bit more sense, but just save it in a .sql file, bring it to ssms, you will get any syntax errors highlighted for you and then you can run it once there.

I still highly recommend against this course of action imo.

---

<div class="post-metadata">

**Author:** ![dhiraj.pawar](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@dhiraj.pawar](https://forum.inductiveautomation.com/u/dhiraj.pawar)\
**Post date:** [June 22, 2025, 4:48am UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/11 "2025-06-22T04:48:40Z")

</div>

Hello @bkarabinchak.psi ,  
Basically, we are generating store procedure by application. We are able to create it same script generate only, but sometimes it's getting same error, even same generated query executing & created in SSMS.

We didnt get error if you help that it will be helpfull

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [June 23, 2025, 12:12pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/12 "2025-06-23T12:12:48Z")

</div>

I would argue if you're able to generate the stored procedures by application its likely the stored procedure could just accept a few additional parameters to handle all your needs.

---

<div class="post-metadata">

**Author:** ![dhiraj.pawar](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@dhiraj.pawar](https://forum.inductiveautomation.com/u/dhiraj.pawar)\
**Post date:** [June 23, 2025, 1:49pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/13 "2025-06-23T13:49:21Z")

</div>

Hello @bkarabinchak.psi ,  
Can you help us how to handle it.

Please refer the

```python

print system.db.runUpdateQuery(" DECLARE @sql NVARCHAR(MAX); SET @sql = ' CREATE PROCEDURE PlannedDowntime @Int INT,@Int1 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Declare @Result_TPM Decimal(16,4) Set@Result_TPM =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'TPM' AND pdt IS NOT NULL) Declare @Result_Break Decimal(16,4) Set @Result_Break =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'BRK' AND pdt IS NOT NULL) Declare @Result_WGBT Decimal(16,4) Set @Result_WGBT =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'WGBT' AND pdt IS NOT NULL) Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) return @Result END '; EXEC (@sql);" , "ignitiondb", True)

```

I have checked same query into SSMS & it creating store procedure

```python

CREATE PROCEDURE PlannedDowntime @Int INT,@Int1 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Declare @Result_TPM Decimal(16,4) Set @Result_TPM =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'TPM' AND pdt IS NOT NULL) Declare @Result_Break Decimal(16,4) Set @Result_Break =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'BRK' AND pdt IS NOT NULL) Declare @Result_WGBT Decimal(16,4) Set @Result_WGBT =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'WGBT' AND pdt IS NOT NULL) Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) return @Result END		

```

Error in script consol:

```python

Java Traceback:
Traceback (most recent call last):
  File "<input>", line 14, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188)
	at jdk.internal.reflect.GeneratedMethodAccessor126.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery( DECLARE @sql NVARCHAR(MAX); SET @sql = ' CREATE PROCEDURE PlannedDowntime @Int INT,@Int1 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Declare @Result_TPM Decimal(16,4) Set @Result_TPM =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'TPM' AND pdt IS NOT NULL) Declare @Result_Break Decimal(16,4) Set @Result_Break =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'BRK' AND pdt IS NOT NULL) Declare @Result_WGBT Decimal(16,4) Set @Result_WGBT =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'WGBT' AND pdt IS NOT NULL) Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) return @Result END '; EXEC (@sql);, Transaction datasource unknown, true, true)

	at org.python.core.Py.JavaError(Py.java:545)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188)
	at jdk.internal.reflect.GeneratedMethodAccessor126.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at org.python.core.PyReflectedFunction. __call__ (PyReflectedFunction.java:190)
	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction. __call__ (ScriptManager.java:553)
	at org.python.core.PyObject. __call__ (PyObject.java:512)
	at org.python.core.PyObject. __call__ (PyObject.java:517)
	at org.python.pycode._pyx236.f$0(<input>:14)
	at org.python.pycode._pyx236.call_function(<input>)
	at org.python.core.PyTableCode.call(PyTableCode.java:173)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1703)
	at org.python.core.Py.exec(Py.java:1747)
	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)
	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:628)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:616)
	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.Exception: Error executing system.db.runUpdateQuery( DECLARE @sql NVARCHAR(MAX); SET @sql = ' CREATE PROCEDURE PlannedDowntime @Int INT,@Int1 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Declare @Result_TPM Decimal(16,4) Set @Result_TPM =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'TPM' AND pdt IS NOT NULL) Declare @Result_Break Decimal(16,4) Set @Result_Break =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'BRK' AND pdt IS NOT NULL) Declare @Result_WGBT Decimal(16,4) Set @Result_WGBT =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'WGBT' AND pdt IS NOT NULL) Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) return @Result END '; EXEC (@sql);, Transaction datasource unknown, true, true)
	... 25 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Connection Error: Transaction "true" is closed.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:360)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:334)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runUpdateQuery(ClientDBUtilities.java:267)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:181)
	... 23 more
Caused by: java.sql.SQLException: Transaction "true" is closed.
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getTx(DatasourceManagerImpl.java:292)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getTransactionConnection(DatasourceManagerImpl.java:299)
	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:99)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:50)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:435)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:523)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:598)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1580)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1553)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.server.Server.handle(Server.java:563)
	at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:287)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:421)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:390)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:277)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.run(AdaptiveExecutionStrategy.java:199)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149)
	at java.lang.Thread.run(null)
Traceback (most recent call last):
  File "<input>", line 14, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188)
	at jdk.internal.reflect.GeneratedMethodAccessor126.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery( DECLARE @sql NVARCHAR(MAX); SET @sql = ' CREATE PROCEDURE PlannedDowntime @Int INT,@Int1 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Declare @Result_TPM Decimal(16,4) Set @Result_TPM =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'TPM' AND pdt IS NOT NULL) Declare @Result_Break Decimal(16,4) Set @Result_Break =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'BRK' AND pdt IS NOT NULL) Declare @Result_WGBT Decimal(16,4) Set @Result_WGBT =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'WGBT' AND pdt IS NOT NULL) Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) return @Result END '; EXEC (@sql);, Transaction datasource unknown, true, true)

```

Please help to resolve issue

Regards,  
Dhiraj Pawar

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [June 23, 2025, 1:54pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/14 "2025-06-23T13:54:31Z")

</div>

JBDC drivers are meant for single statements and anything other than that is liable to not work as expected.

You are setting a variable (this can sometimes work but isn't supported and can fail or mess things up).

Not only that you are setting a variable and then dynamically executing SQL inside your query.

Just take this part

```python
CREATE PROCEDURE PlannedDowntime @Int INT,@Int1 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Declare @Result_TPM Decimal(16,4) Set@Result_TPM =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'TPM' AND pdt IS NOT NULL) Declare @Result_Break Decimal(16,4) Set @Result_Break =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'BRK' AND pdt IS NOT NULL) Declare @Result_WGBT Decimal(16,4) Set @Result_WGBT =(SELECT SUM (Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = Convert(Date,(GetDate())) AND pdtCode = 'WGBT' AND pdt IS NOT NULL) Set @Result =( ISNULL(@Result_TPM,0) + ISNULL(@Result_Break,0) + ISNULL(@Result_WGBT,0) ) return @Result END

```

And go run it in SSMS. I don't see the utility of what you are doing here trying to do this through Ignition.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [June 23, 2025, 2:06pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/15 "2025-06-23T14:06:18Z")

</div>

You are attempting to run a SQL script, and those are not supported by JDBC, even though they do work in some cases.

You should just create the Stored Procedure in SSMS. Really.

Meanwhile the error you’re getting is because you’ve provided the name “True” as the Transaction name, but that transaction doesn’t exist or is being closed.

Look closely at the function parameters and be sure to provide appropriate values for those parameters.

Again, I can’t express this enough, there is a reason that everyone is telling you not to do this the way you’re trying. Even if it does work, it will be very fragile, and subject to breaking at anytime.

---

<div class="post-metadata">

**Author:** ![dhiraj.pawar](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@dhiraj.pawar](https://forum.inductiveautomation.com/u/dhiraj.pawar)\
**Post date:** [June 23, 2025, 2:20pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/16 "2025-06-23T14:20:59Z")

</div>

Hello @lrose ,  
Thanks for suggestion i will check Transaction name.

As for another suggestion, This is only way because we want to create these mentioned operation in SQL query which is dynamically set by UI.  
These operation or sequence of store procedure will execute at trigger point result it store required result into another DB table.

If there is anyother way please suggest

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 23, 2025, 2:46pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/17 "2025-06-23T14:46:38Z")

</div>

> [@dhiraj.pawar](#):
>
> This is only way because we want to create these mentioned operation in SQL query which is dynamically set by UI.

Using your sample, what portions are coming from the UI?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [June 23, 2025, 2:49pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/18 "2025-06-23T14:49:08Z")

</div>

> If there is anyother way please suggest

What exactly is your end goal? From start to finish, how is the functionality of the feature supposed to work - without menitoning stored procedures what ideally should happen here

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [June 23, 2025, 5:38pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/19 "2025-06-23T17:38:17Z")

</div>

> [@dhiraj.pawar](#):
>
> This is only way because we want to create these mentioned operation in SQL query which is dynamically set by UI.

I highly doubt that it is the “only” way. You have yet to state **why** you **need** to do this. You have only insisted it is the only way.

> [@dhiraj.pawar](#):
>
> If there is anyother way please suggest

Of course there is another way.

You could create the SP’s by hand and then call them from a script.

You could abandon the SP’s altogether, and just create the queries dynamically and call them using `system.db.runPrepQuery()` and `system.db.runPrepUpdate()`.

Or perhaps, depending on the nature of these queries you could even create Named Queries to perform the operations you need.

You’ve already spent more time trying to accomplish this unorthodox approach than it should take to implement any of those approaches.

Anytime I hear UI and database _procedure_ manipulation I get concerned about SQL injection. I can see holes where users could potentially manipulate whole databases of data.

Your approach is wrought with danger.

---

<div class="post-metadata">

**Author:** ![michael.flagler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.flagler/32/26272_2.png) [@michael.flagler](https://forum.inductiveautomation.com/u/michael.flagler)\
**Post date:** [June 23, 2025, 8:27pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/20 "2025-06-23T20:27:44Z")

</div>

I don't know if it will even be possible to do what he's asking, but I can definitely see use cases for this if you want to drop an Ignition project on a server and point it to the DB and have it set up all the necessary stored procedures automatically for you. Plus, these stored procedures may not even be for Ignition to use, but for other software to call them to do functions in the database safely.

Due to JDBC I don't know if it will be possible to even do it, but the only way to know is to try and ask others. I do believe though that the variable assignment inside the query will need to be removed and only run the CREATE PROCEDURE directly for it to even have a chance of being successful.

[Next page](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542.md?page=2)