# Sql query evaluation error

**URL:** <https://forum.inductiveautomation.com/t/sql-query-evaluation-error/8418>\
**Category:** Ignition\
**Created:** [May 7, 2014, 9:47am UTC](https://forum.inductiveautomation.com/t/sql-query-evaluation-error/8418 "2014-05-07T09:47:48Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![fahadfrq](https://avatars.discourse-cdn.com/v4/letter/f/85f322/32.png) [@fahadfrq](https://forum.inductiveautomation.com/u/fahadfrq)\
**Post date:** [May 7, 2014, 9:47am UTC](https://forum.inductiveautomation.com/t/sql-query-evaluation-error/8418/1 "2014-05-07T09:47:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\
**Post date:** [May 7, 2014, 7:06pm UTC](https://forum.inductiveautomation.com/t/sql-query-evaluation-error/8418/2 "2014-05-07T19:06:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [May 7, 2014, 7:49pm UTC](https://forum.inductiveautomation.com/t/sql-query-evaluation-error/8418/3 "2014-05-07T19:49:12Z")

</div>

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](http://www.inductiveautomation.com/forum/viewtopic.php?t=5920) problem.

---

<div class="post-metadata">

**Author:** ![fahadfrq](https://avatars.discourse-cdn.com/v4/letter/f/85f322/32.png) [@fahadfrq](https://forum.inductiveautomation.com/u/fahadfrq)\
**Post date:** [May 8, 2014, 3:50am UTC](https://forum.inductiveautomation.com/t/sql-query-evaluation-error/8418/4 "2014-05-08T03:50:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![george](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/george/32/14066_2.png) [@george](https://forum.inductiveautomation.com/u/george)\
**Post date:** [May 9, 2014, 2:55pm UTC](https://forum.inductiveautomation.com/t/sql-query-evaluation-error/8418/5 "2014-05-09T14:55:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\
**Post date:** [May 9, 2014, 3:23pm UTC](https://forum.inductiveautomation.com/t/sql-query-evaluation-error/8418/6 "2014-05-09T15:23:51Z")

</div>

Same is true for Ignition 7.5.11 (b1526)

---

<div class="post-metadata">

**Author:** ![fahadfrq](https://avatars.discourse-cdn.com/v4/letter/f/85f322/32.png) [@fahadfrq](https://forum.inductiveautomation.com/u/fahadfrq)\
**Post date:** [May 11, 2014, 5:38am UTC](https://forum.inductiveautomation.com/t/sql-query-evaluation-error/8418/7 "2014-05-11T05:38:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\
**Post date:** [May 13, 2014, 10:31pm UTC](https://forum.inductiveautomation.com/t/sql-query-evaluation-error/8418/8 "2014-05-13T22:31:58Z")

</div>

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,