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:
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:
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”
And over there, we can set option for query level.
SSMS Level (For all query windows):
In SSMS, we can go to “Tools” > “Options” and chose Advanced as shown below.
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)
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.
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.