Stored procedure not returning a result set?

Ignition
1

Hi there,

I have a stored procedure call from a table in my Ignition program. I have done this for other tables and had positive results. The stored procedure in this instance works when I execute it in MS SQL Server (2008), but not from Ignition. I get a “Gateway Error 500: The statement did not return a result set.” Note, the @MonthName variable isn’t being used yet… I haven’t added the logic to do month filtering yet.

This is the query in the data binding:

[dbo].[usp_MonthlySOAMetrics] {Root Container.Container 3.Month_SOA.selectedValue}

This is the contents of the procedure which pulls together data from several tables:

@MonthName varchar(15)
WITH EXEC AS CALLER
AS
CREATE TABLE #Metrics([ShiftName] VARCHAR(12) ,[t_stamp] DATETIME, [DownTime] INT, [Available_Time] INT, [Goal] INT, [Parts] INT,[Day_of_the_week] VARCHAR(12), PRIMARY KEY(ShiftName,t_stamp))

INSERT INTO #METRICS
SELECT ‘FORX_DAY’, [t_stamp], [DownTime], [Available_Time], [Goal], [Parts], [Day_of_the_week] from Metrics_FORX_Day —WHERE CONVERT(VARCHAR(12), DATEADD(DD, 0,t_stamp), 101) = CONVERT(VARCHAR(12), SOA_Summary.ForDate, 101)
UNION
SELECT ‘FORX_SWING’, [t_stamp], [DownTime], [Available_Time], [Goal], [Parts], [Day_of_the_week] from Metrics_FORX_Swing
UNION
SELECT ‘ES_DAY’, [t_stamp], [DownTime], [Available_Time], [Goal], [Parts], [Day_of_the_week] from Metrics_ES_Day
UNION
SELECT ‘ES_SWING’, [t_stamp], [DownTime], [Available_Time], [Goal], [Parts], [Day_of_the_week] from Metrics_ES_Swing
UNION
SELECT ‘PSD_DAY’, [t_stamp], [DownTime], [Available_Time], [Goal], [Parts], [Day_of_the_week] from Metrics_PSD_Day
UNION
SELECT ‘PSD_SWING’, [t_stamp], [DownTime], [Available_Time], [Goal], [Parts], [Day_of_the_week] from Metrics_PSD_Swing
UNION
SELECT ‘SHOX_DAY’, [t_stamp], [DownTime], [Available_Time], [Goal], [Parts], [Day_of_the_week] from Metrics_SHOX_Day
UNION
SELECT ‘SHOX_SWING’, [t_stamp], [DownTime], [Available_Time], [Goal], [Parts], [Day_of_the_week] from Metrics_SHOX_Swing

SELECT
SOA_Summary.ShiftName,
SUM(CONVERT(DECIMAL,(STR(ISNULL(StandardHours, 0), 10, 2)))) AS [Standard Hours],
SUM(CONVERT(DECIMAL,(STR(ISNULL(ActualHours, 0), 10, 2)))) AS [Actual Hours],
STR(AVG(CONVERT(DECIMAL,(ISNULL(SOA, 0)))),10,2) AS [% SOA]
FROM SOA_Summary INNER JOIN #METRICS ON CONVERT(VARCHAR(12), DATEADD(DD, 0,t_stamp), 101) = CONVERT(VARCHAR(12), SOA_Summary.ForDate, 101) AND #Metrics.ShiftName = SOA_Summary.ShiftName
WHERE
(t_stamp >= DATEADD(MONTH, - 1, GETDATE()-1)) AND (t_stamp <= GETDATE() + 1)
GROUP BY SOA_Summary.ShiftName

Is there something here that is not compatible with Ignition? My other stored procedures work flawlessly.

Thanks!

Adam S

2

Where are you executing this from? Wherever it is, it expects the statement to be a “select” statement, instead of something that only updates the db (and doesn’t return anything).

If you’re using other procedures in this way, it’s likely they return a result (even if it’s just the RowCount [rows affected]), which keeps it happy.

The solution will depend on where it is you’re trying to call this from. If you can call it from a script, use the “system.db. runUpdateQuery” function. If from a SQLTag, you can use the expression function “executeUpdateQuery”. If in a normal expression binding, you may need to use the scripting function, wrapped in the “runScript” expression function.

Probably more than a bit confusing, but I hope this helps a bit!

Regards,

3

I found another topic in here " Running Totals / SQL Temp Tables " that added “SET NOCOUNT ON” to the stored procedure code. This seemed to fix the problem. The other poster’s problem was very similar to mine apparently…

Thanks for the help!