Run SQL Server Update Statement With Powershell

Question

Should be straight forward, but I keep getting this error

shipping_status is not a recognized function name

And this is my syntax - what is causing this, as my statement will run issue free in SSMS

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection=New-Object System.Data.SqlClient.SqlConnection
$SqlCmd.Connection.ConnectionString='Server=Server1;Database=DB1;Integrated Security=True'
$SqlCmd.Connection.Open()
$SqlCmd.CommandText="UPDATE firefly.light.dbo.shipped SET shipped = 'Yes' WHERE shipping_status IN ('Loading', 'Shipped') "
$SqlCmd.ExecuteNonQuery()

Answer 1

Your code looks fine to me but maybe try escaping the single quotes with a grave-accent(`):

$SqlCmd.CommandText="UPDATE firefly.light.dbo.shipped SET shipped = `'Yes`' 

etc

Answer 2

Assuming shipping_status actually exists in firefly.light.dbo.shipped, I'd try using an alias:

UPDATE s
SET shipped = 'Yes'
FROM firefly.light.dbo.shipped s
WHERE s.shipping_status IN ('Loading', 'Shipped');

There may also be a parsing issue since you're using double-quotes. Try using Powershell here-strings:

$SqlCmd.CommandText = @'
UPDATE s
SET shipped = 'Yes'
FROM firefly.light.dbo.shipped s
WHERE s.shipping_status IN ('Loading', 'Shipped');
'@;

That has the advantage of not needing to escape your query with backticks.

If none of that works, you can always expand the IN expression into a list of OR expressions.