July 30, 2021 at 10:04 am
I've built a Powershell script, which I want to run through SQL Server Agent. The script works fine in multiple environments:
As soon as, however, I ask the script to be run through agent it fails and I have no ideas why.
The Powershell script I'm using is at the end of this post, and the error I'm getting is:
A job step received an error at line 48 in a PowerShell script. The corresponding line is '$Reference.APM_Ref.Substring(0,1)'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'You cannot call a method on a null-valued expression. '
Now yes, the value of $Reference.APM_Ref is null, but it's null because there were no rows returned from Invoke-Sqlcmd, therefore the foreach loop should never have been entered and thus the error can't happen. As you can see I've even tried adding a count to check that rows were returned and not enter the foreach if the command returns no rows, and I still get the error.
What's more frustrating is that if rows are returned the script, in agent, works fine. It's literally just when no rows are returned; but no rows being returned is expected and when there aren't the foreach shouldn't be entered, and thus no error (because the line isn't run).
What's difference here? Why is Powershell in Agent producing an error that doesn't exist in Powershell 4, 5, or 7? If I could reproduce this outside of Agent, it would be fine, but it is literally just in Agent that this occurs. Is it a known issue that the Powershell tasks behave differently? If so, how to I stop it behaving differently? I know it seems "dumb", but the behaviour should be the same; why is it not?
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[String]$ServerInstance,
[Parameter(Mandatory=$true)]
[String]$OpenAttachShare
)
function Copy-Items {
param(
[Parameter(Mandatory=$true)]
[String]$Path,
[Parameter(Mandatory=$true)]
[String]$Destination,
[Parameter(Mandatory=$false)]
[Boolean]$Verbose
)
$Items = Get-ChildItem $Path -Recurse
foreach ($Item in $Items) {
$NewItemPath = $Destination + $Item.FullName.Remove(0,$Path.Length)
#Write-Host "Found File $($Item.FullName)."
if(Test-Path -Path $NewItemPath){
#Write-Host "File or Directory already exists in destination. Not copied."
} elseif (Test-Path -Path $Item.FullName -PathType Container){
#Write-Host "Creating new directory $($NewItemPath)."
New-Item -Path $NewItemPath -ItemType "directory" | Out-Null
} else {
#Write-Host "Copying File to $($NewItemPath)."
Copy-Item -Path $Item.FullName -Destination $NewItemPath
}
}
}
$SQL = "SELECT B@ AS Branch,Polref@ AS APM_Ref,Brooms_Ref FROM dbo.MoveApmOpenAttachData_tbl WHERE Moved = 0 ORDER BY Trans_date ASC, Trans_time ASC;" #Testing Query
#$SQL = "SELECT TOP(1) B@ AS Branch,Polref@ AS APM_Ref,Brooms_Ref FROM dbo.MoveApmOpenAttachData_tbl WHERE Moved = 0 ORDER BY Trans_date ASC, Trans_time ASC;" #Testing Query
$References = Invoke-Sqlcmd -Query $SQL -ServerInstance $ServerInstance -Database "General"
if ($References.Count -eq 0){
#Write-Host "No References to update"
} else {
foreach ($Reference In $References){
if ($Reference.Branch -eq 0){
$APM_OA = "$($OpenAttachShare)\Branch$($Reference.Branch)\$($Reference.APM_Ref.Substring(0,1))\$($Reference.APM_Ref.Substring(0,2))\$($Reference.APM_Ref.Substring(0,3))\$($Reference.APM_Ref.Substring(0,4))\$($Reference.APM_Ref.Substring(0,7))\$($Reference.APM_Ref)\"
$Brooms_OA = "$($OpenAttachShare)\Branch$($Reference.Branch)\$($Reference.Brooms_Ref.Substring(0,1))\$($Reference.Brooms_Ref.Substring(0,2))\$($Reference.Brooms_Ref.Substring(0,3))\$($Reference.Brooms_Ref.Substring(0,4))\$($Reference.Brooms_Ref.Substring(0,6))\$($Reference.Brooms_Ref)\"
} else {
$APM_OA = "$($OpenAttachShare)\Branch$($Reference.Branch)\0$($Reference.Branch)\$($Reference.APM_Ref.Substring(0,1))\$($Reference.APM_Ref.Substring(0,2))\$($Reference.APM_Ref.Substring(0,3))\$($Reference.APM_Ref.Substring(0,4))\$($Reference.APM_Ref.Substring(0,7))\$($Reference.APM_Ref)\"
$Brooms_OA = "$($OpenAttachShare)\Branch$($Reference.Branch)\0$($Reference.Branch)\$($Reference.Brooms_Ref.Substring(0,1))\$($Reference.Brooms_Ref.Substring(0,2))\$($Reference.Brooms_Ref.Substring(0,3))\$($Reference.Brooms_Ref.Substring(0,4))\$($Reference.Brooms_Ref.Substring(0,6))\$($Reference.Brooms_Ref)\"
}
##Write-Host $APM_OA
if(-not (Test-Path $APM_OA)){
#Write-Host "OpenAttach Path for APM reference $($Reference.APM_Ref) does not exist. No files to copy."
$Params = "OldPath='No files to copy'","NewPath='No files to copy'", "Branch=$($Reference.Branch)", "APMRef='$($Reference.APM_Ref)'", "BroomsRef='$($Reference.Brooms_ref)'"
$SQL = "UPDATE dbo.MoveApmOpenAttachData_tbl SET Moved = 1, MovedDate = GETDATE(), OldPath =$(OldPath), NewPath =$(NewPath) WHERE B@ =$(Branch) AND Polref@ =$(APMRef) AND Brooms_ref = `$(BroomsRef);"
Invoke-Sqlcmd -Query $SQL -ServerInstance $ServerInstance -Database "General" -Variable $Params
} else {
#Write-Host "Copying files from APM reference $($Reference.APM_Ref) to Brooms reference $($Reference.Brooms_ref) on Branch $($Reference.Branch)."
##Write-Host $Brooms_OA
if(-not (Test-Path $Brooms_OA)){
#Write-Host "OpenAttach Path for Brooms reference $($Reference.Brooms_Ref) does not exist. Creating directory."
New-Item -Path $Brooms_OA -ItemType "Directory" | Out-Null
}
Copy-Items "$($APM_OA)" -Destination $Brooms_OA
$Params = "OldPath='$($APM_OA)'","NewPath='$($Brooms_OA)'", "Branch=$($Reference.Branch)", "APMRef='$($Reference.APM_Ref)'", "BroomsRef='$($Reference.Brooms_ref)'"
$SQL = "UPDATE dbo.MoveApmOpenAttachData_tbl SET Moved = 1, MovedDate = GETDATE(), OldPath =$(OldPath), NewPath =$(NewPath) WHERE B@ =$(Branch) AND Polref@ =$(APMRef) AND Brooms_ref = `$(BroomsRef);"
Invoke-Sqlcmd -Query $SQL -ServerInstance $ServerInstance -Database "General" -Variable $Params
}
}
}
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 30, 2021 at 5:07 pm
I think I had a similar issue years ago - it has been a while so I may be completely off-base. Some constructs are not valid in SQLPS because the agent uses tokens and it gets confused by those constructs.
Are you embedding this script in the agent job - or calling out to a script file?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 2, 2021 at 8:17 am
Hi Jeffrey,
I'm calling a script. All that's actually in the PowerShell command window for agent is (obviously with valid parameters):
& 'C:\Powershell\Copy APM OpenAttach.ps1' -ServerInstance "SQL Server Name" -OpenAttachShare "Share Attach UNC Path"
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 3, 2021 at 9:00 am
Think I've worked out the problem; I didn't realise that SQLPS doesn't use the host's version of Powershell, but it's own. As we're still running 2012 on this instance, then it's running Powershell 2.0, and yeah, the syntax just isn't recognised.
Going to try and call the host's PowerShell from SQLPS, and have the script run natively there. Not ideal, but it should work.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 3, 2021 at 3:14 pm
Yeah - that sounds similar to an issue I had a while ago and it was related to SQLPS. It might actually be better - since you can pass on the PS command line the execution policy or bypass that portion.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply