The following query runs fine in SQL Server Management Studio; but when i try to run it in Ignition it generates the following error:
“Error running query: A result set was generated for update.”
The server contains several databases, so setting the nocount option in server settings would probably affect the other databases running on the server.
Ref: viewtopic.php?f=25&t=5920
I have also tried to use “SET NOCOUNT OFF;” in the first query line, with no results.
-- Decleare the start and end dates
DECLARE @tStart datetime;
DECLARE @tStop datetime;
SET @tStart = '2013-04-25 14:00:00';
SET @tStop = '2013-04-25 15:30:00';
-- Variable that holds the indexID to the row closest to the start time
DECLARE @rStart int;
-- Set the content of the variable
SET @rStart = (
SELECT TOP 1 [nordlaks_produksjon_slakteri_sloyelinje_produksjonstall_ndx]
FROM [IgnitionHistorian].[dbo].[Nordlaks_Produksjon_Slakteri_Sloyelinje_Produksjonstall]
WHERE Datediff(SECOND, @tStart, t_stamp) > 0
ORDER BY Datediff(SECOND, @tStart, t_stamp)
);
-- Fetches the first row after the end time and merges it with the first row after the start time,
-- Then calculates production count.
SELECT TOP 1
r2.[t_stamp] AS From,
r1.[t_stamp] AS To,
r1.[M1Total] - r2.[M1Total] AS [M1Prod],
r1.[M2Total] - r2.[M2Total] AS [M2Prod],
r1.[M3Total] - r2.[M3Total] AS [M3Prod],
r1.[M4Total] - r2.[M4Total] AS [M4Prod],
r1.[M5Total] - r2.[M5Total] AS [M5Prod],
r1.[M6Total] - r2.[M6Total] AS [M6Prod],
r1.[M7Total] - r2.[M7Total] AS [M7Prod],
r1.[M8Total] - r2.[M8Total] AS [M8Prod]
FROM
[IgnitionHistorian].[dbo].[Nordlaks_Produksjon_Slakteri_Sloyelinje_Produksjonstall] r1
LEFT JOIN [IgnitionHistorian].[dbo].[Nordlaks_Produksjon_Slakteri_Sloyelinje_Produksjonstall] r2
ON r2.[nordlaks_produksjon_slakteri_sloyelinje_produksjonstall_ndx] = @rStart
WHERE
Datediff(SECOND, @tStop, r1.t_stamp) > 0
ORDER BY
Datediff(SECOND, @tStop, r1.t_stamp)
