Personally I’ve always enjoyed learning the T-SQL for any given task. The GUI is usually pretty easy to figure out but I’ve found that for standard tasks it can be quite a bit faster to just use a script. In fact I posted a while back on using the script button as a method for learning T-SQL. So other than my personal preference why is it a good idea to know how to work without the GUI?

Well here are 2 examples that I’ve run into recently. And while I will admit both are somewhat unusual they are also cases where you don’t want to get stuck not knowing what to do.

First and more common, although still rare, is the need for the Dedicated Admin Connection (DAC). This connection should only be used during an emergency so let’s really hope you don’t have to do use it too often. It has dedicated resources that will let you into the instance regardless of how hammered the box is otherwise. So why is T-SQL necessary here? Well the DAC can only be used in one of two ways. SQLCMD (a script only connection) or a query window. Now the query window still has intellisense and all the other nice little features it normally does but you still have to be able to write your own scripts. In my particular case the instance had locked up but we really didn’t want to restart it for various reasons, not the least of which is finding out what was causing the problem in the first place. So I logged in using the DAC ran some queries using the DMVs and resolved the issue. Not something I could have done easily or quickly if I always relied on the GUI to do my work for me.

Now this second case was defiantly somewhat unusual. My team had been asked to find a supported home for a database that had been created by an application. The users had installed SQLExpress on one of their servers but had not installed the management tools. The only login that I knew for certain had access to the instance was a service account that was locked down to that server only. Ie the service account can only log into that machine and no other. This meant that I couldn’t just log into a machine that had the management tools with the service account and get to work.

What I ended up doing was logging into the machine using the service account, opening up SQLCMD, which doesn’t require the management tools to be installed, log into the instance using trusted authentication, and run some scripts to grant myself access (CREATE LOGIN and ALTER SERVER ROLE ADD MEMBER). Once that was done I went into the configuration manager, started up the browser service, enabled TCPIP and restarted the instance. Once all that was done I was now able to use my own account from my own workstation and could “lose” the password to the service account.

Note that in both cases I could easily have looked up any commands I needed but by knowing them ahead of time I saved quite a bit of time. And let’s face it, once I’d looked them up I’d be learning them anyway right?

Filed under: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, problem resolution, T-SQL