PSql

Cmdlets for SQL Server and Azure SQL databases.

Status

PSql 2.x is a C# rewrite of what previously was a script module. The script
module was used for for years in production code. PSql 2.x has seen two years
of production use too.

Installation

PSql requires PowerShell 7.2 or later and should work on any platform where
PowerShell runs.

To install PSql from PowerShell Gallery,
run this PowerShell command:

Install-Module PSql

To update PSql, run this PowerShell command:

Update-Module PSql

To check what version(s) of PSql you have installed, run this PowerShell command:

Get-Module PSql -ListAvailable | Format-List

Usage

PSql provides these cmdlets:

Every PSql cmdlet has built-in documentation. To view that documentation, run
a PowerShell command like this:

Get-Help Invoke-Sql -Full

The core function of PSql is to run T-SQL scripts. It can be this easy:

Invoke-Sql "PRINT 'Hello, world.'" -Database master

Or, using a pipe:

"SELECT * FROM sys.schemas" | Invoke-Sql -Database master

In its simplest form, Invoke-Sql assumes that the machine has a local
installation of SQL Server (or compatible product), that the installation is
registered as the default instance,
and that the current user can connect using integrated authentication.
If your situation is different, you need to prepare a SQL context object
that specifies how to connect to a database server.

$login = Get-Credential pgibbons
$context = New-SqlContext `
    -ServerName      initech1 `
    -DatabaseName    TpsReports `
    -Credential      $login `
    -ApplicationName "TPS Report Generator"

When connecting to Azure SQL Database (or compatible product), use the -Azure
switch, which enables some Azure-specific parameters, like resource group name
and Azure Active Directory authentication modes.

$login = Get-Credential pgibbons
$context = New-SqlContext -Azure `
    -ResourceGroupName  initech `
    -ServerResourceName initech-db01 `
    -DatabaseName       TpsReports `
    -AuthenticationMode AadPassword `
    -Credential         $login `
    -ApplicationName    "TPS Report Generator"

New-SqlContext supports a number of other parameters that generally
correspond to settings commonly specified in connection strings. Most of them
are optional. See the built-in help for New-SqlContext for details.

Once a SQL context is prepared, using (and reusing) it is easy:

Invoke-Sql "EXEC dbo.GenerateTpsReport" -Context $context

When used as above, Invoke-Sql opens a new connection for each invocation and
closes the connection once the invocation completes. In many situations, that
is adequate. However, some items, like temporary tables, disappear when their
connection closes. When those must persist across multiple uses of
Invoke-Sql, it is necessary to explicitly open and close a connection.

$connection = Connect-Sql -Context $context
try {
    Invoke-Sql "..." -Connection $connection
}
finally {
    Disconnect-Sql $connection
}

SQLCMD Compatibility

Invoke-Sql supports a limited set of preprocessing features intended to be
compatible with the sqlcmd
utility:

Preprocessor directives are case-insensitive. The GO, :setvar, and :r
directives must appear at the beginning of a line, and no other content may
appear on that line. $(…) may appear anywhere, including inside other
preprocessor directives.

To disable Invoke-Sql preprocessing, use the -NoPreprocessing switch.

Error Handling

By default, Invoke-Sql wraps SQL batches in an error-handling shim.
The wrapper improves the diagnostic experience by printing the batch that
caused an error. Here is an example:

There are a few known scenarios in which the error-handling wrapper can cause
an error, requiring the use of a workaround. The scenarios are:

• Multi-batch transactions. Transactions cannot span batches. If a batch
  begins a transaction but does not commit it (or vice versa), the batch will
  fail with an error.

  

• Multi-batch temporary tables. If a batch creates a temporary table, the
  temporary table is destroyed at the end of the batch. The temporary table is
  not visible to subsequent batches.

  

There are two ways to work around these known issues:

• Pass the -NoErrorHandling switch to Invoke-Sql. When this switch is
  used, the error-handling wrapper is omitted. SQL batches are executed bare.
  No enhanced error-handling is performed.

• Include this magic comment on any line in the batch:

  --# NOWRAP

  The magic comment must appear at the beginning of the line, and no other
  content may appear on that line. The comment causes Invoke-Sql to place
  the batch’s code verbatim into the error-handling wrapper’s TRY/CATCH
  block, rather than within an EXECUTE statement. This prevents the issues
  described above while preserving the enhanced diagnostics provided by the
  wrapper. The drawback is that script hygiene is no longer perfect: an error
  in the batch might interfere with the wrapper itself, preventing the
  error-handling from working as intended.

  To prevent nasty surprises with --# NOWRAP, use it only when required, and
  keep the batches using it as small as possible. Examples:

  --# NOWRAP
  BEGIN TRANSACTION;
  GO

  --# NOWRAP
  CREATE TABLE #T (X int NOT NULL);
  GO

Contributors

Many thanks to the following contributors:

@Jezour:
#1