SQL Server 2012 Import-Module 'sqlps' breaks the "Test-Path" PowerShell cmdlet

Question

I've run into something that is "very" frustrating with the new SQL Server 2012 PowerShell module.  When I Import the module, it breaks the "Test-Path" cmdlet when trying to test a UNC path to a directory.

For example:  

"Test-Path -path \\server\dirname" returns true as expected before the sqlps module is imported.  But after you import the SQL Server module "Import-Module 'sqlps' –DisableNameChecking" the same Test-Path now returns false.

If I run the following in Windows PowerShell ISE I see the following results:

Test-Path -path "\\server\directoryname"
Import-Module 'sqlps' –DisableNameChecking
Test-Path -path "\\server\directoryname"

True
False

Anyone have any idea what's going on?

UPDATE: after more testing, it looks like the problem happens with any cmdlet that references a UNC.  The New-Item has the same problem.  Before importing 'sqlps', New-Item is able to create a directory at the UNC path specified, but ater importing 'sqlps', the New-Item fails.

Thanks!

Answer 1

Hi Mikea730,

Sqlps.exe doesn't take advantage of a couple of these nice PowerShell V2 cmdlets without doing a bit of configuring in your environment. 

Please refer to the following references to make some configuration in your server

http://www.maxtblog.com/2010/11/denali-get-your-sqlpsv2-module-set-to-go/

http://www.simple-talk.com/sql/database-administration/practical-powershell-for-sql-server-developers-and-dbas-%E2%80%93-part-1/

http://sev17.com/2010/07/making-a-sqlps-module/

Thanks,

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Iric Wen

TechNet Community Support

Answer 2

Iric,   Thanks for the reply, do appreciate it!

Unfortunately I don't have time to build a new sqlps module.  Seems a bit ridiculous to me!  There must be an easier solution than this???

Guess I'll just stick with the 2008 R2 SMO for now.   Looks like sqlps is a big step backwards in that it it impact cmdlets that are not part of the module!  

Answer 3

Could you please report this issue to https://connect.microsoft.com/SQLServer/Feedback?

Thanks

Sethu Srinivasan [MSFT]

SQL Server

Answer 4

Thanks for the quick replies everyone. I submitted the following:

https://connect.microsoft.com/SQLServer/feedback/details/768743/sql-server-2012-import-module-sqlps-breaks-the-test-path-powershell-cmdlet

Is any one else able to reproduce this?

Test-Path -path "\\server\directoryname"
Import-Module 'sqlps' –DisableNameChecking
Test-Path -path "\\server\directoryname"

True
False

Thanks!

Answer 5

After importing the 'sqlps' module, your location is changed to the SQLSERVER drive, as indicated by the powershell prompt:

PS SQLSERVER:\>

This drive is an instance of the SQLSERVER Powershell Provider.

The core powershell cmdlets (Clear-Item, Convert-Path, Copy-Item, Get-Item, Get-Location, Invoke-Item, Join-Path, Move-Item, New-Item, Pop-Location, Push-Location, Remove-Item, Rename-Item, Resolve-Path, Set-Item, Set-Location, Split-Path, Test-Path, ...) will assume the item, path or location you specify as an argument of your cmdlet is relative to the Powershell Provider you are currently using.

The UNC path is meaningful to the FileSystem Provider, but not to the SQLServer Provider (which you are currently using).

To resolve your issue, you could (1) explicitly specify the FileSystem provider in your path or (2) switch to a drive of providertype FileSystem before invoking the Test-Path cmdlet for the second time:

Solution 1:

Test-Path -path "\\server\directoryname"
Import-Module 'sqlps' –DisableNameChecking
test-path -path  Microsoft.PowerShell.Core\FileSystem::\\server\directoryname

Solution 2:

Test-Path -path "\\server\directoryname"
Import-Module 'sqlps' –DisableNameChecking
C:
Test-Path -path "\\server\directoryname"

 

Answer 6

I see the same issue with other Powershell providers as well ( Example: Registry Provider ). This issue may not be SQL Powershell specific issue

I ran the following commands in Windows Powershell 2.0 and Windows powershell 3.0

PS C:\Users> New-PSDrive -PSProvider registry -Root HKEY_CLASSES_ROOT -Name HKCR

set-location HKCR:

PS HKCR:\> test-path \\machine\share

False

PS HKCR:\> c:
PS C:\Users> test-path \\machine\share
True
PS C:\Users>

Thanks

Sethu Srinivasan [MSFT]

SQL Server

Answer 7

Workaround:

test-path -literalpath "FileSystem::\\machine\uncpath"

Reference:
http://mcpmag.com/articles/2011/05/16/handling-errors-scripts-test-path.aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/ee126188(v=vs.85).aspx

Thanks

Sethu Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent

Answer 8

Thanks everyone.   The following variations work.

Test-Path -path "Microsoft.PowerShell.Core\FileSystem::\\machine\uncpath"
Test-Path -literalpath "FileSystem::\\machine\uncpath"
Test-Path -path "FileSystem::\\machine\uncpath"

Answer 9

Hopping in very late, but here are just my 2ct on how to avoid this pitfall:

1) Don't use #Requires for SQLPs !

2) Don't rely on Autoload for SQLPs !

3) use this to load SQLPs:

# Check module SQLPS
 if ( !(get-module -name SQLPs ) ) {

 # save original location
 Push-Location
 # SQLPs will set the current location to SQLSERVER:\ !!
    # -DisableNameChecking -> avoid remarks abount non-discouverable function names
 import-module -name SQLPs -DisableNameChecking | out-null
 
 #reset current location to original location
 Pop-Location
 
    }

---

Don't drive faster than your guardian angel can fly......

Answer 10

Thanks a lot for the info it worked with by mentioning the Filesystem Provider in front of file and running the SQL Agent job.

Earlier: $filePath = "\\ServerName\data\CSSM.MEX*"

Error : Cannot retrieve the dynamic parameters for the cmdlet. Invalid Path: '\\ServerName\data'.  '. 

After change : $filePath = "FileSystem::\\ServerNAme\data\CSSM.MEX*"

No errors :)