30 May 2018
30 May 2018

SQL Prompt Safety Net Features for Developers

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Mistakes happen, occasionally .You accidentally close an SSMS query tab without saving it, before realizing it contained an essential bit of code. You’re working late, switching between test and development servers, and accidentally execute code against the wrong server. SSMS conspires against you and crashes unexpectedly, and you lose all your currently open query tabs, some of which you hadn’t saved.

We’ve all been there. I recall one such incident vividly. I was working at Redgate’s offices, and a passing developer laughed at my howls of rage, as SSMS crashed on me, just when I had almost finished a particularly clever stored procedure. A short while later, a higher entity made it happen to him too, so after reflecting soberly for some time, he developed SQL Tab Magic, in a down-tools week project. It became a cult tool and eventually went mainstream, as part of SQL Prompt.

I’ll describe a few common mishaps that everyone working in SSMS will have experienced a couple of times, and how SQL Prompt “Tab Magic” (now more prosaically called Tab Color and Tab History) provides ‘insurance’ against their consequences.

Avoiding the apocalyptic

Have you ever been working away, into the early morning hours, trying things out on your development server, so that you can ‘roll-forward’ the production database server, and fix an urgent and embarrassing problem with the latest release?

No, me neither, as I always say, especially when in august gatherings of DBAs. However, it’s happened to a friend of mine. It was three o’clock in the morning, and he was tired, but still working hard, trying to sort out a bad problem with the shopping cart process of a website. He was working in SSMS, with one query pane connected to the development code, and another query pane open to the production server, showing the current, live version of the same code.

I, by which I mean my friend, had to clear out the entire ledger table on the development server and re-insert with test data, and then run an integration test to verify what he’d done. He executed the wrong code in the wrong query pane. What he’d done wrong hit him almost immediately and in the few brief seconds before the execution failed, his life flashed past him. How many hours of business transactions would be lost to the company? Never had he been so grateful to see his code ‘tripped up’ by the violation of a FOREIGN KEY constraint.

As he pondered his narrow escape, he reprimanded himself for not checking the base of each query pane, which shows the name of the connected server. But who routinely checks that? There must be a better way, surely? For a while, I (and my friend) took to using used different PCs to work with each server simultaneously. It worked well, even when half asleep. Now, we both use Prompt “Tab Magic”, and we never ever again forget to run anything contentious within a transaction, just in case.

Why, more generally, would you need to work on different servers as a developer? I like to keep an eye on the test server, check on metrics in the test runs that wouldn’t interest the test team. The same goes for staging. I need to be very careful, though.

The query pane of SSMS has a row of tab labels along the top, and a status pane at the bottom of each query tab. SQL Prompt’s ‘Tab Color Magic’ component will colour-code these to help you to see immediately to what server, or type of server, each tab is connected. The colour of the tab that you choose is bright, if it is the current tab, or darker if it isn’t.

Figure 1 shows my SSMS session, with three query tabs. You would want to think twice before executing this code, intended for your development server, on your production server instead!

Figure 1

Fortunately, with Tab Magic colouring, you will be in no doubt at all, because of the lurid red background colour of the tab connected to the production code, and thanks to the status bar at the base of the query window.

Figure 2

There are several ways that you can map each of your servers or databases to your chosen colour for that category of environment. The most intuitive way is to right-click on the server or database in the browser pane, but you can also do it for servers, or groups of servers, from the local Registered Server groups browser, or the Central Management Servers browser.

Personally, I find it neatest to use the registered server groups, and there are a couple of ways to map these groups to their environment (and therefore tab color).

Firstly, you can set the colour for the group by write clicking on it, and selecting Tab Color (Group), and assigning it to an environment.

Figure 3

The Tab Color (Group) option is for when you are working on a query pane that will need to connect to several servers in a group. The assigned environment color only takes effect for query tabs opened directly from that group. Having assigned the group to Development (as in Figure 5), if you then just open a query tab and connect it to an instance, the tab won’t be green; it will be the default color, or whatever color is assigned lower down in the hierarchy, at the server or database level.

However, if I use the setting Tab Color (Servers in Group) option instead, or as well, then any query tab connected to an instance in that group will inherit the specified colour, unless I then override it at the individual server or database level.

The cleanest way to do it is make your assignments at the highest level possible, and then leave everything else set to Default. For example, set the environment at the Registered Server Group level and then make sure Tab Color (Server) is set to Default, for each server in that group, and then that Tab Color (Database) is set to Default for each database on each of those servers.

You can do the editing of all the tab colour assignments within SSMS, using SQL Prompt’s Options > Tabs > Color window. It is in this window that you can delete assignments, which is the same as setting them to Default. It pays to check in here occasionally, and clean stuff up if needed!

Figure 4

You can use this window to add need environments, if the built-in collection proves insufficient. As you can see, it’s also possible to add server directly from this menu, but it is less error-prone to do it from the server’s context menu in SSMS.

Before capturing the screenshot in Figure 4, I’d previously added a Sandbox environment to the existing built-in environments by clicking on Edit Environments, and providing the name and colour I wanted.

Figure 5

I was then able to assign it to my ‘Sandbox’ group, in my registered local servers, from the context menu.

Recovering from the unexpected

Just occasionally, SSMS disappears. You’ve been busy, and have plenty of query tabs open. Suddenly, you don’t have SSMS anymore. You were minutes away from finishing some code you’d laboured on for hours. Now you are, it would seem, still hours away. I managed to do it while writing this article.

There was a time when I consoled myself with the thought that I’d probably write it all better second-time around, but then there is the tedium of having to do so. My brain slowly empties overnight like a leaking whoopee cushion, so it means staying late to redo it, before I forget it all.

Tab History was developed with characters like me in mind. Now, I just give a smug smile and hit the Prompt “Tab History” button. I feel rather like that insufferable family in the ads who, on returning home to find their house submerged, merely smile sweetly and call their incredibly-accommodating insurance company.

The trick is to visit SQL Prompt’s Options menu before disaster strikes, and then click in Tabs > History.

Figure 6

If tab history is enabled, you have an SSMS insurance policy. If SSMS crashes, or if you just close down SSMS recklessly without saving your open tabs then, on relaunch, SQL Prompt Tab Magic will restore all the tabs you had open when SSMS was last alive. You’ll have the same work as you did when you last saw it. The application saves all the T-SQL code, just as it was before you closed or lost it. Tab history can also reconnect the tabs to the databases to which they were previously connected.

With Tab History enabled, you’ll also have protection against the consequences of a few other common mistakes…

Undoing the unintended

Tabbed query panes proliferate. Suddenly, you yearn for less clutter in your life, and start furiously clearing out all those query panes, all with default names like SQLQuery25.sql. Click on the x in each tab in turn. Click – Save changes to the following items? Nope. Click – nope. Click – nope. Oh no, wait! That tab SQLQuery20.sql had that brilliant table-valued function you’d spent ages working on, and now you’ve deleted the query pane without ever saving the file. All that work gone!

No, it hasn’t. Tab Magic History has saved what I was working on. Click on the Tab History icon at the top of the SSMS application menu bar (or hit Ctrl+Q).

Figure 7

You can filter the list to show all tabs, or only open, or only closed tabs, and switch between the views simply by hitting the Ctrl+Right Arrow and Ctrl+Left arrows.

You can see that the tab list shows the file name, if you ever saved it, and the SQL Server to which the tab is or was connected, and the environment to which that server belongs, as defined by your Tab Color Magic configuration.

Just mouse over each of the tabs in the tab listing column, or use the up and down arrows, and the code for that tab will appear in the right-hand pane. At the top right of the code preview pane, you’ll see the time the tab was closed if you ever closed it, and in the bottom right you’ll see the database, as well as the SQL Server to which it was connected.

Once you’ve found the code you want, click on it, in the tab listing, or just hit enter, and the SQLQuery20.sql will be restored as an open query pane, with nothing lost. Now save it, of course.

Reinventing the invented

I’m always doing it. Crikey, three weeks ago I hit a similar problem. I had a great solution in the end, but it took ages to develop. Now where is it? The first task is to think of a good search term. Hmm. It was how to import a CLOB, a JSON or text file, into SQL. I remember that it was an OpenRowSet command but what was the syntax?

Open Tab History again. I like my tab history as large as the query pane when I’m trying to find what I’ve been working on. Type into the search bar anything significant you can remember about the ‘lost’ code.

Figure 8

In this case, I’ve gone straight there. The search term is even highlighted in Yellow Ochre. Click on the list item to read it into a query pane. Alternatively, you can hit Tab, to switch the context to the query preview pane (Shift+Tab to switch it the other way), and then Ctrl+A and Ctrl-C to copy the code onto the clipboard.

Occasionally, you might get the urge to de-clutter the tab listing. By right-clicking on a tab, you will see a context menu that allows you to rename a tab or remove it from tab history. If you wish to clear out old tabs, you can remove tabs older than the one you right-clicked.

Summary

In an ideal world, you might think that you would be unlikely to need Tab History or Tab Colour. You would be saving your work in source control dutifully on every change, and doing nothing wild, woolly or likely to lead the eminent members of PASS to hiss through their teeth.

In reality, SSMS is pretty undiscriminating in the way that it occasionally clicks its heels and vanishes, usually when you least expect it to. SQL Prompt Tab Magic deserves its cult status because it was designed for those of us that know better but occasionally cut corners. It tames SSMS and prevents us from making fools of ourselves. I was a very early adopter of Tab Magic, and I’ve lost no work ever since. I can now reuse things I’ve done before, and I’ve never since come close to doing anything apocalyptic on a production server, by mistake.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    How to implement a T-SQL code formatting standard using SQL Prompt

    To what extent should a team attempt to impose a uniform SQL coding format? It can sometimes seem like a futile task. All programmers have a preferred formatting style, to which they profess an unwavering attachment. Check the code of ten programmers, though, and you will likely discover at least 15 different formats, since few

  • Article

    Quick extended properties with SQL Prompt

    I’ve been experimenting with Extended Properties, and I found myself slightly annoyed by the syntax of adding and updating Extended Properties. I decided to take advantage of SQL Prompt to store the commonly used code for adding and updating properties. Adding Properties I’m a big fan of naming the snippets the first thing that comes to mind.

  • Article

    How to reformat a database in one operation

    Inherited a database from another team? Changed your team policy on the way that you format SQL? What’s to stop you formatting the code of an entire database nicely, when you’re developing it? It can be done, but the process can take longer than you expect. This article will demonstrate a simple 3-step approach to

  • Article

    Finding code smells using SQL Prompt: Asterisk in SELECT list

    Using SELECT * FROM within IF EXISTS statements are fine, but in other contexts it causes several problems. For example, INSERT INTO…SELECT * FROM statement assumes the columns are in a particular order, and that order may not last. Also, selecting all the columns in a table when you don’t need them all can cause

  • Article

    Quick SQL Prompt tip – using the CDB snippet

    SQL Prompt includes a number of snippets by default that can help you quickly write T-SQL code. These are templates of code that users use regularly. One of the more popular snippets is the ‘cdb’ snippet that helps with quickly creating a new database for development work. This can also be used to ensure production

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly