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()

Bacon Bits · Accepted Answer · 2015-06-08 18:46:16Z

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.

edited Jun 8, 2015 at 18:46

answered Jun 8, 2015 at 13:51

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

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

Comments

user3046742 · Accepted Answer · 2015-06-08 14:03:57Z

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

edited Jun 8, 2015 at 14:03

answered Jun 8, 2015 at 13:47

user3046742

2351 silver badge11 bronze badges

Collectives™ on Stack Overflow

Run SQL Server Update Statement With Powershell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related