Search code examples
powershellinvoke-sqlcmd

after running invoke-sqlcmd why is the fileSystem provider required to access UNC Server paths

after reading the following link I was able to resolve my own issue - but does anyone know why the FileSystem Provider is required here after using invoke-sqlcmd

ie. why does the following fail

$Doc_Folder = "\\Server.fqdn.com\c$\DocFiles" 
$DataSet = Invoke-Sqlcmd -Query $sqlQuery -ServerInstance $instanceName -username $SQLCreds.UserName -Password $pswd 
Get-ChildItem $Doc_Folder -recurse

and why does the following work

$Doc_Folder = "Microsoft.PowerShell.Core\FileSystem::\\Server.fqdn.com\c$\DocFiles" 
$DataSet = Invoke-Sqlcmd -Query $sqlQuery -ServerInstance $instanceName -username $SQLCreds.UserName -Password $pswd 
Get-ChildItem $Doc_Folder -recurse

This is the link where I found the solution to adding the file system provider Microsoft.PowerShell.Core\FileSystem:::
powershell-invoke-sqlcmd-get-childitem-cannot-call-method

Solution

  • The SQL Server modules that shipped with SQLServer prior to August of 2016 changed the directory to the SQLSERVER: drive.

    To get around this, you should install the SQLServer module (from PSGallery) or, if you need to use the older stuff (for compatibility reasons), you can do this:

    pushd
import-module SQLPS
popd

    That will restore the current directory after the module is imported.