- Capture your scheduled task PowerShell output in a log file so you can see what is happening.
- Our nightly sync is dependent on the completion of several other processes. I want our sync to run as soon as possible, but not before the other processes are complete. Fortunately, there are tell-tale flags in specific tables that I look for from PowerShell with Invoke-Sqlcmd. The query calls a custom stored procedure that returns a row if the flags are present (meaning the accounting automation has run).
- Sometimes our users work late and need to delay or bypass the sync. Our sync tries to run every few minutes during a 2-hour window. If a file called “skip.txt” is detected in a specific folder, the sync will not run.
- Log the successful execution of the nightly sync script in a database table. Because I run the sync task multiple times, I want to make sure I don’t run this sync more than once, so I check the log table using a stored procedure. If the proc returns a row, the sync has already run.
- If you want to ensure that you have the ability to invoke SQL from your script, you can test to make sure the correct module is loaded:Write-Host "Install SQL server if not already installed"
- LetSync and Quickbooks are both dependent on the %localappdata% folder. Both apps store critical information in the user profile. When running the task with the option to “Run whether the user is logged in or not”, windows does not load the interactive profile, so the %localappdata% folder is not available. Windows creates a temporary profile for the user that is deleted when the session ends. I had to change the task to run only when the user is logged on and log the user on with a remote session that I disconnect. Without some additional edits to the group policy, however, windows will eventually log this user out. To keep the user logged in, run mmc.exe and load the Group Policy Object Editor. Drill down to Computer Configuration -> Administrative Templates -> Windows Components -> Remote Desktop Services -> Remote Desktop Session Host -> Session Time Limits. Disable the following:
Case sample:
Capture your scheduled task PowerShell output in a log file so you can see what is happening. #
- In the scheduled task action, use powershell.exe in the program/script field.
- In Add Arguments, call a second instance of PowerShell and redirect the output. Include the quotes. “%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -WindowStyle Hidden -File C:\SomePath\SomeScript.ps1 -verbose >> C:\SomeOtherPath\somescript.log 2>&1”
- Use Write-Host in your script to send output to the log
Note: If you aren’t getting a log file, you may have a permission problem or an invalid path.
Our nightly sync is dependent on the completion of several other processes. I want our sync to run as soon as possible, but not before the other processes are complete. Fortunately, there are tell-tale flags in specific tables that I look for from PowerShell with Invoke-Sqlcmd. The query calls a custom stored procedure that returns a row if the flags are present (meaning the accounting automation has run). #
Write-Host “Has the accounting automation run yet? (Necessary step. If not, exit)”
#******************************************
try {
#Append @ForceHasRun = 1 to pretend it has run, @ForceHasNotRun = 1 to pretend it has not run for testing
Invoke-Sqlcmd -TrustServerCertificate -StatisticsVariable stats -ServerInstance “SomeSQLServer” -Database “SomeDatabase” -Query “EXEC sp_AccountingEODHasRunToday”
} catch {
Write-Host ($_ | Format-Table | Out-String)
Write-Host “$(Get-Date) Error calling SQL EXEC sp_AccountingEODHasRunToday. Exiting…”
Write-Host “******************************************”
Exit
}
if ($stats.SelectRows -eq 0) {
Write-Host “$(Get-Date) Accounting Automation has NOT run. Exiting…”
Write-Host “******************************************”
Exit
} else {
Write-Host “$(Get-Date) Accounting Automation Complete. Continuing…”
}
We use that table to determine whether or not the script has run and prevent it from running multiple times.
Sometimes our users work late and need to delay or bypass the sync. Our sync tries to run every few minutes during a 2-hour window. If a file called “skip.txt” is detected in a specific folder, the sync will not run. #
Write-Host “Is the skip file present?”
#*************************
#check a folder \\somefileserver\QuickBooks Database\_skip LetSync for a file named skip.txt
#if present, exit
$skip = Test-Path -path \\somefileserver\Quickbooks Database\_skip LetSync\skip.txt -PathType Leaf
If ($skip) {
Write-Host “$(Get-Date) Skip file found. Exiting…”
Write-Host “******************************************”
Exit
} else {
Write-Host “$(Get-Date) No skip. Continuing…”
}
Log the successful execution of the nightly sync script in a database table. Because I run the sync task multiple times, I want to make sure I don’t run this sync more than once, so I check the log table using a stored procedure. If the proc returns a row, the sync has already run. #
- Write-Host “Has this LetSync script already run? (If so, exit)”
#************************************
try {
#Append @ForceHasRun = 1 to pretend it has run, @ForceHasNotRun = 1 to pretend it has not run for testing
Invoke-Sqlcmd -TrustServerCertificate -StatisticsVariable stats -ServerInstance “SomeSQLServer” -Database “SomeDatabase” -Query “EXEC sp_AccountingEODLetSyncHasRunToday”
} catch {
Write-Host ($_ | Format-Table | Out-String)
Write-Host “$(Get-Date) Error calling SQL EXEC sp_AccountingEODLetSyncHasRunToday”
Write-Host “******************************************”
Exit
}
if ($stats.SelectRows -gt 0) {
Write-Host “$(Get-Date) LetSync has run today. Exiting…”
#Write-Host ($_ | Format-Table | Out-String)
#Write-Host ($stats | Format-Table | Out-String)
Write-Host “******************************************”
Exit
} else {
Write-Host “$(Get-Date) LetSync has NOT run. Continuing…”
}
If you want to ensure that you have the ability to invoke SQL from your script, you can test to make sure the correct module is loaded:
Write-Host “Install SQL server if not already installed” #
#*************************
if (-not(Get-Module -ListAvailable -Name SqlServer) ) {
Install-Module -Force sqlserver -Scope currentuser -AllowClobber
Write-Host “SqlServer module install complete”
} else {
Write-Host “SqlServer module found”
}
LetSync and Quickbooks are both dependent on the %localappdata% folder. Both apps store critical information in the user profile. When running the task with the option to “Run whether the user is logged in or not”, windows does not load the interactive profile, so the %localappdata% folder is not available. Windows creates a temporary profile for the user that is deleted when the session ends. I had to change the task to run only when the user is logged on and log the user on with a remote session that I disconnect. Without some additional edits to the group policy, however, windows will eventually log this user out. To keep the user logged in, run mmc.exe and load the Group Policy Object Editor. Drill down to Computer Configuration -> Administrative Templates -> Windows Components -> Remote Desktop Services -> Remote Desktop Session Host -> Session Time Limits. Disable the following: #
This has been working for us. The problem, of course, is if the server is restarted, the user will need to be logged in again. I’m exploring auto-login settings.
Depending on this user being logged in makes the whole process rather fragile. Copying the LetSync profile info over to the temp profile from Powershell, which worked for LetSync.
