PowerShell & SQL :: A Quick Fix for the SQLPS Problems

Filed under: Microsoft, powershell, SQL Server, Technology — Tags: , , , — Developer42 @ 18:19

There are a couple of issues with the SQLPS PowerShell module.

  1. It changes the current directory to PS SQLSERVER:\>.
  2. It may cause warnings (see end of this post for examples).

Calling the Import-Module-SQLPS function (the definition of which you’ll find below) instead of running Import-Module SQLPS will prevent such issues.

function Import-Module-SQLPS {
    #pushd and popd to avoid import from changing the current directory (ref:
    #3>&1 puts warning stream to standard output stream (see
    #out-null blocks that output, so we don't see the annoying warnings described here:
    import-module sqlps 3>&1 | out-null

"Is SQLPS Loaded?"
if(get-module sqlps){"yes"}else{"no"}


"Is SQLPS Loaded Now?"
if(get-module sqlps){"yes"}else{"no"}

NB: Though from PowerShell 3.0 onwards you don’t need to import modules; for SQLPS I’d recommend that you do import it; this ensures that you can control when in your script this happens, rather than the first command from that library (e.g. Invoke-SqlCmd statement) causing the script to load and thus your directory being changed and warnings being spewed.


Blog at

%d bloggers like this: