Sql query evaluation error

Ignition
1

I am using Ignition version 7.6.6, although i have tested this on older versions of ignition as well.

When I create an expression item with expression type “sql query”, and the query is simply

declare @i int
set @i=1
select @i

the expression value is always “Evaluation Error”

I am using sql server 2005 enterprise edition, basically the project was upgraded from factorysql where these kind of sql expressions worked fine, but now in ignition, they do not and I assume that when we choose sql query in expression type, then any valid sql script should run.

what i found was when i use declare statement, this is happening.

2

This works fine for Ignition 7.5.11 (b1526) using SQL Server 2008.

3

Run the query in the Database Query Browser and see what the error is. If the error returned is “A result set was generated for update” then you may have this problem.

4

Ok I am testing the query in the database query browser

declare @i int
set @i=10
if @i<5
select ‘less than 5’
else
select ‘greater than 5’

I get the error “A result set was generated for update”.

Any script with more than a single select statement in it will generate this error.

“No Count” is turned off in sql server. I also added set nocount off in connection initialization commands, but still the same error comes.

This error leads to evaluation error when the script is used in sql expression.

I know that making the script into a stored procedure of user defined function solves this but there should be a way to run sql query scripts without issues directly in ignition sql expressions as well.

5

Ignition 7.6.6 - SQL Server 2012

If I run that in the DB Query Browser I get the same error.

If I use it in property binding though it works ok.

6

Same is true for Ignition 7.5.11 (b1526)

7

My question was more about how a valid sql script can be used directly in sql expression without creating a user defined function or stored procedure.

8

Hi everyone,

Yes, this has to do with how the queries get executed… the system (unfortunately) has to guess as to whether the query will return a result or not, and in this case, it chooses “not”… and then an error occurs when it does.

In 7.7, you’ll be able to select what type of query it is directly on the tag, which will fix this. Unfortunately, in the mean time, you’ll have to wrap up what you want to do into a stored procedure or function and call that.

Regards,