SQL Exec.command in a query tag in Ignition timing out

Ignition
1

I have a projecting command that is custom wrote for our SQL server that I’m trying to attach to a tag. It takes roughly 1.5-2 minutes in SSMS to run. When I pass the command in to the tag it times out after 1 minute. I’ve tried different scan classes with the same issue. Is there a way to increase tag timeout?

2

Well, the real answer is to find out why your query is taking 1-2 minutes to run - that’s absolutely egregious.

If you just need a workaround, then don’t try to run the SQL query from a tag. Use a periodic gateway script to run the query (optionally, asynchronously) and write the results to the tag. Consider using an additional sentinel tag to make sure slow queries don’t end up overlapping each other - that’s a good way to kill Ignition, your database, or both.

1 Like
3

It’s a rather robust exec command, not a typical select * from … type of script thats projecting inventory usage based on forecast to establish weeks on hand etc. I agree typically a SQL query that long is a big no-no but it’s unique. So write it to the gateway, but how do I pass that info to a tag?

4

system.tag.write() ?

1 Like
5

Just learning the basics of scripting my man, I’m not sure exactly how to write to the tag, the SQL script that I want to pass through…if that makes sense. Like I don’t know how to write it in script to write to the tag “Forecast Components” the results of the SQL command.

6

here is the link to Ignition Scripting Functions for v7.9. This is a go to page for syntax and examples. Your code will be like so using a scalar named query for brevity…

result = system.db.runNamedQuery("Name",{params})
system.tag.write("Folder/tagName", result)
2 Likes
7

Thanks man!

1 Like
8

So I’m just working in a script console window the following:

call = system.db.createSProcCall("GetForecastData")
call.registerInParam("@Include", system.db.VARCHAR, "ACA%")
system.db.execSProcCall(call)
results = call.getResultSet()
for row in system.dataset.toPyDataSet(results):
	print list(row)

I have a parameter called “@Include” in the stored procedure. Running the script without the parameter it times out in the script console, but when I try to put in the parameter it just errors out because it can’t take the parameter. I’m sorry to be green, but I have no idea how to remedy. Any assistance?

9

Pretty sure you don’t wan’t the ‘@’ symbol when calling registerInParam().

1 Like
10

Gotcha, I tried both ways and same error, but I’ll correct. Thanks for the heads up though my man!

1 Like
11

You are also going to have to increase your project communications timeout, and your query timeout.

12

I’ve done that as far as I know in the project properties and it still times out at 1 minute; so I’m not sure where else I should set that

13

Untitled

This is a screenshot from SSMS, this stored procedure runs in just under a second. I’m just not sure how to plug this into Ignition haha.

14

Can you show the SQL for the Stored Procedure? The JDBC driver can be a little picky in this situation.

15

Untitled
Untitled964×322 64.6 KB

This is where I’m at currently and it bombs with the parameter. If I take out the parameter it goes past 1 minute and times out

16

That is the script, I’m looking for the SQL for the Stored Procedure from SSMS. I understand if you cant show it that’s why I asked if you could.

How you write the script is heavily dependent on what the SP looks like. Should be something like:

Select [DATA] from [dbo].[TableName] WHERE [FilterColumn] = @include
17

This is a stored procedure in SQL itself, there’s not a select statement to run each time.

18

If you have the correct permissions, you can find the SP in SSMS, right click on it and select modify, this will show you the SQL that is being run when the Stored Procedure is called.

From what you have posted it seems that you expect the stored procedure to be returning a list, so there is undoubtedly a SELECT statement of some type in the stored procedure.

I have provided a snippet from my own environment as an example:

image