在 out-datatable (invoke-sqlcmd2) 期间将 powershell 变量传递给 SQL 值

问题描述

当我从 SQL 查询构建数据表时，我想插入一个带有 Select 的 PowerShell 变量值.

I want to insert a PowerShell variable value with a Select as I build a datatable from a SQL query.

从 TechNet 库中借用函数 invoke-sqlcmd2 并将其点源.

Borrowed function invoke-sqlcmd2 from TechNet gallery and dot-sourced it in.

$NewSequenceID = invoke-sqlcmd2 -ServerInstance "MyServer" -Database "MyDB" -Query "INSERT INTO [Sequence] (TimeStarted) SELECT GETDATE(); SELECT max(SequenceID) as SequenceID FROM [Sequence]" | foreach { $_.SequenceID }

这会生成一个新的序列 ID 并标记我们开始批处理的时间.结果是一个单一的数字，它将识别这次运行.使用写入 $NewSequenceID"进行验证.我想将查询的后续结果与此 SequenceID 一起保留以进行分析.

This generates a new sequence ID and stamps the time we started the batch. Results in a single number which will identify this run. Verified with 'write $NewSequenceID'.I want to keep later results from queries together with this SequenceID for analysis.

然后我有这个:

$PollTime = Get-Date -format "yyyy-MM-dd HH:mm:ss"

然后我想这样做:(此语句不起作用 - 底部的错误消息)

Then I want to do this: ( This statement is not working - error message at the bottom)

$AuditUserOutput = invoke-sqlcmd2 -ServerInstance "MyServer2" -Database "MyDB2" -Query "SELECT $NewSequenceID, $PollTime, [USERID], [PID], [UDATE] FROM [MyTable]"  -As 'Datatable'

然后用table做一些事情，然后用write-datatable写出来.

And do some things with the table, then write it after with write-datatable.

如果我为前两个值选择 NULL 并从现有表中获取其他三个值，它工作正常.我想从前面的语句中添加 $NewSequenceID 和 $PollTime.

If I select NULL for the first two values and grab the other three from the existing table, it works fine. I want to add the $NewSequenceID and $PollTime from the previous statements.

我已经阅读了十几页关于使用 `(反引号)、$、{} 等等的内容，但我没有理解正确.有人可以帮助使用正确的语法将这些变量值插入到选择中吗?

I've read a dozen pages about using ` (backtick), $, {}, and on and on, but I haven't gotten it right. Can someone help with the correct syntax for inserting these variable values into the selection?

PS 错误是:用1"个参数调用Fill"的异常:伪列$NewSequenceID"无效."

PS Error is: Exception calling "Fill" with "1" argument(s): "Invalid pseudocolumn "$NewSequenceID"."

推荐答案

您正在 PowerShell 中正确插入变量.如果我理解正确，问题出在您的 SQL 查询上.我将在这里进行推断，但我认为这可能是您想要的:

You're interpolating the variables correctly in PowerShell. If I'm understanding this correctly, the problem is with your SQL query. I'm going to make an inference here, but I think this is probably what you want:

$AuditUserOutput = invoke-sqlcmd2 -ServerInstance "MyServer2" -Database "MyDB2" -Query "SELECT [NewSequenceID], [PollTime], [USERID], [PID], [UDATE] FROM [MyTable] WHERE NewSequenceID = '$NewSequenceID' AND PollTime = '$PollTime'"  -As 'Datatable'

如果不是，请通过回答上述问题来澄清.

If not, please clarify by responding to the questions above.

这篇关于在 out-datatable (invoke-sqlcmd2) 期间将 powershell 变量传递给 SQL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！