Directory.Exists, File.Exists, Test-Path etc no longer work after importing SQLPS module in PowerShell

Standard

This was a bit of a strange one which pointed out how little I actually know about PowerShell! After importing the SQLPS module to allow me to query a database, I was no longer able to check my file structure as File.Exists just returned nothing, no error, just blank lines.

The issue is that when you import the SQLPS module the directory in which you are working changes to the SQLSERVER directory (more info can be found here and here). As you can see in the blog post the simple answer is just to set the working directory before importing the SQLPS module:

#Importing the "sqlps" module changes the current directory to the SQLSERVER drive which is an instance of the SQLSERVER Powershell Provider, so take a copy of the current location here:
$workingDirectory = (Get-Item -Path ".\" -Verbose).FullName

Import-Moduel "sqlps" -DisableNameChecking

#Get the Report Server URI from the Config table so we can use it in our script
$ReportServerURIQuery = Invoke-Sqlcmd -Query "SELECT ConfiguredValue FROM Config.dbo.Config WHERE ConfigurationFilter LIKE 'ReportServerURI';" -ServerInstance "Dev"

$ReportServerURI = $ReportServerURIQuery.ConfiguredValue | Out-String

#Change the directory back to where we were
cd $workingDirectory

Good luck!

Advertisements