Why working directory is changing when executing Invoke-sqlcmd?

Question

I am trying to search whether particualr machine name is there in database and if so we are replacing with new machine name.

We are using powershell

 Add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
    Add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue

    $SelectQuery = "SELECT [$columnName] FROM [$DatabaseName].[dbo].[$TableName] Where [$columnName] is not null;"

    try {
          $Qresult= Invoke-sqlcmd -query $SelectQuery -Database $DatabaseName -ServerInstance $srvInstance -Verbose
          $Qresult = $Qresult| % { $_.$columnName+"`n" 

           #check whether the Machine Name is found if the column value is processed in URL format
            $IsOldFoundFromURL=TestOldMachineFromURL $Qresult $OldMachineName

            #check whether the Machine Name is found if the column value is processed in Connection string format
            $IsOldFoundFromConn=TestOldMachineFromconnstring $Qresult $OldMachineName

            If ( $IsOldFoundFromURL -or  $IsOldFoundFromConn ) {

                    LogWrite "Columns Name before changing: $columnName "
                    LogWrite "$Qresult"
            }
            Else {
                    LogWrite "OldMachine name is not found in DB"
                    Return
            }
     }
     catch {
            Write-error "Error occured when executing sql $SelectQuery"
            LogWrite $Error[0]
        }

Every thing works fine . But when executing Invoke-sqlcmd the drive letter is changing to SQLServer:\ which is surprising.

I am running this in Windows 2012 R2 machine and executing this against sQL server 2012.

The problem is when the working directory is changing our script fails as we are writting a log file in current script path, when the script path changes to SQLserver:\ ,unable to create log file and fails.

Answer 1

This issue occurs as part of running the Add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue section of your code at the start. I'm not sure why it does this but I know that when I run a similar command (Import-Module 'sqlps' -DisableNameChecking) on computers in our network running MS SQL 2012, it changes the filesystem location from whatever location to the SQLSERVER:> location - I assume this is because it expects you to begin using commands in the SQL Server instance.

To overcome this problem, you can do a Push-Location and Pop-Location command to move back to your original location, like so;

C:\Users\foo.bar> Push-Location
C:\Users\foo.bar> Add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
SQLSERVER:\> Add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
SQLSERVER:\> Pop-Location
C:\Users\foo.bar> _

Either that or overcome the issue by factoring in the change in directory in your commands.