I recently came across a strange issue where the user executes the query in SQL Server Management Studio but does not return any row.
The query returns the message – Commands completed successfully.
If we use SET NOEXEC ON at the query level, then SQL Server does not execute it and terminates query after compilation phase. However, SSMS was not returning output for any session or query in this case.
How do we solve this? You can set the advanced execution setting in SSMS that is applicable for all connections.
In the SSMS, navigate to Tools-> Query Execution -> SQL Server -> Advanced and uncheck SET NOEXEC.
Click Ok and rerun the query in a new connection of SSMS. You get the results as shown below.
