SQLPS.exe and SQLPS the module
SQLPS, like SQLPSX, is a set of extensions to Powershell for interacting with SQL Server. SQLPS first appeared with SQL Server 2008 in the form of a mini-console called SQLPS.exe. This mini-console was originally based on Powershell V1.0 but is updated to V2.0 in 2008 R2 and then V4.0 in 2014. Released in SQL Server 2012, came SQLPS the module. Powershell V2.0 and above supports modules which is an entirely new way of packaging a library of functions. While SQLPS.exe is a mini-console with pre-defined cmdlets which cannot be changed, SQLPS enables us to take advantage of the cmdlets from our current version of Powershell in addition to utilizing the SQL Server extension.
In this last part of the T-SQL Command Line Options blog series, I will explain how to set up both versions of SQLPS but I will only be analyzing the cmdlets from the newest version, SQLPS the module.
Running SQLPS.exe
Executing SQLPS.exe is as easy as any other executable. When it is run; the SQLPS mini-shell will launch. The first option is to make a shortcut to the executable location. The location is displayed below.
The next option is to simply access it via command prompt or Powershell. In either console you should be able to simply type “sqlps” to run the application.
The navigation of your SQL Server and the execution of queries is very similar to the SQLPS module, see below for those details.
Establishing a connection with SQLPS the module
SQLPS the module is included when you install SQL Server 2012. If you don’t have SQL Server 2012 you can create your own SQLPS module by following Chad Miller‘s article, Making A SQLPS Module.
Once installed you must import the module into your active Powershell console window. To do this execute the below script.
[sql]Import-Module SQLPS[/sql]
As seen above, you trigger a warning when first importing the SQLPS module. This can be ignored. All it is saying is that the SQLPS module is using non-standard verbs in their cmdlets. If you don’t wish to see this message, helpful if you put the import-module command into a profile file, simply run this script instead.
[sql]Import-Module sqlps -DisableNameChecking[/sql]
You’ll also notice that it will automatically change directory to the SQLSERVER PSDrive which is created by the module. You can use ‘cd’ to exit and re-enter this location at will.
Once your new PSDrive is accessed you can navigate through the above listed areas of SQL Server. This module was designed to do far more than simply invoke SQL commands but, unfortunately, many of the SQLPS features are out of scope for this blog series. We will, however, be covering the SQL commands in this next section.
Query execution with SQLPS the module
Executing a query or SQL command couldn’t be easier with SQLPS. Below is an example of the Invoke-SqlCmd cmdlet running a query, this can be done from any directory, there is no need to have SQLSERVER:\ as your working directory.
[sql]Invoke-Sqlcmd -Query "SELECT name FROM sys.databases;" -ServerInstance localhost\sql2012[/sql]
This next example runs the same query but from within the context of your working directory.
Wrap up
I hope this five part blog series on T-SQL command line options provided you some value. We covered the basic functionality of SQLCMD.exe, Powershell native .NET objects, the open source SQLPSX module, SQLPS.exe the mini-shell, and SQLPS the module. In addition to basic query execution and an overview of features we covered the purpose behind each’s use and recommendations as to when you should choose to use one over the other.
If you have a question you’d like answered or have an idea of a SQL Server topic that you’d like me to discuss please feel free to contact me at derik@sqlhammer.com or on twitter @sqlhammer.
Navigation
- Part 1 – Introduction
- Part 2 – SQLCMD.exe using command prompt
- Part 3 – .NET objects using Powershell
- Part 4 – SQLPSX using Powershell (SQL Server 2000 and above)
- Part 5 – SQLPS using Powershell (SQL Server 2012 / 2014)
Leave a Reply