T-SQL Command Line Options – Part 4 – SQLPSX

Powershell and SQLPSX

SQLPSX is an open source codeplex project that was created to provide an intuitive interface for interacting with SQL Server in Powershell. The module uses the SMO objects which are backwards compatible to SQL Server 2000 and also includes an add-in module for the Powershell ISE called SQLIse. There are even some reports.

To establish scope for this part of the T-SQL Command Line Options blog series; we will be covering the basic usage of SQLPSX where we will be invoking SQL commands. Even though that is what we will be covering here today, there is far more to play around with in this Powershell module. Once you are finished reading about all of your options, if you choose to use SQLPSX, I recommend taking a look at Chad Miller’s SQLPSX instructions on SQLServerCentral.com (see references below) to extend your knowledge.

References:

Why this method?

Similar to the .NET method that we covered in the previous blog part using SQLPSX is backwards compatible to SQL Server 2000. Unlike the previous option this does require a module to be installed and imported before it can be used but SQLPSX dramatically reduces the number of lines of code required to accomplish a given task and it contains several cmdlets with easy to understand names and well documented uses.

How to use it

Setup

1. Install the SQL Server Management Objects which is included with SQL Server Management Studio.
2. Install Powershell, if not already installed. Powershell comes packaged with Windows 7, 8 and Server 2008 R2 and 2012.
3. Set your Powershell execution policy to remote signed by running a Powershell console as administrator and execute the below code.

Set-ExecutionPolicy RemoteSigned

4. Download SQLPSX from the CodePlex documentation.

5. Install SQLPSX. I prefer to use the SQLPSX.msi but there are also manual installation instructions provided in the documentation.

Powershell profiles

When you open a Powershell session you will need to import the SQLPSX modules for all of the functions and cmdlets to be accessible. The primary means of doing this is the execute the following code.

Import-Module SQLPSX;

This works fine but is less than desirable when you have to run it every time you open a Powershell console window. What I like to do is to use the Powershell profiles to pre-import the module every time a new Powershell console is loaded. In order to do this you need to:

1. Navigate to My Documents and create a file with the name Microsoft.Powershell_profile.ps1. Example path for Windows 7 and 8:

C:\Users\<UserName>\Documents\WindowsPowerShell\Microsoft.Powershell_profile.ps1

2. Edit the Microsoft.Powershell_profile.ps1 file and paste in the line of code seen below.

Import-Module SQLPSX;

3.  Save and open a Powershell console to test.

Query and DML commands

You can view a full list of all of the SQLPSX functions by executing the below code snippet. In this section, we are going to stick to the Get-Sql and Set-Sql commands which cover the basic ability to execute queries and DML commands.

Get-Command *et-Sql*

Get-SqlData

The Get-SqlData cmdlet returns an ADO.NET data table which can be used as is or you can perform in memory filtering of the data set. This is useful if you want to limit the amount of SQL Server calls in a script that requires the same data set in multiple different formats or sub-result sets.

Executing the below command will return the data set of the sys.databases table and store it in the variable $dataset. The next line displays the result set. It is easy to see that this format for returning results is not ideal. It is difficult to read in the default format.

$server = Get-SqlServer '.\SQL2012';
$dataset = Get-SqlData $server master 'SELECT * from sys.databases;'
$dataset;

To correct this issue you can pipe the data set into the Format-Table cmdlet.

$dataset | Format-Table;

We can further clean-up this result set by returning only the name column of the query, like so.

$dataset | Select name;

Finally, we can filter the data set in memory, like so.

$dataset | Select name | Where {$_.name -eq 'master'};

All of the above uses of Get-SqlData produce the same result as seen in this example.

$dataset = Get-SqlData $server master "SELECT name from sys.databases WHERE name = 'master';"

Set-SqlData

The Set-SqlData operates just like the Get-SqlData function but is used for commands that do not return results, for example, DDL and DML operations. This next command will create a new table in the master database.

Set-SqlData $server master 'CREATE TABLE dbo.SQLHammer_Test (id INT NOT NULL, value NVARCHAR(1000) NOT NULL);'

Then we insert a record.

Set-SqlData $server master "INSERT INTO dbo.SQLHammer_Test SELECT 1, 'Don''t forget to subscribe to the SQLHammer.com RSS feed!';"

Finally we will loop back to our Get-SqlData command to retrieve the data.

Get-SqlData $server master 'SELECT * FROM dbo.SQLHammer_Test;'

What’s next?

In the next, and final, part of this blog series we will glance at the SQLPS module which comes packaged with SQL Server Management Studio 2012 and higher.

Navigation

  1. Part 1 – Introduction
  2. Part 2 – SQLCMD.exe using command prompt
  3. Part 3 – .NET objects using Powershell
  4. Part 4 – SQLPSX using Powershell (SQL Server 2000 and above)
  5. Part 5 – SQLPS using Powershell (SQL Server 2012 / 2014)

This article has 1 comment

Leave a Reply