PowerShell Invoke-Sqlcmd with Array item as variable in WHERE

Question

I'm not getting any output from this script. Not even an error msg... I think my problem is the use of a variable in the WHERE clause. I've tried escaping this $(item) with backtics but making no progress. Can anyone help me understand what I'm doing wrong?

$dbserver   = "AHDC389"
$dbase  = "FOO"
$table  = "$($dbase).dbo.BAR"

Import-Module “sqlps” -DisableNameChecking

$myArray    = @(Invoke-Sqlcmd "Select myColumn From adifferentserver.dbo.[mylookuptable]") | select-object -expand myColumn

ForEach ($item in $myyArray) {

    Invoke-Sqlcmd "Select * FROM $table Where FacilityID='$(item)' "

}

UPDATE:

So when I modify the script like below, is seems the variable for the Where= is not expanding out.

$dbserver   = "AHDC389"
$dbase  = "FOO"
$table  = "$($dbase).dbo.BAR"

Import-Module “sqlps” -DisableNameChecking

$myArray    = @(Invoke-Sqlcmd "Select myColumn From adifferentserver.dbo.[mylookuptable]") | select-object -expand myColumn

$myQuery    = "Select * FROM {0} Where FacilityID='{1}'" -f $table, $item

ForEach ($item in $mArray) {

    Write-Host $myQuery
    #Invoke-Sqlcmd $myQuery

}

The value of $myQuery is returned to the console indicating the $item is not being expanded:

Select * FROM  FOO.dbo.BAR Where Facility=''

If you build the string outside the loop then $item is null

EBGreen
– EBGreen

2013-08-16 18:17:05 +00:00
Commented Aug 16, 2013 at 18:17 — EBGreen
– EBGreen, Commented Aug 16, 2013 at 18:17

EBGreen · Accepted Answer · 2013-08-16 17:45:45Z

1

It is simply personal preference, but I build the string using the -format operator:

$cmdStr = 'Select * FROM {0} WHERE FacilityID={1}' -f $table, $item
Write-Host 'Executing: ' + $cmdStr
Invoke-Sqlcmd $cmdStr

answered Aug 16, 2013 at 17:45

EBGreen

38k12 gold badges69 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Colin Over a year ago

I still get no output to the console when I do this. I suspect he problem is the need for TSQL to use the ' in the WHERE='foo' clause.

EBGreen Over a year ago

You built the string outside of the loop.

Colyn1337 Over a year ago

@ColinA.White Don't, because you're not. We've all facepalmed

RobertKenny · Accepted Answer · 2013-08-16 18:20:42Z

0

$item is not defined until the ForEach loop so it cannot be used in the assignment prior to that. Move the declaration into the loop.

ForEach ($item in $mArray) {
$myQuery    = "Select * FROM {0} Where FacilityID='{1}'" -f $table, $item

Write-Host $myQuery
#Invoke-Sqlcmd $myQuery

}

answered Aug 16, 2013 at 18:20

RobertKenny

3,6362 gold badges21 silver badges17 bronze badges

1 Comment

RobertKenny Over a year ago

Just seen that @EBGreen answered as I typing.

Collectives™ on Stack Overflow

PowerShell Invoke-Sqlcmd with Array item as variable in WHERE

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related