A result set was generated for update - SQL Query

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)

Where are you trying to run it from? Wherever it is, it is trying to run it as an “update”, when you really want it to run like a select. You’ll likely need to use the system.db.runQuery scripting function or something similar. And in that case, it’ll probably be easier to wrap it up in a stored procedure and just call that.

Regards,

I was running the query from the query console in the designer.
Upon reading your post i tried to move the query to the component binding (where it was supposed to run anyways).

And guess what; everything worked beautifully :slight_smile:
Been fiddling with this in the query console for days, and that was the only problem :laughing:

Yeah, I suspected that. Behind the scenes we have to decide whether it’s going to be a query or an update, and the query browse just does a basic check on the first keyword to decide, which doesn’t work for things like this.

Glad you got it working,