I discovered a bug today in 2016 Management Studio when creating and updating policies. It drove me crazy until I realized what was going on, causing lots of lost time. Hopefully this will get fixed fast; we are reporting it immediately because I couldn’t find any references to it already out there. Special thanks to Kenneth Fisher for helping confirm that it wasn’t just affecting me.
In the latest release of SSMS 2016, 16.5.1 and newer, policy conditions are removing quotes on each save, causing parse errors.
Vote the Connect up. A fix for this should be released in the next few weeks, but it doesn’t hurt to show your support for Policy Based Management.
I’ll walk through a full, simplified policy creation showing how I discovered the problem, but it can be recreated by just editing a condition.
I created a new policy named
Test and a new condition, also named
Test. I set the condition facet to server, and input the following code into the field to create an ExecuteSql statement. Everything requiring a quote inside of the string has to have at least double quotes.
Executesql('string',' Select ''One'' ')
Once the code was input, you can see below that the code parsed correctly. SSMS was happy with it, so I hit OK to continue.
I finished creating the policy, everything was still looking fine.
I then went to Evaluate the policy. The policy failed, as I expected. That’s not the point. If you look closely, you’ll notice that the
Select One statement is no longer surrounded by double quotes. That shouldn’t have happened.
I opened the Condition itself and received a parse error. Without the required double quotes, the Condition was broken.
I tested this by creating or editing a condition without a policy or evaluating it and got the same results using SSMS 2016 on two separate computers, versions 16.5.1 and 17.0 RC1. When using SSMS 2012 or 2014, the code was not altered, everything worked as it should have. Finally, Kenneth happened to have an older version of SSMS 2016 and could not reproduce my error until he updated to the latest version of SSMS 2016, indicating that it is a recently introduced bug.
And again, if you haven’t already, vote up the Connect item.