Why working directory is changing when executing Invoke-sqlcmd?
powershell, sql-server-2012
Solution
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.
Problem
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.