A positional parameter cannot be found that accepts argument

0
votes

I wrote a dynamic SQL to build the below query and execute it using xp_cmdshell in SQL Server

POWERSHELL -command "$d = Get-Date Get-childItem "c:\ServerManagement\Logs\SQL Server Agent" -recurse -include *.log | Where {($_.lastwritetime -le $d.Addhours(-120))} | Remove-Item -Force"

but when I run it on the command prompt, I get this error:

Get-ChildItem : A positional parameter cannot be found that accepts argument 'Agent'.
At line:1 char:19
+ ... = Get-Date; Get-childItem c:\ServerManagement\Logs\SQL Server Agent - ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Get-ChildItem], ParameterBindingException
+ FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

My intention is to run the above code as a SQL Server Agent job with the job type as "CMDEXEC"

I am a beginner at PowerShell and couldn't figure out how to troubleshoot the above error.

Executing the PowerShell code from the Powershell ISE window works just fine. -

Any help is appreciated.

3
sql-serverpowershell

3 Answers

1
votes

Through the double quotes in double quotes your command was split in three parts

  1. "$d = Get-Date; Get-childItem "
  2. c:\ServerManagement\Logs\SQL Server Agent
  3. " -recurse -include *.log | Where {($_.lastwritetime -le $d.Addhours(-120))} | Remove-Item -Force"

Powershell tried to translate this using positional parameter which translates to:

Get-ChildItem -Path C:\ServerManagement\Logs\SQL -Filter Server Agent

There are multiple ways to avoid this:

  1. Using different quotes like you did

    "'String'"

  2. Escaping the inner double quotes

    "\"String\""

  3. Using a Scriptblock instead of quotes to encapsulate the Powershell part

    powershell.exe -command {"String"}

1
votes

Your whole argument is wrapped in double quotes. Then you cannot wrap parts of it in double quotes, you need single quotes. Or you can escape the double quote using the back-tick `:

POWERSHELL -command "$d = Get-Date; Get-childItem `"c:\ServerManagement\Logs\SQL Server Agent`" -recurse -include *.log | Where {($_.lastwritetime -le $d.Addhours(-120))} | Remove-Item -Force"
0
votes

I have figured it out through trial and error. I replaced the double quotes with single quotes and the code started to work, I don't know why though.

POWERSHELL -command "$d = Get-Date; Get-childItem 'c:\ServerManagement\Logs\SQL Server Agent' -recurse -include *.log | Where {($_.lastwritetime -le $d.Addhours(-120))} | Remove-Item -Force"