0

I have the following script in powershell

Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location
$SQLServer = "localhost"
$today = (get-date).ToString("dd-MM-yyyy")
$DBName = "db"
$ExportFile = "L:\FC Folder\Despatch\Brexit Files\DHL\DHL "+$today+".csv"
$Counter = 0
$Storedprocedure = "EXEC [dbo].[DHLDeliveries]"
while ( $true )
{
    # Remove the export file
    if (Test-Path -Path $ExportFile -PathType Leaf) {
        Remove-Item $ExportFile -Force
    }
    # Clear the buffer cache to make sure each test is done the same
    $ClearCacheSQL = "DBCC DROPCLEANBUFFERS"
    Invoke-Sqlcmd -ServerInstance $SQLServer -Query $ClearCacheSQL
    # Export the table through the pipeline and capture the run time. Only the export is included in the run time.
    $sw = [Diagnostics.Stopwatch]::StartNew()
    Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DBName -Query $Storedprocedure | Export-CSV -Path $ExportFile -NoTypeInformation
    $sw.Stop()
    $sw.Elapsed
    $Milliseconds = $sw.ElapsedMilliseconds    
    $Counter++
    Exit
}

If Run it in Powershell it works and ends fine.. however if I run it in Task Scheduler the task does not seem to end. It stays in the 'Running' state. Any help appreciated.

Christopher Jack
  • 97
  • 2
  • 12
  • 1
    I guess that there is an error when your run it from the task scheduler. Do you actually see anything happening in your database? What happens when e.g. you export the `$Milliseconds` to a file (before the `exit`? Do you run the task under the same (user) account as where you tested with? – iRon Dec 18 '20 at 11:22
  • 1
    If it concerns the SYSTEM account. I recommend you to have a look at [Scheduled Task Powershell Script - Runs OK as user account, but not as SYSTEM](https://stackoverflow.com/a/51612478/1701026) on how to troubleshoot this. – iRon Dec 18 '20 at 11:37
  • Thank iRon will have a look at that. It creates the file fine.. just doesnt seem to stop the task in the scheduler. – Christopher Jack Dec 18 '20 at 12:40

1 Answers1

0

Added end to powershell script and it seems to work fine.

Christopher Jack
  • 97
  • 2
  • 12