CODEWITHSUNDEEP
powershellscheduled-taskssqlcmd
Gary Malley
Gary Malley

Reputation: 33

Scheduled Task null value return from sqlcmd in PowerShell

I have a PowerShell script which is running a SQL query from a Windows Server 2008R2 Server; when logged on to the Server with a domain Administrator/ Service Account the script runs and completes from both basic PS and ISE. If I schedule the same script using the same Administrator/ Service Account from Task Scheduler the query does not return any value. The scheduled task is running as ‘Run with Highest Privileges.’ PowerShell is V4.

$Result = Invoke-Sqlcmd -ServerInstance Server -Database Database -Query $Query

Where run manually $Result always returns a value and yet from the Scheduled Task $Result is always null. In both instances the remainder of the script runs successfully and completes.

Upvotes: 1

Views: 1072

Answers (2)

Gary Malley
Gary Malley

Reputation: 33

Here’s the ‘resolution’ although it really feels like a cheat. I attempted different trigger methods to start the script; first a .bat file which also maddeningly ran when manually started but also failed when scheduled. Next I wrote a simple .vbs script to start the PowerShell script which worked when run manually and this, at least, did run correctly from scheduler … I can’t explain it …

Upvotes: 0

G42
G42

Reputation: 10019

Literally faced this today. The result from this query is of the type System.Data.Datarow. I'm guessing you are just entering $Result in the console/ISE? PowerShell is being nice and doing an on-the-fly conversion.

Try Write-Host "$Result" and you should see System.Data.Datarow instead of your results.

The question then becomes where to convert this System.Data.Datarow to. This really depends on the rest of your code (and isn't my strong point). Lazy examples below.

$Results | Out-file ".\Results.txt"
$csv = ConvertTo-Csv $Results

Upvotes: 0

Related Questions