How to safely update database using powershell syntax?

Question

I have a PowerShell script which runs safely up to the point of an update. I connect to the database and can read from it, however, I have trouble updating.

Here is a copy of the error message I receive: "Exception calling "ExecuteNonQuery" with "0" argument(s): "There is already an open DataReader associated with this Command which must be closed first." At C:\xxx\xxx\ToolsController.ps1:37 char:5 + $InnerCommand.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : InvalidOperationException"

FYI: I posted this before and this is an update to the previous question. Any help at all would be greatly appreciated.

I have tried attaching the variable at the end of the string i.e '..+$varibale' also tried @" "@ and I have gotten the same result.

$Global:Server = "server"      
$Global:Database = "db"
$Global:u = "un"
$Global:p = "pw"
[string]$Query = "SELECT * FROM [dbo].[jobs] WHERE [Run] = 0"
try{
$ConnectionString = "server=$Server;Integrated Security=true;database=$Database;user id=$u;password=$p"
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()
}
catch{
    "Failed to connect SQL Server"     
}

$Command = $Connection.CreateCommand()
$InnerCommand = $Connection.CreateCommand()
$Command.CommandText = $Query
$jobs = $Command.ExecuteReader()

    while ($jobs.Read()){

        switch($jobs["Script"]){                
            15
            {
                $traverse = $jobs["Frequency"] - 1
                While ($traverse -ge 0) {
                    c:\xx\xx\batch $jobs["Date"]
                    $traverse--
                }

                "Updating Database...."
                $Query2 = "UPDATE [dbo].jobs SET [Run] = 1 WHERE [ID] = $jobs['ID']" <------Error here.
                $InnerCommand.CommandText = $Query2
                $InnerCommand.ExecuteNonQuery()
                "Updating Completed!"
            }
        }
    }
$Connection.Close()
$Connection.Dispose()
Write-Host "Connection Closed"

Maybe just try simplifying by using Invoke-Sqlcmd and its parameters. — m0lochwalker
– m0lochwalker, Commented Jul 2, 2019 at 15:16
The message is clear: while you're reading from the DataReader, you cannot perform commands on the same connection. Read all the jobs into a collection first, then do a second loop where you perform the actual actions and update the jobs. Alternatively, you can open a second connection to do the UPDATE on, but this makes your processing far less efficient as you're holding the first connection open while you're doing something that could take a very long time (potentially longer than timeouts). — Jeroen Mostert
– Jeroen Mostert, Commented Jul 2, 2019 at 15:17
If I can toot my own horn for a moment, this answer has some code for conveniently reading objects from a DataReader (even more so than Invoke-Sqlcmd, which borrows all the weirdness of sqlcmd). — Jeroen Mostert
– Jeroen Mostert, Commented Jul 2, 2019 at 15:17

Dan Guzman · Accepted Answer · 2019-07-02 16:00:13Z

2

Add MultipleActiveResultSets=True to the connection string. This will allow interleaving of UPDATE statements while reading from the data reader.

edited Jul 2, 2019 at 16:00

answered Jul 2, 2019 at 15:40

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

How to safely update database using powershell syntax?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related