SQL SERVER – SSMS Query Command(s) completed successfully without ANY Results – Part 2

What do you do when you encounter something that you didn’t expect? A lot of times such experiences take you to a state of shock or a state of bafflement. The curiosity sometimes pushes you off the cliff to understand why certain behaviors occur. I am fortunate that I get into bizarre situations and the learning it gives is immense. No wonder such things happen because I play around with SQL Server Management options as part of learning. This learning exercise gets me into trouble and below is a classic case.

From my laptop, whatever query I run, I just receive a message like Query Command(s) completed successfully without any result. Here is an example:

SQL SERVER - SSMS Query Command(s) completed successfully without ANY Results - Part 2 no-exec-01

Long back I have written below blog post with the same subject.

SQL SERVER – SSMS Query Command(s) completed successfully without ANY Results

The first stop would be to search the blog for possible replies that might help. Later, I realized that there were few comments where readers mentioned that even after following the steps given in the above blog (SET PARSEONLY option), they were still seeing the same behavior. Whenever I get into such stage, I ask folks who might know. So, I was asked I can capture a profiler to reproduce the issue.

Below is what I shared with my friends to walk the same learning:

SQL SERVER - SSMS Query Command(s) completed successfully without ANY Results - Part 2 no-exec-02

In the profiler, we have the answer. It’s been due to the fact that NOEXEC option, it SET to ON. The next question was to identify a possible reason and how I landed into this in the first place. There are two places we can set it in Management Studio.

Query Level (for current query window in SSMS):

In SQL Server Management Studio, right click on query window and choose “Query options”

SQL SERVER - SSMS Query Command(s) completed successfully without ANY Results - Part 2 no-exec-03

And over there, we can set option for query level.

SQL SERVER - SSMS Query Command(s) completed successfully without ANY Results - Part 2 no-exec-04

SSMS Level (For all query windows):

In SSMS, we can go to “Tools” > “Options” and chose Advanced as shown below.

SQL SERVER - SSMS Query Command(s) completed successfully without ANY Results - Part 2 no-exec-05

If any the option is checked, we would see the same behavior which I explained at the beginning of the blog. So, if you see such behavior, check PARSEONLY and NOEXEC set options in query level setting and SSMS level setting.

Have you ever landed into this behavior ever? Do let me know. I am sure if you share via comments, it will get us into an exploratory mode to learn and try more options if I missed any.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Server Management Studio
Previous Post
SQL SERVER – Steps to Backup to Windows Azure storage
Next Post
SQL SERVER – Why Should You Not to Use Old Style JOIN?

Related Posts

2 Comments. Leave new

  • I use SET NOEXEC ON at the beginning of scripts when I’m developing. It prevents running the whole script accidentally when I forget to select the part I’m testing.

    Reply
  • I am seeing this now, and neither of those options are checked for me. On my spare domain admin account, I can run the queries just fine.

    Reply

Leave a Reply