When SSMS Thinks It’s Helping, But It’s Not

Sometimes, you can run into problems where you can’t believe what you’re seeing. I recently went through one of those times. How could a seemingly insignificant change to a stored procedure cause me so much frustration?

The task was about as simple as could be: commenting out a section of a stored procedure that’s not currently needed. I opened the stored procedure, made a few keystrokes to comment out the section in question, and pressed F5 to apply the change. A few minutes later, I happened to reopen the same stored procedure and noticed that not only were all comments gone, the procedure was completely reformatted.

There Goes My Relaxing Morning

I grabbed a copy of the original stored procedure, applied that version with all comments, and reopened the stored procedure. Once again, comments were gone and the stored procedure reformatted.

I started questioning my sanity and had to double check whether or not I was making changes to the correct database. Was I updating one database but opening the procedure from another database? Even then, that wouldn’t explain missing comments and different formatting.

It’s Not Me, It’s You

I had not been logged onto the server hosting the database so I decided to go to the source. I logged onto the SQL Server, opened up SSMS, made the same changes, and they magically saved just as they were entered. Comments and formatting remained intact.

I went back to my jump box, went through the same update steps, and still had a problem there. That seemed to narrow it down to my SSMS setup.

The Culprits

I did some Google searching and came across this thread which mentions a few settings changes that caused someone else grief:

1. In the Connection Properties of the Connect to Database Engine, go to Additional Connection Parameters and enter the following: Column Encryption Setting=Enabled
2. Right click on your query pane and to go Query Options => Advanced => select Enable Parameterization for Always Encrypted

This lined up with some recent testing and I confirmed those settings were causing the issue.

If you ever encounter similar strangeness, check the encryption settings referenced above to see if they are the source of your headache.

Thanks for reading!