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

  • 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!


    • Edited by mikea730 Wednesday, October 24, 2012 12:58 AM
    Wednesday, October 24, 2012 12:44 AM

Answers

  • 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"


     


    • Edited by zjo Saturday, November 3, 2012 8:42 PM incorrect use of parameter/argument
    • Marked as answer by mikea730 Monday, November 5, 2012 4:00 PM
    Saturday, November 3, 2012 8:40 PM

All replies

  • 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

    Thursday, October 25, 2012 8:03 AM
  • 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!  

    Thursday, October 25, 2012 4:19 PM
  • Could you please report this issue to https://connect.microsoft.com/SQLServer/Feedback?

    Thanks

    Sethu Srinivasan [MSFT]

    SQL Server

    Thursday, October 25, 2012 5:53 PM
  • 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!

    Thursday, October 25, 2012 7:25 PM
  • 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"


     


    • Edited by zjo Saturday, November 3, 2012 8:42 PM incorrect use of parameter/argument
    • Marked as answer by mikea730 Monday, November 5, 2012 4:00 PM
    Saturday, November 3, 2012 8:40 PM
  • 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

    Saturday, November 3, 2012 9:25 PM
  • 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

    Saturday, November 3, 2012 9:36 PM
  • 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"

    Monday, November 5, 2012 3:59 PM
  • 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......

    Monday, February 23, 2015 2:21 PM
  • 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 :) 

    Friday, October 5, 2018 12:41 AM