# Query runs in SQL Management Studio but not in Ignition

**URL:** <https://forum.inductiveautomation.com/t/query-runs-in-sql-management-studio-but-not-in-ignition/16316>\
**Category:** Ignition\
**Tags:** database, scripting\
**Created:** [December 13, 2017, 10:11pm UTC](https://forum.inductiveautomation.com/t/query-runs-in-sql-management-studio-but-not-in-ignition/16316 "2017-12-13T22:11:22Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://forum.inductiveautomation.com/u/dturner)\
**Post date:** [December 13, 2017, 10:11pm UTC](https://forum.inductiveautomation.com/t/query-runs-in-sql-management-studio-but-not-in-ignition/16316/1 "2017-12-13T22:11:22Z")

</div>

I have a query that creates a temporary table and then runs a query against it. The query runs fine in Management Studio but not in Ignition. The error message is “The statement did not return a result set.”

I can include the result sets from Management Studio if that would be helpful.

```auto
Create Table #Reject_Dollars
(
	Reject_Message varchar(50),
	Reject_Count int,
	Amount Decimal(6,2)	
)

Insert Into #Reject_Dollars
Select distinct(Reject_Message) As 'Reject Message', IsNull(count(Reject_Message), 0) As 'Reject Count', sum(Convert(Decimal(6,2),Total_Cost)) * -1 As 'Dollar Amount'
From Scrap_Information
Where Date_Created >= '09-01-2017' and Date_Created <= '09-30-2017' and Reject_Message != 'NULL' and Work_Center like 'MB061'
Group By Reject_Message
order by [Reject Count] desc

Select Reject_Message, Amount
From #Reject_Dollars
Order by Amount Desc

Drop Table #Reject_Dollars

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 13, 2017, 10:13pm UTC](https://forum.inductiveautomation.com/t/query-runs-in-sql-management-studio-but-not-in-ignition/16316/2 "2017-12-13T22:13:46Z")

</div>

You cannot run scripted SQL from JDBC drivers, only single statements. You will have to create a stored procedure in your database and call it.

---

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://forum.inductiveautomation.com/u/dturner)\
**Post date:** [December 13, 2017, 10:16pm UTC](https://forum.inductiveautomation.com/t/query-runs-in-sql-management-studio-but-not-in-ignition/16316/3 "2017-12-13T22:16:06Z")

</div>

Thank you! I will try it and report back.

---

<div class="post-metadata">

**Author:** ![dcamp](https://avatars.discourse-cdn.com/v4/letter/d/a88e4f/32.png) [@dcamp](https://forum.inductiveautomation.com/u/dcamp)\
**Post date:** [December 14, 2017, 1:58pm UTC](https://forum.inductiveautomation.com/t/query-runs-in-sql-management-studio-but-not-in-ignition/16316/4 "2017-12-14T13:58:36Z")

</div>

pturmel - Is that because a “Stored Procedure” is (Server Side)?

Thanks

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 14, 2017, 2:17pm UTC](https://forum.inductiveautomation.com/t/query-runs-in-sql-management-studio-but-not-in-ignition/16316/5 "2017-12-14T14:17:16Z")

</div>

> [@dcamp](#):
>
> Is that because a "Stored Procedure" is (Server Side)?

Yes. { Plus filler for stupid 20-character post requirement. }

---

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://forum.inductiveautomation.com/u/dturner)\
**Post date:** [December 14, 2017, 11:18pm UTC](https://forum.inductiveautomation.com/t/query-runs-in-sql-management-studio-but-not-in-ignition/16316/6 "2017-12-14T23:18:49Z")

</div>

I am having an issue with calling the stored procedure via script. The script below returns “None” instead of the dataset I am looking for. No errors are being generated. Is there an input limit?

The inputs have been verified to work in Management Studio.

```auto
call = system.db.createSProcCall("usp_FirstShiftRejectInformation", "End_of_Shift")
call.registerReturnParam(system.db.INTEGER)
call.registerInParam(1, system.db.VARCHAR, "MB061")
call.registerInParam(2, system.db.VARCHAR, "MB06")
call.registerInParam(3, system.db.VARCHAR, "09-01-2017")
call.registerInParam(4, system.db.VARCHAR, "09-30-2017")
call.registerInParam(5, system.db.VARCHAR, "Dollars (USD)")

system.db.execSProcCall(call)

#Print the result to the console

print call.getResultSet()

```

I created a stored query in SQL and verified that I get the results that I

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [December 15, 2017, 12:29am UTC](https://forum.inductiveautomation.com/t/query-runs-in-sql-management-studio-but-not-in-ignition/16316/7 "2017-12-15T00:29:15Z")

</div>

Try `SET NOCOUNT ON` in your stored procedure code. MSSQL and the JDBC driver seem to have differences on how to deliver result sets.

---

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://forum.inductiveautomation.com/u/dturner)\
**Post date:** [December 15, 2017, 4:22pm UTC](https://forum.inductiveautomation.com/t/query-runs-in-sql-management-studio-but-not-in-ignition/16316/8 "2017-12-15T16:22:33Z")

</div>

Thank you!!! That was it. I set NOCOUNT to on and it worked.