system.db.execSProcCall Issue

I am trying to get the system.db.execSProcCall function to work. No matter what I do it fails. Below is the error that is returned.

Java Traceback:
Traceback (most recent call last):
  File "<input>", line 9, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:497)

	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.execSProcCall()


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

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

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:497)

	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)

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

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

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

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

	at org.python.pycode._pyx52.f$0(<input>:11)

	at org.python.pycode._pyx52.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:1687)

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

	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:605)

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

	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.execSProcCall()

	... 26 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: The value is not set for the parameter number 2.

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

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

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

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:945)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._call(ClientDBUtilities.java:346)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:495)

	... 24 more

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 2.

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

	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildParamTypeDefinitions(SQLServerPreparedStatement.java:426)

	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildPreparedStrings(SQLServerPreparedStatement.java:379)

	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:554)

	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)

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

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

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

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

	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:503)

	at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)

	at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)

	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.execute(SRConnectionWrapper.java:996)

	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._callSProc(GatewayDBUtilities.java:312)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.CallSProc.call(CallSProc.java:45)

	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(null)

	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)

	at java.lang.reflect.Method.invoke(null)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:225)

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

	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:86)

	at org.eclipse.jetty.servlet.ServletHolder$NotAsyncServlet.service(ServletHolder.java:1391)

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

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

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

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

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

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)

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

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)

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

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)

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

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

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)

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

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

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

	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)

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

	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)

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

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

	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)

	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)

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

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

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

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

	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)

	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)

	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)

	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)

	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)

	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)

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

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)

	at java.lang.Thread.run(null)

Traceback (most recent call last):
  File "<input>", line 9, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:497)

	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.execSProcCall()

My script that I am executing in the scripting console looks like this.

# Set up the call to the stored procedure
call = system.db.createSProcCall("dbo.sp_CreateBatchID")
# Configure the input parameters
call.registerInParam("@mixMONum", system.db.VARCHAR, "M12345")
call.registerInParam("@packMONum", system.db.VARCHAR, "P12345")
# Configure the output parameter
call.registerOutParam("@result", system.db.INTEGER)
# Call the stored procedure
system.db.execSProcCall(call)
# Get the result from the stored procedure
result = call.getOutParamValue("@result")

My stored procedure in the database looks as follows.

ALTER PROCEDURE [dbo].[sp_CreateBatchID] 
	-- Add the parameters for the stored procedure here
	@mixMONum as varchar(10),
	@packMONum as varchar(10),
	@result as int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Determine if this mix and packing MO number already exists in the batchID table
	if not exists (select 1 from OTI_BatchID where mixMONum = @mixMONum and packMONum = @packMONum)
		begin
			-- Record doesnt exists so insert it
			insert into OTI_BatchID
				values(@mixMONum, @packMONum)
			-- return success
			set @result = 1
		end
	-- Record already exists so return a failure
	else
		set @result = 99
	
END

I am running SQL Express 2019 along with Ignition 8.1.9. Does anyone have any idea what could be wrong?

Thanks in advance.

To provide some feedback. I was able to get this to work. It appears that my DB user that I am using did not have the execute permission. To solve this I created a new database role:

CREATE ROLE db_executor;

Then I assigned the execute permission to this role:

GRANT EXECUTE TO db_executor;

Once that was completed, then assign this new role to the database user that is used to make the connection to the DB.

Interesting that it was a role issue because the error I am seeing in your stack trace

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 2.

made it sound like you were just calling it wrong. Nothing changed in your script at all?