CODEWITHSUNDEEP
powershellscheduled-tasksoledbconnectionoledbcommandexecutenonquery
Verax
Verax

Reputation: 201

ExecuteNonQuery() method fails when running script from task scheduler. No errors when ran manually

So I implemented a function that logs to a database

function write-logDB ($user,$message,$reason,$script,$dbConnection) {
    $sql= "INSERT INTO ScriptLogging(Date_Time,Person,Message,Reason,Script) 
    VALUES (GETDATE(),'$user','$message','$reason','$script')"
    $command = new-object -TypeName System.Data.OleDb.OleDbCommand
    $command.Connection = $dbConnection
    $command.CommandText = $sql
    $command.ExecuteNonQuery()
}

This function uses a ALREADY open DB connection to write the log. When running this script with ISE open this script iterates through each user logging the necessary values in the DB.. no problem.

HOWEVER if this script is being ran through task scheduler (through a timed event or manually right clicking and clicking run) the script will fail out and I will get the following exception error:

"ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. at Line Number 655 in CreateStudents - MasterLog.ps1 Function Main ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. at Line Number 655 in CreateStudents - MasterLog.ps1 Function Main"

This is erroring out on is part of my function for logging. I am not sure why the connection is closed when running through task scheduler.. It does not even iterate through 2 users before errors.

I have confirmed and reconfirmed I am using the same exact account through task manager as I am when running manually. I also confirmed a million times that it is pointing at the right script..

I know you may not have a answer for this but even help troubleshooting or how I should troubleshoot would be appreciated. The only data I am getting back when the script fails to task scheduler is the error code I posted above.

Thanks in advance.

Upvotes: 0

Views: 231

Answers (1)

Srinivasan Jayakumar
Srinivasan Jayakumar

Reputation: 72

I hope you can try opening the connection before calling the ExecuteNonQuery. You can try below..

function write-logDB ($user,$message,$reason,$script,$dbConnection) {
    $sql= "INSERT INTO ScriptLogging(Date_Time,Person,Message,Reason,Script) 
    VALUES (GETDATE(),'$user','$message','$reason','$script')"
    $command = new-object -TypeName System.Data.OleDb.OleDbCommand
    $command.Connection = $dbConnection
    $command.CommandText = $sql
    if ($dbConnection.State -eq [System.Data.ConnectionState]'Closed') 
    {
        $dbConnection.Open()
        Write-Output "Connection Opened Successfully.."
    }
    else
    {
        Write-Output "Connection already Opened.."
    }
    $command.ExecuteNonQuery()
}

Upvotes: 0

Related Questions