如何将此sqlcmd脚本转换为Invoke-Sqlcmd脚本？

Question

我有一个旨在由sqlcmd执行的SQL脚本，以及一个使用正确参数执行sqlcmd的命令脚本。

我想将命令脚本转换为使用Invoke-Sqlcmd而不是sqlcmd的PowerShell脚本。

SQL脚本、命令脚本和新的PowerShell脚本都位于目录C:\Users\iain.CORP\SqlcmdQuestion中。

SQL脚本

该SQL脚本称为ExampleQuery.sql。它选择一个字符串文字。字符串文本的值由sqlcmd在运行时设置为ComputerName sqlcmd脚本变量的值。代码如下所示：

SELECT '$(ComputerName)';

命令脚本

命令脚本称为ExecQuery.cmd。它调用sqlcmd来执行ExampleQuery.sql，并将脚本变量ComputerName的值设置为环境变量COMPUTERNAME的值。代码如下所示：

sqlcmd -i ExampleQuery.sql -v ComputerName = %COMPUTERNAME%

当我打开命令提示符时，默认的工作目录是C:\Users\iain.CORP。我将切换到包含文件的目录，并运行命令脚本：

cd C:\Users\iain.CORP\SqlcmdQuestion
ExecQuery.cmd

我看到了下面的输出：

---------
SKYPC0083

(1 rows affected)

该脚本成功选择了由sqlcmd设置的字符串文字。

PowerShell脚本

PowerShell脚本称为ExecQuery.ps1。它应该与命令脚本执行相同的操作，使用Invoke-Sqlcmd而不是sqlcmd。代码如下所示：

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Invoke-Sqlcmd -InputFile 'ExampleQuery.sql' -Variable "ComputerName = $Env:COMPUTERNAME"

当我打开PowerShell提示符时，默认的工作目录是Z:\。我切换到包含这些文件的目录，然后运行PowerShell脚本：

cd C:\Users\iain.CORP\SqlcmdQuestion
.\ExecQuery.ps1

我看到了下面的输出：

Invoke-Sqlcmd : Could not find file 'Z:\ExampleQuery.sql'.
At C:\Users\iain.CORP\SqlcmdQuestion\ExecQuery.ps1:4 char:14
+ Invoke-Sqlcmd <<<<  -InputFile 'ExampleQuery.sql' -Variable "ComputerName = $Env:COMPUTERNAME"
    + CategoryInfo          : InvalidResult: (:) [Invoke-Sqlcmd], FileNotFoundException
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

由于Invoke-Sqlcmd在Z:\目录中找不到输入文件，PowerShell脚本会引发一个错误，该目录恰好是默认的工作目录。

命令脚本在当前工作目录中找到该脚本。

如何让Invoke-Sqlcmd使用当前工作目录而不是默认工作目录？

Answer 1

对于这个答案，假设目录C:\Users\iain.CORP\SqlcmdQuestion存在，并且在该位置执行dir将生成以下输出，如问题所示：

    Directory: C:\Users\iain.Corp\SqlcmdQuestion


Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---        26/09/2012     15:30         27 ExampleQuery.sql
-a---        26/09/2012     15:30         61 ExecQuery.cmd
-a---        26/09/2012     15:34        172 ExecQuery.ps1

PowerShell故意忽略工作目录

我的问题有一个错误的前提：

如何让Invoke-Sqlcmd使用当前工作目录而不是默认工作目录？

cmdlet确实使用当前工作目录。问题是，我在PowerShell会话中根本没有更改工作目录。

在PowerShell中，cd是Set-Location cmdlet的别名。您可以使用Get-Alias cmdlet来证明这一点：

Get-Alias cd

输出：

CommandType     Name                                                Definition
-----------     ----                                                ----------
Alias           cd                                                  Set-Location

Alex Angelopoulos解释说：

虽然PowerShell的位置类似于工作目录，但它与工作目录不是一回事。事实上，PowerShell并不涉及工作目录。

Set-Location不设置工作目录。它设置工作位置，这是PowerShell中类似但不同的概念。

您可以通过在使用cd设置工作位置后使用.NET属性Environment.CurrentDirectory检查工作目录来证明这一点，如问题所示：

cd C:\Users\iain.CORP\SqlcmdQuestion
Environment::CurrentDirectory

输出：

Z:\

我猜这个设计决定是一致的。例如，当工作位置被设置为注册表配置单元时，工作目录将是未定义的。

Invoke-Sqlcmd违反了这个设计原则

Invoke-Sqlcmd使用工作位置而不是工作目录违反了PowerShell的一般设计原则。大多数cmdlet使用工作位置来解析相对路径，但Invoke-Sqlcmd是个例外。

使用ILSpy disassembler和一点直觉来检查包含程序集的Microsoft.SqlServer.Management.PSSnapins，我相信我已经找到了错误的原因。

我相信cmdlet的参数-InputFile是由IncludeFileName方法实现的。ILSpy对该方法的反汇编如下：

// Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor
public ParserAction IncludeFileName(string fileName, ref IBatchSource pIBatchSource)
{
    if (!File.Exists(fileName))
    {
        ExecutionProcessor.sqlCmdCmdLet.TerminateCmdLet(new FileNotFoundException(PowerShellStrings.CannotFindPath(fileName), fileName), "ExecutionFailureException", ErrorCategory.ParserError);
        return ParserAction.Abort;
    }
    BatchSourceFile batchSourceFile = new BatchSourceFile(fileName);
    pIBatchSource = batchSourceFile;
    return ParserAction.Continue;
}

Invoke-Sqlcmd使用.NET方法File.Exists检查指定的输入文件是否存在。该方法的documentation说明使用工作目录解析相对路径：

允许path参数指定相对或绝对路径信息。相对路径信息被解释为相对于当前工作目录。要获取当前工作目录，请参见GetCurrentDirectory。

这表明在这种情况下，File.Exists将返回false，这将导致问题中看到的错误消息。您可以通过直接从提示符处执行该方法来证明这一点：

cd C:\Users\iain.CORP\SqlcmdQuestion
[IO.File]::Exists('ExecQuery.sql')

输出：

False

该方法返回false，因此cmdlet将终止，并显示“找不到文件”错误。

您可以解决不寻常的行为

Invoke-Sqlcmd有两种变通方法，使用工作目录而不是工作位置来解析相对路径：

1. 始终使用绝对路径作为-InputFile参数的值。CandiedCode's answer显示了如何执行此操作。
2. 设置工作目录并使用相对路径。

我通过像这样修改ExecQuery.ps1解决了这个问题，没有副作用：

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

$RestoreValue = [Environment]::CurrentDirectory
[Environment]::CurrentDirectory = Get-Location
Invoke-Sqlcmd -InputFile 'ExampleQuery.sql' -Variable "ComputerName = $Env:COMPUTERNAME"
[Environment]::CurrentDirectory = $RestoreValue

我看到了下面的输出：

Column1
-------
SKYPC0083

成功！

在执行Invoke-Sqlcmd之前，新脚本将工作目录设置为与工作位置匹配。为了避免更改工作目录的意外副作用，scrtipt会在完成之前恢复工作目录值。

本Channel 9 thread中介绍了如何设置当前目录。这里的示例使用了Directory.SetCurrentDirectory方法，但我发现直接设置该属性更简单。

Answer 2

您可以完全限定Inputfile位置：

Invoke-Sqlcmd -InputFile 'C:\Users\iain.CORP\SqlcmdQuestion\ExampleQuery.sql' -Variable "ComputerName = $Env:COMPUTERNAME"

并使用一个变量来驱动脚本位置：

$FileLocation = 'C:\Users\iain.CORP\SqlcmdQuestion\'