Hacking SQLCMD Mode: Introducing PowerShell

Disclaimer and a heartfelt thanks:   Whenever possible, I will endeavor to give credit to people that provided a solution, idea, or inspiration for my own blog posts. These posts would not have possible without all the amazing people in the SQL Server and PowerShell community sharing their own solutions. While I'm not aware of anybody using PowerShell in SQLCMD mode, I don't claim to be the first person to have thought of it. If there are any other sources for anything I cover, please contact me so that I may give proper attribution.

Goodbye Command Shell. Hello PowerShell.

It seems like the relationship between SQL Server and PowerShell is getting a lot of attention lately. Wayne Sheffield just ran a series titled, "A month of PowerShell" which covers just about everything you could want to know about SQL SMO and PowerShell. As I've started using PowerShell in early 2011, there's not much I can add to what Wayne's doing. So instead of using PowerShell to connect to SQL Server, we're going to use SQLCMD mode to talk to PowerShell.

This is new way of using SQLCMD that I'm really excited about. It's not part of any service pack but something that's been available to us for a very long time. It seems that we just failed to notice. Though PowerShell, we can start executing CmdLets, small pieces of code, and combine pieces of SQL Server with PowerShell to make everything a little bit easier.

Running PowerShell from SQLCMD

First, an undocumented parameter

An important thing to note (and required for PowerShell 2.0 users): When running PowerShell through SQLCMD, you should always include the following parameter:

 -InputFormat none

The problem is described here and, while PowerShell 3.0 behaves fine without this switch, I personally leave it in my scripts for backwards compatibility.

Want BCP? Remember where your server is

As I present these scripts, it's with the assumption that your client PC and your server are not the same machine. This means that I won't use BCP or other commands that assume your data is on the same computer as your server.

The script-signing barrier

Upon installation, PowerShell's default security level prohibits the execution of unsigned scripts. This is a very important feature and one that can easily be configured to an individual's needs. While changing this setting would make things easier, I like to find solutions that work "out of the box", without changes to system settings or additional software. Its with this goal in mind that I'm providing solutions that will execute without changing the default PowerShell security settings. There are easier and cleaner ways but (as far as I know) this is the only way to do it without extra steps.

Let's form a nice, long line...

The last thing to remember is that we're performing each PowerShell call in a single line with a semicolon ( ; ) between commands.While there are some tricks to improve readability, it still won't match up to the clean style you get from other code.

Hello PowerShell World

What script would be complete without the obligatory "Hello World"? All you have to do is make sure PowerShell is installed, turn on SQLCMD mode, and execute the following line:

!! PowerShell -InputFormat none -Command "& { Write-Output 'Hello PowerShell World!' }"

If everything went well, you'll see an output similar to this:

 

Let's Make a Numbers Table

Adam Mechanic has a post titled, You REQUIRE a numbers table! Adam took over 15 lines to write the SELECT part (probably because he had a choice) but we're going to do it in one line (because we don't have a choice). It's also going to be slower and less-legible than Adam's, but... well... We're doing it in PowerShell! That's got to count for something!

-- Execute CREATE TABLE from within our script
:OUT $(TEMP)\Numbers.sql
PRINT '
SET NOCOUNT ON
USE TempDB
GO
IF OBJECT_ID(''Numbers'') IS NULL CREATE TABLE Numbers (Number INT)
TRUNCATE TABLE TempDB..Numbers
GO
'
-- If you forget the GO statement, the lower code executes first.
GO
!! PowerShell -InputFormat none -Command "& { (1..1000) | ForEach-Object {Write-Output ('INSERT Numbers VALUES (' + $_ + ')' )} }"
-- Redirect output to the message pane
:OUT STDOUT
-- Execute our newly-created script.
:r $(TEMP)\Numbers.sql
-- Check for our numbers table
SELECT * FROM tempdb ..Numbers

After executing the above code, you should see results that look similar to this:

 

Note that the results won't show up in grid mode (even if you've chosen "Results to Grid"), but will be present in the "Messages" tab instead. This is a result of redirecting output to STDOUT.

SQLCMD variables for script block reuse

Finally, let's make it even easier to run our code. Nobody wants to risk a typo or waste screen real-estate when these PowerShell scripts start getting really long.

!! PowerShell -InputFormat none -Command "& { Write-Output 'Out with the old.' }"

:setvar PSCmd "PowerShell -InputFormat none -Command"

!!$(PSCmd) "& { Write-Output 'In with the new!' }"
!!$(PSCmd) "& { Write-Output 'This is a little easier to read.' }"

Next up: Say Hello to 450 million of your closest friends.

In the next post, I'll provide you with a 20-line script to generate a table of 450 million unique names. Following that, I'll take a break from SQLCMD and PowerShell to introduce something new (or new to me) on the SQL Server performance side.