SQL Server 2012 SQLPs Module - Changing current location automatically?

Question

I have a large library of existing powershell scripts, primarily to run some complicated logic and interactions between SQL Server and the file system.

The SQL interactions typically are performed through the cmdlet Invoke-SQLCmd.

Recently the SSMS version on all of my servers was upgraded to 2012. This meant I had to make some changes to my code to remove references to the sqlserverprovidersnapin100 and sqlservercmdletsnapin100 since those are deprecated in 2012 in favor of importing the SQLPs module.

This has worked OK, but it seems that the SQLPs module is at some point changing the current location to PS SQLSERVER> instead of <local folder>, which makes a lot of my other commands fail. For instance, if I run a Test-Path on a network drive, it works fine from the <local folder> location but will fail from the SQLSERVER location.

Is this a known issue? Is there a workaround or setting I can change for this?

The main script I am worried about is over 1000 lines and has run with no issues for over two years until we made this change, so I'd like to avoid doing a line by line test of it if I can.

Answer 1

A little more information on WHY in sql 2012 it is changing to the SQLSERVER: directory...

When powershell imports sqlps module, the manifest defines a post script file named SqlPsPostScript.ps1. This file (first line) changes the current location to the SQLSERVER: provider

On x64 bit box the module is located - C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS.

In powershell 3.0 automatic importing can make it difficult to tell when this will occur (in my experience).

Its not clear why (from a logical standpoint) it should do this. We don't want to touch the SQL server modules, so I guess you need to do an explicit set-location after import of the module. You might also look at this as a solution that's a little less hacky.

Push-Location
Import-Module sqlps
Pop-Location

Answer 2

I've solved this problem by adding a cd <local folder> as the first line of the script to break out of the provider. Feels hackish though.