Voice of the DBA

Category: Blog

Quick SQL Prompt Updates in a Pattern
I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try.

We had a customer post a question today on how they can built an update statement with a pattern. Specifically, they said that the code often looks like:
```
UPDATE dbo.Contacts
   SET 
   c.Salutation = @Salutation
, c.FirstName  = @FirstName
, c.MiddleName = @MiddleName
, c.LastName   = @LastName
, c.Suffix       = @Suffix
WHERE ContactID = @contactid
```
The table columns are the same name as a variable. That’s a good pattern, and I’d think SQL Prompt could handle that.

It doesn’t.

The column picker doesn’t work with Updates (logged w/ product team), and I can’t duplicate selected text over (also logged for discussion). However, I do have a workaround.

As I thought about it, I realized there are some features of Prompt that help here, and some of SSMS that will work.

I made a quick video of the process, but I’ll describe it below:

The Process

The first thing is to get a column list. ssf<tab> does for me. I’ll get the select statement for a table and then expand the list of columns with a tab when on the *.

Now, I’ll copy the columns. I tend to copy all since it’s usually easier to remove than pick and choose specific ones. I’ll wrap these in an update, which could be a snippet. If it’s not, that’s fine.

From here, I use the power of Shift+ALT. If you’ve never done this, it’s amazing. I use this to select the columns and copy them. Then I’ll CTRL+ALT to add the = and paste in the columns. I can then use CTRL+ALT once again to remove the alias and replace with a @.

And, of course, I can reformat to make it look nice with SQL Prompt. Give SQL Prompt a try today and see how it can improve coding and feel free to share your tips here.
October 17, 2017
Using Backup-DbaDatabase for a Quick Backup

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One of the core tasks of a DBA is backing up a database. In fact, I’d argue that it’s the most important thing for a DBA to know. Second would be restores.

I’ve used the BACKUP DATABASE command so often from T-SQL that it’s a quick way for me to just get a backup of a database. I almost always have SSMS running, so I can easily just run the backup.

For regular backups there are some great tools out there, SQL Backup Pro from Redgate, Minion Backup, Ola Hallengren’s scripts, and more. However, there still might be a time I want to make a few backups, perhaps copy them over, and that’s where I think dbatools and Backup-DbaDatabase might help.

This is a nice, easy cmdlet to use. You can probably guess how to use it. Give it an instance, a database (or few), include a path, maybe create folders for each database, and let it go.

That’s about it.

If I run this interactively, I’d get the progress bar:

$2017-10-02 17_04_29-{15%} cmd - powershell (Admin)$

When the command is done, I see the results from each database. I get the file, folder, full path, and the script used.

I could include a different path if I wanted, and certainly I can chain this along with other PoSh commands.

I don’t know that I’d use this often, but it is handy in places, and certainly if I want to script the movement of some files, perhaps for something like HA/DR testing or setup, or even to refresh other environments.

October 16, 2017
Getting the Random Module in SQL Data Generator

This is a series on SQL Data Generator, covering some interesting scenarios I’ve run into. If you’ve never tried it, SQL Data Generator is a part of the SQL Toolbelt. Give it a try today with an evaluation today.

I have SQL Data Generator, and use it regularly to build quick test data in non-trivial scenarios. One of the things I ran into recently was a minor bug, but one that’s been logged. Hopefully you won’t need this, but in case you do.

I was trying to use a Python script and return a random value from a list. My code was:

return random.choice(mylist)

This gave me an error.

No biggie, I’ll add “import random” to the top of the script. That didn’t help. Apparently the distro with SQL Data Generator is missing the random module for some reason.

Fortunately, I have Python 2.7 on my system. I clicked “Tools” and “Application Options” in SQL Data Generator.

This gave me a dialog. On the General tab, there’s a “Python” section. I added the path to my Python lib folder in here.

That worked fine. I didn’t need to import random; it was available. My script now worked.

October 12, 2017
WAITFOR isn’t a function–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I needed to delay the execution of some code the other day. This was a test that was trying to simulate a few things happening, and one batch needed a random delay. I started typing and got something I didn’t expect.

Where’s the TIME or DELAY? SQL Prompt didn’t like this, and since I expect SQL Prompt to save me from writing bad code, I knew I’d done something wrong. I backed up and removed the parenthesis and ‘R’ and then typed again, this time adding a space.

That works. Now I can change this test to real code.

WAITFOR is designed to delay execution, but it’s not a function. No parenthesis. Instead, it’s a control of flow statement, like CASE, so it just takes other expressions after.

SQLNewBlogger

This was one of those commands I haven’t used in a long time, but is a handy one. Hopefully this 5 minute writeup will help me remember this in the future.

October 11, 2017