Voice of the DBA

Category: Blog

What’s the little popup window in #SQLPrompt?

Awhile back I was working in SSMS and saw this window.

It threw me off since I was trying to write some code and hadn’t expected it. I clicked Escape, Enter, a few things and was getting frustrated when it disappeared.

I ignored it until I saw the window again and then investigated. I’m glad I did because I was able to answer a question from someone else recently that didn’t know how to get rid of it.

Tl;Dr CTRL will make it appear or disappear.

When I am working with SQL Prompt, it’s in the background. I usually just depend on it to pop up some code or give me information. This means when I have a cursor, there’s no sign of SQL Prompt. Notice this below.

As soon as I select an area, as little as one space, I get a small SQL Prompt window in the left sidebar. As you can see in the image below, this has a down arrow on it.

I can click on this, but being a keyboard person whenever possible, I accidently discovered that CTRL will expand this, as shown below.

What threw me initially is that not all my snippets are in this list. Only those that have the $SELECTEDTEXT$ token inside them. These are handy snippets that I want to use to encapsulate text.

For example, let me surround a simple query.

I see the SQL Prompt icon and can click CTRL to open the list. If I type “cv”, I get the Create View snippet.

Once I then hit tab, I get the snippet with my query inside.

This is especially handy with things like TRY..CATCH, where I can write the TRY part and then quickly surround it with the structure.

Once you get used to this, and learn not to habitually tap the CTRL key (as I do), you’ll find this list of snippets handy. And if you don’t like them, just tap CTRL and get rid of the list.

November 30, 2016
Rate a Session for GroupBy

One of the things that I struggle with is understanding whether a session at a conference like a SQL Saturday is worth watching. I also struggle writing abstracts and attracting people to my own sessions, so I think the idea of GroupBy allowing rating and reviewing of abstracts in advance is fantastic.

First, let me encourage many of you to go take a minute today and rate an abstract. If you see something that you like, leave a note. If you aren’t sure of something, or don’t like something, or even have a question about the content, leave that note as well.

I’ve been fortunate to attend lots of events. I speak at many, but I try to view a session or two at each as well. I’ve seen some great ones, and some poor ones. I do try to provide constructive criticism, and I do so privately. If you’d like feedback from me at any event, please ask.

One of the things I’ve seen is that the abstract often doesn’t quite match the talk, or the abstract doesn’t really help me understand what will be covered. It doesn’t matter if someone has tried to write a cute description or a plain boring one, the writing doesn’t always match the talk well.

I know I make mistakes in my abstracts. I know sometimes I write something that I realize later isn’t quite what I think will work in the talk. I’d like the chance to edit and correct small items. More importantly, I’d like to be sure that if my abstract topic (and talk) could slightly be tweaked in a way that more people like, I want to do it.

Take a minute and give some feedback. Be honest, rate what you want, don’t take up too much of your day, and help improve the conference schedule.

I’d love to see PASS implement this as well for the Summit. I realize this can be hard, but I would prefer to see some give and take in advance to help build the best set of sessions that people want to attend.

November 29, 2016
Validating a Set of Database Scripts using DLM Automation

The basis of all the DLM Automation from Redgate is a series of PowerShell cmdlets. They might look intimidating or confusing, but they aren’t. This is part of a series of posts that examine how you use each one.

Previously I looked at New-DatabaseConnection. In this post, I’ll go through Invoke-DlmDatabaseSchemaValidation. This is the cmdlet that one uses to check if your set of scripts will actually produce a database. This is equivalent to the “build” plugin that exists for a few platforms.

The way this works is that the location of the database scripts is passed to this object through a pipe. This will then validate the scripts on LocalDB with a build of the database and the static data scripts. If this works, then an output object is returned.

A Quick Build

Let’s see how this works. I have a valid database folder on my computer. This has all my object code in subfolders, including static data in the data folder. I want to validate this folder.

I can do that with this code. I’ll pass the location of the scripts into the cmdlet.

$output = “e:\Documents\GitHub\SimpleTalk_Devlopment\ScriptFolder” | Invoke-DlmDatabaseSchemaValidation

When I do this, a LocalDB instance is created and the code validated. I get a message to that effect. The output variable has the confirmation message.

This means the code is valid. However, does this really work? Let’s edit some code and see. I’ll change the code for a procedure. Here’s the original GetCountryCodes.sql.

Let’s change this to top 100 and add an ALTER, but I’ll get an extra comma in there. This is no longer valid SQL.

Let’s re-run the build. We now see this has failed with an error, and the file is the one I edited:

This is a quick look at builds, but there is more that can be done. You can specify the server and database to be used, combining this with the New-DlmDatabaseConnection I previously wrote about.

I urge you to experiment with this cmdlet if you want to perform your own builds.

November 28, 2016
Quick PoSh Kills with Stop-DbaProcess

I’ve been trying to get used to using the dbatools cmdlets in PoSh. They help me learn some PowerShell, but they also make some things easier. As a part of my practice, I’m documenting the various items I’ve played with. This post looks at Stop-DbaProcess.

Killing Spids

At first I didn’t think much of this cmdlet, because I need to know a spid right? The time it takes me to run sp_WhoIsActive or sp_who2, find a spid, and decide to kill it is most of the time. A quick “kill xx” is easy in SSMS.

This cmdlet does more, which makes it really handy for me. I know it’s just issuing a kill in the background, but it does have some features that perhaps make this more enticing, especially during times where I might need to quiesce a server and remove a number of users.

The first thing that comes to mind is that I can quickly kill all the users using a particular program. For example, I had a demo app with a generic connection to SQL Server. When I ran it, there were multiple connections to SQL Server. If this had been behaving badly, perhaps with a long running query, I’d have had to kill both of these to ensure I got the right one. In a demo, perhaps “kill 60” and “kill 62” is quick enough.

I could have done this, though.

Way more typing, right? Sure it is. There’s an advantage, which I could have used more than a few times in my career. I didn’t have to look up the Spid with PoSh. In various jobs where I’ve had poorly behaving applications, I’d have to get a list of processes, find the numbers, and kill each of them individually. Easy as a one-off, harder (and annoying) when you are interrupting work regularly to repeat a tedious process.

With Sop-DbaProcess, I can keep this script ready and have it kill all the connections using that program name, whether there is 1 or 100.

What’s more, I have more options to exclude particular spids or logins, limit this to databases, run across multiple hosts, etc.

Simple, easy, and useful in some situations. I wouldn’t kill a single process with this, but I’d certainly want to use this if I had to repeat myself over and over.

Give dbatools a try and see where you might start finding PoSh to be useful while administering SQL Server.

November 25, 2016