I am using "SQL Server PowerShell Extensions" from codeplex and I
think I am having a punctuation issue with powershell vs. the SQL
query. I am pretty sure that it is single quotes vs. double quotes
and how posh vs t-sql are interpreting them. I get 10 numbers from
my first statement, but in the second one is not interpreting my
variables correctly. Can someone give me a push in the right
direction?



cd c:\scripts\sqltools
.. .\LibrarySmo.ps1
$on = Get-SqlData 'VISTA-LAPPY\SQLEXPRESS' OrderNumbers 'SELECT top
10 * FROM dbo.UPSData'

$orders = $on | select OrderNumber
$orders
foreach ($order in $orders){
$wd += Get-SqlData 'VISTA-LAPPY\SQLEXPRESS' Worldship 'SELECT
siShipmentID, pkgPackageReference1, pkgPackageReference2 FROM
dbo.ShipmentData Where pkgPackageReference1 = '$order.OrderNumber' or
pkgPackageReference2 = '$order.OrderNumber' '
}
$wd

The variables won't expand in single quotes like that.

When nesting single and double quotes if the outer quote is double,
everything gets expaned.

Given $var = "something"
"$var" # this expands to "something"
'$var' # this is literal and won't expand
"this is a nested quote: '$var'" # this expands to "this is a nested quote:
'something'"


Try this for your select statment:

"SELECT siShipmentID, pkgPackageReference1, pkgPackageReference2 FROM
dbo.ShipmentData Where pkgPackageReference1 = '$order.OrderNumber' or
pkgPackageReference2 = '$order.OrderNumber'"

"Jsimpson" wrote:

> I am using "SQL Server PowerShell Extensions" from codeplex and I
> think I am having a punctuation issue with powershell vs. the SQL
> query. I am pretty sure that it is single quotes vs. double quotes
> and how posh vs t-sql are interpreting them. I get 10 numbers from
> my first statement, but in the second one is not interpreting my
> variables correctly. Can someone give me a push in the right
> direction?
>
> cd c:\scripts\sqltools
> .. .\LibrarySmo.ps1
> $on = Get-SqlData 'VISTA-LAPPY\SQLEXPRESS' OrderNumbers 'SELECT top
> 10 * FROM dbo.UPSData'
>
> $orders = $on | select OrderNumber
> $orders
> foreach ($order in $orders){
> $wd += Get-SqlData 'VISTA-LAPPY\SQLEXPRESS' Worldship 'SELECT
> siShipmentID, pkgPackageReference1, pkgPackageReference2 FROM
> dbo.ShipmentData Where pkgPackageReference1 = '$order.OrderNumber' or
> pkgPackageReference2 = '$order.OrderNumber' '
> }
> $wd
>

You might find this function useful. I've used it to embed PowerShell
variables in SQL strings.

function ToSql($Value) {
if ( $Value -eq $null -or $Value -is [DBNull] ) { 'NULL' }
elseif ( $Value -is [String] ) { "'{0}'" -f $Value.Replace("'","''") }
elseif ( $Value -is [Guid] ) { "'{0}'" -f $Value }
elseif ( $Value -is [DateTime] ) { "'{0:yyyy-MM-dd HH:mm:ss.FFF}'" -f
$Value }
elseif ( $Value -is [Boolean] ) { if ($Value) { '1' } else { '0' } }
elseif ( $Value -is [Byte[]] ) { "0x{0}" -f [String]::Join('', ( $Value |
%{ $_.ToString('X2') } ) ) }
elseif ( $Value -is [System.Xml.XmlNode] ) { ToSql
"$($Value.get_OuterXml())" }
elseif ( $Value -is [System.Xml.Linq.XNode] ) { ToSql
"$($Value.ToString())" }
elseif ( $Value -is [System.Collections.IEnumerable] ) {
[String]::Join(',', ( $Value | %{ ToSql $_ } ) ) }
else { $Value }
}

usage:

# SELECT * FROM MyTable WHERE MyColumn = 'String'
$Value = 'String'
$SQL = "SELECT * FROM MyTable WHERE MyColumn = $(ToSql $Value)"

# SELECT * FROM MyTable WHERE MyColumn IN ('String1', 'String2', 'String3')
$Value = ('String1', 'String2', 'String3')
$SQL = "SELECT * FROM MyTable WHERE MyColumn = $(ToSql $Value)"

etc... as you can see it supports Guid, String, DateTime, Boolean, Byte[],
System.Xml, XLinq, and arrays of primitive types. Anything else is returned
verbatim.

Josh

"Jsimpson" <jsimpson2000@xxxxxx> wrote in message
news:cd5e3636-953a-46e4-a7da-31b62039421c@xxxxxx

> I am using "SQL Server PowerShell Extensions" from codeplex and I
> think I am having a punctuation issue with powershell vs. the SQL
> query. I am pretty sure that it is single quotes vs. double quotes
> and how posh vs t-sql are interpreting them. I get 10 numbers from
> my first statement, but in the second one is not interpreting my
> variables correctly. Can someone give me a push in the right
> direction?
>
> cd c:\scripts\sqltools
> . .\LibrarySmo.ps1
> $on = Get-SqlData 'VISTA-LAPPY\SQLEXPRESS' OrderNumbers 'SELECT top
> 10 * FROM dbo.UPSData'
>
> $orders = $on | select OrderNumber
> $orders
> foreach ($order in $orders){
> $wd += Get-SqlData 'VISTA-LAPPY\SQLEXPRESS' Worldship 'SELECT
> siShipmentID, pkgPackageReference1, pkgPackageReference2 FROM
> dbo.ShipmentData Where pkgPackageReference1 = '$order.OrderNumber' or
> pkgPackageReference2 = '$order.OrderNumber' '
> }
> $wd