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
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!
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.
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.
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!
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.
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.
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).
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.
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.
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.
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.
Also in Hub
It would be wrong to portray SQL Change Automation (SCA) as being suitable only for epic project deployments, of the sort described in my previous article. It can do smaller tasks as well. To demonstr...
Also in SQL Prompt
The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Relational tables are not ordered, so the ORDER BY is meani...
Also in Product learning
Data protection and privacy regulations, ranging from GDPR to HIPPAA to PCI, among many others, put strict compliance requirements on the storage and use of personal and sensitive data, in any of your...
Also about SSMS
In SSMS, we have access to templates that provide building blocks for creating various types of query, common routines to create various schema objects, or to do common DBA tasks such as running backu...