Query runs in SQL Management Studio but not in Ignition

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.

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

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.

2 Likes

Thank you! I will try it and report back.

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

Thanks

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

1 Like

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.

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

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

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