A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, April 1, 2015

Never trust the SSMS GUI

There's a group of SQL Server professionals that try to help on StackOverflow and DBA.StackExchange.com. One of the most telling symptoms of a bad question is people using the GUI to administer their SQL Server instances. "I clicked here, here, here and bad thing happened". What did you actually do? Do you know?

It's ok, you don't have to be ultra hardcore and memorize every bit of syntax or spend time looking through Books Online to find the specific command you're looking. You can use the GUI to administer your machine. Just don't click the OK button. Instead, look up.

That beautiful Script button is the one you're looking for. That generates all the DDL that clicking OK would have done but now you have something you can inspect. "Trust, but verify" as someone once said. Not only can you inspect it, you can share those commands with someone else.

In today's example, I learned the GUI was silently swallowing the error. I had created an operator for myself but misspelled my last name. I correct my name, clicked OK and ... it never changed. Weird, I must have clicked Cancel. This time, I ensured I clicked that big OK button.

I hit refresh on the Operators list and what the heck? Why am I still "Bill Fellow?" I demand an "S"! Once more unto the GUI and this time, I clicked Script Action to new query window.

EXEC msdb.dbo.sp_update_operator @name=N'Bill Fellows', 
        @enabled=1, 
        @pager_days=0, 
        @email_address=N'BillinKC@world.domination', 
        @pager_address=N'', 
        @netsend_address=N''
Perfect, this is what I want. I'm updating the operator...
Msg 14262, Level 16, State 1, Procedure sp_update_operator, Line 61
The specified @name ('Bill Fellows') does not exist.
Or not. I could have been spent time yelling at the SQL Server for being "dumb" or posted some question with exact reproduction and if someone was inclined, they could have verified that yes, it doesn't appear to rename. But look what happens when you actually run the command yourself. It tells you exactly why it's not working.

Asking questions is good. We should all endeavor to ask questions of everything. But even better is go find the answer.

Happy scripting.

No comments: