I am at a loss to solve a problem. I have a SQL stored procedure with 114 +/- variables. Whatever the last variable is, when I try to let Ignition execute it, I get the error “Error registering parameter ‘xxxxxx’. Verify that the parameter name and type are correct. The index 114 is out of range.”
The values from the PLC are currently 0. I removed the last one and the new last one becomes the issue (index 113). I added it back plus another variable and it became the issue (index 115).
Those 3 are:
@SealingCapW3SettingMaxDistance real,
@Status smallint OUTPUT,
@NothingHere int OUTPUT
Is there a limit to how many variables or characters can be sent from Ignition to a stored procedure? I have several strings in the beginning.
My stored procedure doesn’t seem to even start (first command is an update which doesn’t happen). Shouldn’t be permission problem because the 2 I setup before it have the same permissions and I used them to create this one. I run the stored procedure in SQL Management Studio and it works and shows no errors.
Ignition 7.9.1 & SQL Server 2012
Can you create a test procedure with less variables, and confirm that you’re able to execute any stored procedures? How are you executing the call; are you using the system.db.createSProcCall syntax, or just directly executing the statement with runUpdateQuery("EXEC myprocedure") or something similar? Depending on which one you’re using, try using the other.
If you can’t get any stored procedure calls working from Ignition, try updating the JDBC driver. The publicly available one from Microsoft ( https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server ) will work fine in Ignition - although if you’re using Windows authentication make sure you update the sqljdbc_auth.dll file as well.
If the only procedure that isn’t working is when you call the full, original procedure (with 114 parameters) and you’re calling it with the scripting function, would it be possible to post a sanitized version of your code? Alternately, you can get in contact with Support and we can take a look at things.
It is the only one not working. I have been systematically removing variables to see if I could isolate it, and found that my strings are the culprit. But I still don’t know why. They are empty, so now I am trying to get data into them and see what happens.
The call is a Stored Procedure Group transaction in Ignition. So, I am not sure which one of your examples that it uses.
Thanks!
If it’s a transaction group, then I’d say updating the JDBC is a good thing to try. You can also try setting the ‘gateway.database.updates’ logger to TRACE for a moment, triggering the group, then resetting the logger. There will likely be a lot of messages logged, but at least one of them should have some indication of the actual syntax of the group call.
Thanks for that tip, I will use that in the future.
The culprit was one of my stored procedure tags was not being called (I had accidentally tied two OPC items (in my strings) to the same SQL tag (copy and paste error!) and never saw the error box for that, until now). So it is solved with all 114 tags now that I corrected the one tag.