Voice of the DBA

Category: Blog

SQL Konference 2015

My first speaking date of the new year is scheduled. I’ll be at the SQL Server Konference 2015, near Frankfurt, Germany. Red Gate arranged things, and I’m looking forward to heading over and renting a car to drive in Germany.

And speaking, of course.

I don’t know the agenda or details, or which session I’m presenting, but it’s exciting.

Slightly bittersweet as I won’t make SQL Saturday #358 in Albuquerque. I’ve been to the last two, taking a day to ski at Taos, but I can’t make it this year as I’ll be speaking that week in Europe.

November 19, 2014
T-SQL Tricks – Custom Templates
I wrote about the Template Explorer, which comes with the SQL Server tools and is visible in Management Studio (SSMS). It’s handy, but there are limited code items in there. What if I want more?

That’s easy. Suppose I decide that I often need to create procedures with the EXECUTE AS clause. I usually do this:
```
CREATE PROCEDURE MyProc
   @id INT
WITH EXECUTE AS OWNER
AS
BEGIN

-- do work
BEGIN TRY

  COMMIT
END TRY
BEGIN CATCH
  ROLLBACK
  EXEC uspErrorHandler;
END CATCH

END
```
It’s a basic template of stuff I do. Let’s stick this in our Template Explorer.

The first thing I do is go to the Stored Procedure folder. I can right click it and I’ll see this:

I choose template and a new one is created. I enter a name and I have a template. The first 6 templates here are defaults. The last one, highlighted below, is the one I created.

Now I right click it again and select Edit. At this point, it will open in a query window. This is just a file in my file system (under ), and like any other query, I can edit it. I paste in my script from above, and change a few items to parameters.

Now I can save this, and the next time I need this, just drag it into the main window and customize it.
November 19, 2014
Use -eq in Powershell

I was writing a quick script to work with files and I only wanted to process one file for each execution of a loop. I could have done this multiple ways, but I threw this together:

$fileEntries = [IO.Directory]::GetFiles(“d:\placeholder”);
$delete = 1;
foreach($fileName in $fileEntries)
{
if ($delete = 1)
{
# do something
$delete = 0;
}
}

When I ran it, it kept deleting everything in the folder. That was really annoying, and it took me a few minutes to spot the problem. I kept thinking my variable wasn’t getting set to a new value, but it was. The problem was it kept getting reset.

I first changed to this, but that produced a PoSh error. That’s because I’m working in PoSh and not C.

$fileEntries = [IO.Directory]::GetFiles(“d:\placeholder”);
$delete = 1;
foreach($fileName in $fileEntries)
{
if ($delete == 1)
{
# do something
$delete = 0;
}
}

Eventually I remembered that I need to compare things with -eq, so I ended up with this, which worked perfectly.

$fileEntries = [IO.Directory]::GetFiles(“d:\placeholder”);
$delete = 1;
foreach($fileName in $fileEntries)
{
if ($delete -eq 1)
{
# do something
$delete = 0;
}
}

November 17, 2014
T-SQL Tricks – Templates in SSMS

There are lots of little tricks that you can use to become more proficient, and efficient, at writing T-SQL. Here’s a short one that might be more for administrators, but I think for developers it’s very handy as well.

SQL Server tools have included templates for years. I first started using them in Query Analyzer with SQL Server 2000. The tools have changed, but we still have templates in Management Studio (SSMS). We can access the templates by exposing the Template Browser from the View menu (as shown below).

We can also use the CTRL+ALT+T shortcut. Either of these will give us a tab on the right side of our SSMS main window, opposite the Object Explorer, as shown below.

Like all the windows in SSMS, I can detach this, move it, auto hide, etc.

Each of these folders contains a series of default scripts that are installed with the tools. For example, I can look at the Backup folder and see three scripts in there. I can grab one of them with a left click and "drag" it to the query window and the script will appear in the query window as I’ve shown here.

A CTRL+Shift+M will give me the parameters in the script, which I can replace with my own values (or accept the defaults).

Many of these are generic, but they are useful. I’d encourage you to take a look at them and use them when you can.

You can customize these, but that’s for another post.

November 12, 2014