問題描述
當我從 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 值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！