Have you ever encountered a situation where you run a query in SQL Server Management Studio (SSMS) and receive a message saying “Query Command(s) completed successfully without any results”? If so, you’re not alone. This behavior can be quite baffling, but fear not, there is an explanation.
When faced with unexpected behavior in SQL Server, it’s natural to feel curious and want to understand the underlying cause. Playing around with different options in SSMS can often lead to such situations, providing valuable learning experiences.
Let’s take a look at a classic case that I recently encountered. I was running queries on my laptop and no matter what query I executed, I would always receive the message “Query Command(s) completed successfully without any results.” This puzzled me, and I decided to investigate further.
My first step was to search for possible solutions on my blog, where I had previously written about a similar issue. However, I found that some readers had followed the suggested steps (such as using the SET PARSEONLY option) but were still experiencing the same behavior.
When faced with such a situation, it’s always helpful to reach out to others who may have encountered similar issues. I asked for assistance and was advised to capture a profiler to reproduce the problem. Here’s what I shared with my friends to help them understand the issue:
By using the profiler, I was able to identify that the NOEXEC option was set to ON, which was causing the “Query Command(s) completed successfully without any results” message. The next question was to determine why this option was set and how I ended up in this situation in the first place.
There are two places where the NOEXEC option can be set in SSMS:
- Query Level (for the current query window in SSMS): Right-click on the query window and choose “Query options.” From there, you can set the option for the query level.
- SSMS Level (for all query windows): Go to “Tools” > “Options” in SSMS and choose “Advanced.” If any of the options are checked, you may experience the same behavior described earlier.
If you ever encounter this behavior, make sure to check the PARSEONLY and NOEXEC set options in both the query level settings and SSMS level settings. This will help you identify and resolve the issue.
Have you ever experienced this behavior in SQL Server Management Studio? If so, I would love to hear about your experiences. Please share your thoughts and insights in the comments section below. Together, we can explore and learn more about SQL Server management options.
Reference: Pinal Dave (https://blog.sqlauthority.com)