SQL CTE Not Returning Results

[code_skin]

Do you get any errors when you run this in the DB Browser?

I suspect Ignition is interpreting your query as an update and not a select.

You could try this solution

Microsoft SQL Server - "Result set was generated for update"

If using Ignition (or FactorySQL/PMI, for that matter) with SQL Server and you run into the following error in various places: you need to turn off the “no count” option for the server. It appears that the different versions of SQL Server are inconsistent as to whether it is on by default. To change it: Go into the SQL Server management console Right-click on the server and select “properties”. Select “connections” Under “Default Connection Options”, turn off “no count”. The change will on…

The solution below suggests running runQuery which you are doing so if the No Count Off solutions doesn't work for you then you'll probably have to wrap it in a Stored Procedure as recommended below or use a View and call that.

A result set was generated for update - SQL Query

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,