SQL Server Agent Job - $RawUI.CursorPosition Error

Problem

I had this issue a long time ago, but did not have time to write about this.

I am trying to run some PowerShell script to cleanup old backup files in the database server using SQL Server Agent job.

But when I tried running the job, I got this error below.

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script. The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  The step failed.

Solution

Let's walk through my initial troubleshooting steps:

1. I read the first 2 sentences and it seems to be obvious that the error is in line 3.

    Line 3 of my code just gets the current date and puts it in a variable:

    

    $currdate = Get-Date

    It seems straight forward, I ran it in Powershell and it runs fine.

    I isolated the code by adding Write-Output to display the value right after getting the date.

Write-Output "Current Date: $currdate"

    I also deleted the rest of my code after the Write-Output since the error is just in line 3.

    

2. I ran the job again and I still got the same error.

    Now I tried to read and understand the error and read the rest of the error message.

    I tried to understand what each sentence.

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script.

The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. 

Correct the script and reschedule the job. 

The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  

The step failed.    

    Looking at the code, there is no command that seems to be prompting a user and waiting for user interaction.

    The only command that seems to be interacting with the PowerShell Console or ISE is Write-Output which I added in step 1.

3. I tried running the job again with 3 the job again and I still got the same error on line 3.

    I decided to just remove the Get-Date code, just for the heck of it.

4. I ran the SQL Server Agent job with just "clear" on the code. 

clear

    I still got the same error:

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script. The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  The step failed.

    There is no line 3 in the code but it is still telling me that the error is in line 3.

    

    This was just frustrating because the error message is not giving the right information.

    The "clear" command is not prompting a user, but it is somewhat interacting with the console and ISE.

    The command "clear" is and alias for "Clear-Host". Another alias for "Clear-Host is "cls". 

5. I removed the "clear" command and tested again the Get-Date and Write-Output code, and it ran without error.

$currdate = Get-Date

Write-Output "Current Date: $currdate" 

6. I added back the rest of my code to complete the SQL Agent job that will clean old backup files.

Lesson's learned: Don't forget to remove the "Clear-Host", "clear" or "cls" when running your PowerShell commands in SQL Server Agent job.