Sql-server – Archive backups with a PowerShell script launched by SQL Server Agent

powershellsql-server-2012sql-server-agent

I would like to run a Powershell script via the SQL Server Agent.
This script works fine if I run it manually through the command prompt, but the job history in SQL Server Management Studio tells me a syntax error on line 8.

Is there a difference in the SQL Server Agent runtime environment?

Here is my script:

$ArchivesMPath = "E:\ArchivesBDD\Mensuelles"
$SauvegardeMPath = "E:\SauvegardeBDD\Mensuelle"
$ArchivesQPath = "E:\ArchivesBDD\Quotidiennes"
$SauvegardeQPath = "E:\SauvegardeBDD\Quotidienne"
$DAYSOFWEEK = "Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"
$MONTHS = "Decembre", "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre"

$DAILYDIR = "$ArchivesQPath\$($DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__])"
if ([System.IO.Directory]::Exists($DAILYDIR)){
    Remove-Item $DAILYDIR -Force -Recurse
}
New-Item -Path $DAILYDIR -ItemType directory
Move-Item -Path "$SauvegardeQPath\*" -Destination $DAILYDIR

if ((get-date).AddDays(-1).day -eq 1){
    $MONTHLYDIR = "$ArchivesMPath\$($MONTHS[(get-date).month])"
    if ([System.IO.Directory]::Exists($MONTHLYDIR)){
        Remove-Item $MONTHLYDIR -Force -Recurse
    }
    New-Item -Path $MONTHLYDIR -ItemType directory
    Move-Item -Path "$SauvegardeMPath\*" -Destination $MONTHLYDIR
}

Thank you in advance for your help

Best Answer

I believe the problem you're having has to do with SQL Server Agent Tokens as referred to by this Stack Exchange post SQL Agent - PowerShell step “syntax error”

Excerpting from that post

This is a not very intuitive and I was never able to find anything concrete on the explanation [e.g. no exact BOL or white paper was found]. The syntax error in the SQL Agent job is a T-SQL syntax error based on User Tokens. So that basically means that a PowerShell Sub-Expression Operator is treated as a Token to SQL Server Agent. So in PowerShell this $( ) appears to be treated as reserved character sequence for SQL Server Agent.

In your example, the problem line is this

$DAILYDIR = "$ArchivesQPath\$($DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__])"

Notice the $( which is a Powershell sub-expression. This part is attempting to be interpreted by the SQL Server Agent as a token.

My suggestion would be to separate out the sub-expression into another Powershell variable ($DAILYDIRDAY) and then use that to finish building the $DAILYDIR variable.

Give this a try:

$ArchivesMPath = "E:\ArchivesBDD\Mensuelles"
$SauvegardeMPath = "E:\SauvegardeBDD\Mensuelle"
$ArchivesQPath = "E:\ArchivesBDD\Quotidiennes"
$SauvegardeQPath = "E:\SauvegardeBDD\Quotidienne"
$DAYSOFWEEK = "Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"
$MONTHS = "Decembre", "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre"
$DAILYDIRDAY = $DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__]
$DAILYDIR = "$ArchivesQPath\$DAILYDIRDAY"

and follow up with the rest of the Powershell script

Related Solutions

Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet:

Set-Location c:

This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file.

This article contrasts how SQLPS loads in SQL Server 2008 R2 vs. SQL Server 2012/2014. The SQL Provider is loaded using the 'Add-PSSnapin' cmdlet in SQL Server 2008 R2, while starting with SQL Server 2012, the 'Import-Module' cmdlet is used.

The author states: "The outcome of loading the module [with 'Import-Module'] is simply changing to the PSDrive you just loaded." Evidently, the 'Add-PSSnapin' cmdlet left the provider for its SQLPS session unchanged (as FileSystem). That change in cmdlet use, and the resulting session behavior, was what caused my problem.

Sql-server – SQL Agent – PowerShell step “syntax error”

This is a not very intuitive and I was never able to find anything concrete on the explanation [e.g. no exact BOL or white paper was found].

The syntax error in the SQL Agent job is a T-SQL syntax error based on User Tokens. So that basically means that a PowerShell Sub-Expression Operator is treated as a Token to SQL Server Agent. So in PowerShell this $( ) appears to be treated as reserved character sequence for SQL Server Agent. So SQL Agent would be looking for something like this T-SQL example from the BOL article referenced: PRINT N'Current database name is $(A-DBN)' ; .

So in my script when it reached ,@DriveLetter = '$($c.DriveLetter)', the "$c.DriveLetter" is not one of the tokens allowed.

The workaround to this is simply to not use sub-expression statements in PowerShell. I would make the adjustments in my script so this:


$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
    @ServerName = '$($c.ServerName)'
    ,@DriveLetter = '$($c.DriveLetter)'
    ,@DiskSpaceCapacityGB = $($c.DiskSpaceCapacityGB)
    ,@DiskFreeSpaceGB = $($c.DiskFreeSpaceGB)
"@

would have to be modified to something like this:


$severName = $c.ServerName
$driveLetter = $c.DriveLetter
$capacityGB = $c.DiskSpaceCapacityGB
$freeGB = $c.DiskFreeSpaceGB

$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
   @ServerName = '$serverName'
   ,@DriveLetter = '$driveLetter'
   ,@DiskSpaceCapacityGB = $CapacityGB
   ,@DiskFreeSpaceGB = $freeGB
"@

Made the above adjustments to my script and it runs perfectly now.

Best Answer

Related Solutions

Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

Sql-server – SQL Agent – PowerShell step “syntax error”

Related Question