Scheduled Task null value return from sqlcmd in PowerShell

1
votes

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.

2
powershellscheduled-taskssqlcmd
trap your error output into a file and update your question with the error. My initial thoughts is that this is user session related, you might need to use a credentialed session. - Colyn1337
From the sounds of it it does not appear to be failing so I don't think there is an error to capture. Unless the $ErrorActionPreference has been set in this script which would be useful to know. Perhaps just not returning anything? Maybe in how $query is being generated.... I don't know for sure. - Matt
Thanks for the thoughts, as a rule whilst initially developing a script I add an Add-Content $Location $Variable line under every possible action to ensure I can follow the logic through in a text file it’s very useful and in this case for me running manually I ‘see’ an output against the variables in question however the schedule task returns a blank. - Gary Malley
I have run with Start-Transcript and the error recorded is: Transcript started, output file is d:\Logs\Debug.out Invoke-Sqlcmd : The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. sqlcmd runs from cmd and invoke-sqlcmd runs perfectly well from PS and ISE ... just not when scheduled. - Gary Malley
Is the SQL snapin/module loaded when running as scheduled task? Is Add-Snapin part of your script, or is it loaded automagically when using the console/ISE (e.g. via Profile)? Is this 32/64bit PowerShell? Invoke-SqlCmd how-to - G42

2 Answers

0
votes

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
0
votes

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 …