In this post I show you a problem that has been bothering me since the first version of SSMS 17 to the point that I have been using SSMS 16 until now This post is going to be a quick one, but hopefully will help others as this problem has been bugging me since the first release of SSMS 17 to the point I’ve been using SSMS 16 till now.
Background
Like probably most of us, I need to modify stored procedures or other sql modules that have comments before the CREATE statement.
I find this better for readability because you can skip the comments and get straight to the point if that is what you want to.
But all the excitement I had when I installed SSMS 17 for the first time went down the toilet when I right-clicked a stored procedure and select “Modify”. I just couldn’t believe it, all the comments where gone, WTF?
That is not nice, because if by mistake I compiled that, all the history would have been lost and since we use source control, that’s not much o a deal, but if not what? restore from backup to get your comments back? Ugly.
I thought that was a bug back in the earlier versions and will be fixed, and to be honest I kinda remember it was fixed in 17.2 or so, but when I updated to a more recent version, the problem showed again.
And 17.6 come out and my colleague Shane (b|t) installed and I asked him to see if he was experiencing this problem, and he wasn’t, happy days!
But then I updated mine to 17.6 and there it was my comments removed. Bloody hell.
Where are you little cheeky config?
Ok, enough is enough so I got to cross check all the settings for scripting to find if we had the same and find the culprit. These settings can be found in
Tools -> Options -> SQL Server Object Explorer -> Scripting
And there it was, almost hidden where you won’t expect it to be, under tables and views.
And et voilà! Here it was. “Convert user-defined data types to base data types“. This has to be defined as “false” for the comments to show.
As I said, I’m not sure how this setting might ever enable or disable showing the comments prior the CREATE or ALTER statement to be honest, I believe this is a bug 🙂
Conclusion
Once we found the setting that makes the problem, I think I like SSMS 17 a bit more than before, and it’s always best to keep your tools up to date, although sometimes you can hit bugs.
Thanks for reading!