为什么在执行 Invoke-sqlcmd 时工作目录会发生变化? [英] Why working directory is changing when executing Invoke-sqlcmd?

问题描述

我正在尝试搜索数据库中是否存在特定的机器名称，如果有，我们将替换为新的机器名称.

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

我们使用的是powershell

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]
        }

一切正常.但是在执行 Invoke-sqlcmd 时，驱动器号更改为 SQLServer:\，这令人惊讶.

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

我在 Windows 2012 R2 机器上运行它并针对 SQL Server 2012 执行它.

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

问题是当我们在当前脚本路径中写入日志文件时工作目录更改我们的脚本失败，当脚本路径更改为 SQLserver:\ 时，无法创建日志文件并失败.

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.

推荐答案

此问题是在开始时运行代码的 Add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue 部分的一部分时发生的.我不确定为什么要这样做，但我知道当我在运行 MS SQL 2012 的网络中的计算机上运行类似的命令 (Import-Module 'sqlps' -DisableNameChecking) 时，它会更改文件系统从任何位置到 SQLSERVER:> 位置的位置 - 我认为这是因为它希望您开始在 SQL Server 实例中使用命令.

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.

为了解决这个问题，你可以执行一个Push-Location 和Pop-Location 命令来移动回你原来的位置，就像这样；

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.

这篇关于为什么在执行 Invoke-sqlcmd 时工作目录会发生变化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！