0

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

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 }

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.

Then I have this: 

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

Then I want to do this: (Edit: 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'

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

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 Error is: Exception calling "Fill" with "1" argument(s): "Invalid pseudocolumn "$NewSequenceID"."

apyo
  • 1
  • 1
  • 1
  • 2
  • How/where do you want to add those variables? You need to show us your non-working code. There is nothing to fix in working code. – Ansgar Wiechers Jul 30 '13 at 20:41
  • Uh, are $NewSequenceID and $PollTime supposed to be the *names* of columns in the table, or *values* to search for? What are the names of the columns? – Adi Inbar Jul 30 '13 at 20:42
  • Was trying to explain with the first two working statements how I get the values I want to use in the third statement, which does not work. Where I've put SELECT $NewSequenceID, $PollTime, ... That does not work. @AdiInbar - they're meant to be values and will be the same for each row. – apyo Jul 30 '13 at 20:51
  • @AnsgarWiechers - I want to use those values in the Select statement to fill two fields. Then I get the other three [USERID], [PID], [UDATE] FROM [MyTable] to complete the final datatable. – apyo Jul 30 '13 at 20:53
  • If I substitute '1' and getdate() for those two variables in the same statement, it works fine and builds the table as expected. I want to use what's stored in $NewSequenceID and $PollTime instead of fixed values. – apyo Jul 30 '13 at 21:51
  • Don't describe what you think you're doing. **Show** the code and the exact error message. – Ansgar Wiechers Jul 30 '13 at 22:19
  • Thanks for the input on this one. I was able to work around this by first creating a variable to store the query text, which allowed for the natural substitution I needed: $AuditUserQuery = "SELECT '$NewSequenceID', '$PollTime', [USERID], [PID], [UDATE] FROM [AUDITUSER]" Then calling that variable as the query when building the datatable: $AuditUserOutput = invoke-sqlcmd2 -ServerInstance "MyServer2" -Database "MyDB2" -Query $AuditUserQuery -As 'Datatable' This avoided the parameterization problem inside invoke-sqlcmd2. – apyo Jul 30 '13 at 22:36

2 Answers2

1

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.

Adi Inbar
  • 12,097
  • 13
  • 56
  • 69
  • No, those columns don't exist yet. The where would rely on those columns being found in MyTable, and they're not. Sorry this one's confusing. The big picture is I need to know how to use a powershell variable value inside a SQL select or insert statement. It would probably make more sense if I said I wanted to insert the value into an existing column. That same syntax should solve this. In this case, I just haven't created that final table yet. I'm building $dt Datatable variable to do bit more work first. – apyo Jul 30 '13 at 21:05
  • I was able to work around this by first creating a variable to store the query text, which allowed for the natural substitution I needed: $AuditUserQuery = "SELECT '$NewSequenceID', '$PollTime', [USERID], [PID], [UDATE] FROM [AUDITUSER]" Then calling that variable as the query when building the datatable – apyo Jul 30 '13 at 22:40
0

I was able to work around this by first creating a variable to store the query text, which allowed for the natural substitution I needed: 

$AuditUserQuery = "SELECT '$NewSequenceID', '$PollTime', [USERID], [PID], [UDATE] FROM [AUDITUSER]"

Then calling that variable as the $query when building the datatable.

This avoided the parameterization problem experienced before.

apyo
  • 1
  • 1
  • 1
  • 2