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
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