Run Batch Of Update Statements

I need to run 4 update queries via powershell, so I did some googling to try to find the answer.  This is the syntax that I was able to come up with, but it is giving me an error, so I assume I didn't set something up properly.  Can someone help me get this code functioning properly?

 

[code]

$sql=@'

UPDATE firefly.light.dbo.shipped SET shipped = 'Yes' WHERE shippingstatus IN ('Loading', 'Shipped') 

UPDATE firefly.light.dbo.inventorystatus SET orderitem = 'Yes' WHERE Count(item) < '3'

UPDATE firefly.light.dbo.stockinformation SET verify = 'Yes' WHERE Count(item) = '0' 

UPDATE firefly.light.dbo.orderinformation SET verify = 'Yes' WHERE status NOT IN ('Shipped', Back Order', 'Pending')

'@

 

#Setting Server Information

$server='Powerball'

$dbname='hero6'

$connStr='Server={0};Database={1};Integrated Security=SSPI;' -f $server,$dbname

 

$conn=New-Object System.Data.SqlClient.SQLConnection($connStr)

$conn.Open()

$cmd=$conn.CreateCommand()

$cmd.CommandText=$sql

$i=$cmd.ExecuteNonQuery()

Write-Host "Records Affected: $i" -fore green

[/code]

  • Sorry guys I didn't see an edit button, but this is the error that is being thrown

    [code]

    Error of: Exception calling "ExecuteNonQuery" with "0" argument(s): "'shippingstatus' is not a recognized built-in function name." AT C:\powershell.ps1:21 char:24 + $i+$cmd.ExecuteNonQuery <<<< () + CategoryInfo : NotSpecified (:) [], MethodInvocationException + FullyQualifiedErrorID : DotNetMethodException

    [/code]

  • Hello,

    I was able to get the code to work by making some simple test tables. Your PowerShell code is valid, but your SQL code does not appear to be. Make sure your SQL works in SQL Server Management Studio, then try it in PowerShell.

    There are syntax errors with the COUNT() being in the WHERE for the UPDATE, and also, you are missing a quote on the last line, before "Back Order".