Why I don’t use Invoke-SQLCmd

Unpopular Opinion Alert!

I hope you’re wide awake and ready to get mad, because I have a very unpopular opinion regarding SQL Server and PowerShell: I don’t like using Invoke-SQLCmd . That’s not to say I haven’t used it; some of my older scripts on my GitHub use it but recently I have stopped. I think the cmdlet is of marginal use when compared to other methods to executing queries against a SQL Server database. Maybe you agree, but you probably don’t, so before you grab the torches and pitchforks and head to the comments, I encourage you to read on. It’s a little long, but I think it outlays my thoughts pretty well.

But first, a history lesson

When I first started learning to use PowerShell with SQL Server and I wanted to execute a query, I used to write things like this. Stop me if this looks familiar:

$connectionString = "Data Source=localhost; Integrated Security=SSPI; Initial Catalog=master"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand("SELECT * FROM sys.databases",$connection)
$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
$connection.Close()

When I first saw that I thought to myself “Self, why am I using a scripting language to write .NET code?” And that’s what it was, for something so simple as just returning a list of databases on a server. Fortunately, there were handy SMO objects to help. The code was “better”, but it wasn’t what I’d call “intuitive” or even “correct:”

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$SQLSvr = "localhost"                              
$ServerObject = new-object "Microsoft.SqlServer.Management.Smo.Server" $SQLSvr
$Databases = $ServerObject.Databases

The code above is pretty endemic of what I see in a lot of blog posts or on various forums around the internet on how to use SMO objects. There’s just one big glaring problem with it: [Reflection.Assembly]::LoadWithPartialName is deprecated (scroll down to the remarks section). The updated way to load a library in PowerShell is Add-Type:

Add-Type -AssemblyName "Microsoft.SqlServer.SMO,Version=13.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91“

That still isn’t great; because you then have to specify a version to load (and that PublicKeyToken… try typing that over and over). Not ideal if you want to share scripts with people running older (or newer) versions of libraries released with SQL Server.

The reason people do this is because of SQLPS, the oft-maligned (sometimes rightly so) SQL Server PowerShell module. Personally, I’ve never had a problem with it because I’ve either learned to live with some of the shortcomings or I saw it for what it really was: a module that loaded all the required SMO libraries, added a PSDrive for SQLSERVER and then got the hell out of your way. Cmdlet support was sparse (it’s getting better, more on that below), often buggy, and lord help you if you installed multiple versions of Management Studio and/or SQL Server on your machines.

Using the module, however, had some signifcant improvements in your scripting. Take for example the code I used above: returning databases from a server. Instead of doing all that work, if I imported the SQLPS module with import-module sqlps and then I could just do this:

$Databases = Get-ChildItem -Path "SQLSERVER:\SQL\LOCALHOST\DEFAULT\DATABASES" 

It’s utterly brilliant and it does exactly what you want it to do: connect to localhost, to the default instance, and get all the databases. With Get-ChildItem, a cmdlet that supports piping and outputs an object. It’s beautiful.

You said this was about Invoke-SQLCmd?

The reason I bring all that up is that like most people, you want to load a module to get at the new cmdlets it offers, like the gooey caramel of a certain type of candy bar I have had a craving for recently . After you load the SQLPS module, you’ll discover a little cmdlet called Invoke-SQLCmd . The syntax looks like this:

Invoke-Sqlcmd [[-Query] <String>] [-AbortOnError] [-ConnectionTimeout <Int32>] [-Database <String>] [-DedicatedAdministratorConnection] [-DisableCommands] [-DisableVariables] [-EncryptConnection] [-ErrorLevel <Int32>] [-HostName <String>] [-IgnoreProviderContext] [-IncludeSqlUserErrors] [-InputFile <String>] [-MaxBinaryLength <Int32>] [-MaxCharLength <Int32>] [-NewPassword <String>] [-OutputSqlErrors <Boolean>] [-Password <String>] [-QueryTimeout <Int32>] [-ServerInstance <PSObject>] [-SeverityLevel <Int32>] [-SuppressProviderContextWarning] [-Username <String>][-Variable <String[]>] [<CommonParameters>]

If you scan through that list there’s maybe…4(?) parameters you care about: -Query, -Database, -ConnectionTimeOut, and -QueryTimeout (because if you’re like me, you never write a query right the first time). But that’s not the only “problem” with the cmdlet, it hides a far more sinister secret: it’s really just a fancy wrapper for sqlcmd:

Invoke-Sqlcmd is a SQL Server cmdlet that runs scripts that contain statements from the languages (Transact-SQL and XQuery) and commands that are supported by the sqlcmd utility¹.

That’s right, everyone’s favorite command-line tool for using SQL server has it’s own PowerShell cmdlet! Which means that, if you’re doing more than just a simple select statement or running an update, you don’t get very many bells and whistles.  What do I mean? Well, let’s look at two use cases:

Example #1: Executing a stored procedure that returns more than one result set

To set up for this demo, we’re going to create a simple stored procedure. The trick is, we’re going to have the stored procedure return TWO sets of results. Here’s the code. I’m using AdventureWorks2014 but you can create this anywhere really, because all I’m doing is selecting from system views.

CREATE PROCEDURE MultiResultSet
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM sys.databases;

    SELECT * FROM sys.database_principals;

END
GO

Here’s the output from the stored procedure when I run it from Management Studio. I get 7 rows from the first result set and 16 from the second one for a total of 23:

Now, let’s run two different PowerShell commands. The first is Invoke-SQLCmd :

$results = invoke-sqlcmd -ServerInstance localhost -Database AdventureWorks2014 -Query "EXEC dbo.MultiResultSet"

When we return the results to the aptly-named $results variable, we can then output some of the results and look at the members. I encourage you to. But right now, I’m just going to use $results.Count . And what I get is:

Which you might think, “Gee Drew, isn’t that what you wanted?” Not really, because if I look at the contents of the results or type $results | Get-Member I see something less than ideal:

What happened? Well, it clobbered all my results together into one custom object. What I got returned is an array of objects with properties for the columns. But where does one result set end and the other begin? You could figure it out by looking at each object in the array and counting properties, but that’s clumsy. Instead, let’s use Database Objects and one of two very overlooked methods: .ExecuteWithResults() .

To do so we need to first get the database object. We can either put this in a variable or use a handy one-liner:

$results = (get-childitem -path "SQLSERVER:\SQL\Localhost\DEFAULT\Databases" | where-object {$_.Name -eq "AdventureWorks2014"}).ExecuteWithResults("EXECUTE dbo.MultiResultSet")

Instead of using Invoke-SQLCmd  we’re using Get-ChildItem  to return a specific database (specified in the Where-Object ). Database objects have cool methods, and this one, ExecuteWithResults() , does exactly what it says. But the real magic is in what it returns: a DataSet object. So when I get output this object, I get this:

At first, this doesn’t look like much. But the really juicy part of this object is that each result set is stored as a data table inside this object! So that means, I can do this:

Pretty awesome, right? Now I can keep the results separated so I can work with different results easier. That’s a huge difference, and one I think merits using .ExecuteWithResults() even for queries that return just one result set. But what about if we want to run a query, and we don’t want (or expect) any results? I’m glad you asked.

Example #2: Modifying data: how many rows did I affect?

Full disclosure: I don’t query databases from PowerShell unless I absolutely have to; after all, I’m scripting automation and not developing a front-end. If I can’t get what I need from a existing object’s properties, I’m probably just wasting my time and I should probably just write SQL instead. But sometimes you might want to develop automation and log results in a database, or use a table to get the next set of commands… my point is, there are cases where you want to do it, but there are probably fewer cases where you’re going to use PowerShell to actually change data.

If you wanted to though, there’s nothing to stop you from doing it with Invoke-SQLCmd. For this example, let’s say I have a simple table like this:

CREATE TABLE dbo.SomeTable
(
    ID INT PRIMARY KEY IDENTITY(1,1),
    BitField BIT NOT NULL,
    TextField NVARCHAR(255) NOT NULL
)

And then I wanted to insert a new row into it. My command would be:

$results = Invoke-Sqlcmd -ServerInstance localhost -Database AdventureWorks2014 -Query "INSERT INTO dbo.SomeTable (BitField, TextField) VALUES (1, 'Drew')"

If I look at the contents of $results, it’s empty! Was my command successful? I didn’t get any errors, so maybe? How many rows were affected? We don’t really know unless we query the database and find out. Much like the method above, database objects have another special method in them: .ExecuteNonQuery(). I think the name has a lot to be desired, but before we explain why it’s named the way it is, let’s run the following command:

(Get-ChildItem -Path SQLSERVER:\SQL\LOCALHOST\DEFAULT\DATABASES | Where-Object {$_.Name -eq "AdventureWorks2014"}).ExecuteNonQuery("INSERT INTO dbo.SomeTable (BitField, TextField) VALUES (1,'Drew')")

Unfortunately, that doesn’t do it either: nothing gets returned. But should it? Well, that’s where things get a little confusing. See, .ExecuteNonQuery() is a method on two different objects. A database object, which we just tried, and a SQLCommand Object. If you use the former, it’s just a “fire and forget” query that does what you tell it and ignores ALL results, even rows affected. In the latter, it returns an integer object of the rows affected. If that’s what you want however, it takes us… unfortunately right back where we started. The code looks like this:

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = "Persist Security Info=False;Integrated Security=true;Initial Catalog=AdventureWorks2014;server=localhost"
$Command.CommandText = "INSERT INTO dbo.SomeTable (BitField, TextField) VALUES (1,'Drew')
$Command.ExecuteNonQuery()

Running that code will return an integer, which represents the number of rows inserted/updated/deleted when doing DML statements. It’s messy, and it’s everything I said I hated at the start of this post. I want useful objects that have methods to do this stuff for me! I guess if you’re looking for any sort of silver lining in that mess, those are .NET common objects, and you don’t need to load any libraries or import the SQLPS module. So, what can we do in the meantime?

More than a personal preference?

I’m sure a lot of people will read this and say it’s all rubbish; why bother with what works? After all, almost everyone (especially DBAs who are new to PowerShell) probably discover this cmdlet first and never look back. And I’m not saying it’s a bad cmdlet! But I think it’s not quite the most useful one you should have in your toolbox. Instead, I coach people to work with database objects instead by using the module paths and executing queries directly against them. If you’re new to PowerShell or programming in general the syntax may look little goofy, but it helps you understand objects much better than flinging queries at a a cmdlet. You could also argue that my two use cases above border on edge cases. Maybe, but sooner or later you may have to do it and you might as well go with the solution that works for simpler queries as well as more complex ones. And as far as modifying data goes, as much as I love PowerShell, I’d rather leave data-changing DML statements up to stored procedures ESPECIALLY if I want to call them in some form of automation as a query string. Plus I hate writing connection strings.

A new hope?

Right after I started work on this blog post, Microsoft announced the SQL PowerShell July 2016 update. In it, they added some nice enhancements to Invoke-SQLCmd:

Invoke-SqlCmd now supports an OutputAs parameter (or its alias -As). This parameter allows you to specify DataRows, DataTables or DataSetas the object type to return. These types map to the .Net types you find in System.Data. DataRows is the default, and corresponds to the old behavior.

Additionally, we added the ConnectionString parameter which allows the script author complete control over the connection context. This unlocks new capabilities such as connecting to SQL Azure using Azure Active Directory authentication².

This is awesome: now Invoke-SQLCmd  can return datasets, so if you wanted multiple result sets returned from the command, now you can have it. That’s good! But I don’t think it goes quite far enough. Instead, what I’d love to see is .ExecuteNonQuery()  expanded for database objects to return an integer of rows affected like the SQLCommand method does. That way I can use commands against database objects to do both types of queries without writing a bunch of extra code. Or, if you REALLY wanted to entice me to use Invoke-SQLCmd , let me pipe a database object to it so I don’t have to specify anything but the query text. Then, add support in the result sets to add rows affected as a property. That would work beautifully. For me, at least.

The July update also adds support for connection strings for use with Invoke-SQLCmd  which I don’t think is that big of a deal. Yes, that means you can add spiffy read only routing to them. But again, I have to ask: what could you possibly be querying, with a PowerShell script, IN PRODUCTION, that you’re worried about making sure you’re on the secondary? Maybe you’re trying to create audits or health checks on your data, and you want to move that data around or export it. But isn’t that something for SSRS/IS, or even SQL Agent jobs? To me, that’s overkill for PowerShell scripting, and it wouldn’t be my first choice as a solution.

Hold up, did I just recommend you DON’T use PowerShell for something? Maybe this blog post is going on too long, and I should go have a nap.

What do you think? I’d love to hear your comments, so fire away, and thanks for reading!

1. https://msdn.microsoft.com/en-us/library/cc281720.aspx
2. https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/