Search code examples
powershellsql-server-2012sql-agent-job

Running a PowerShell script in SQL Server Agent is not working

I have a SQL Server Agent job that runs a PowerShell script to pull data from a database and save it in a csv file.

The job type is set to PowerShell and the command looks like this:

$sql=@'exec my_stored_procedure'@
$result = Invoke-Sqlcmd -ServerInstance INSTANCE_NAME -Database DBNAME -Query $sql
#The first row gets removed and then save it to a csv file
$result | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Set-Content \\SERVER\c$\Folder\file.csv

When I run this job, it runs successfully, but the file.csv is not created in the specified location.

I checked the Job History and it says:

The job script encountered the following errors. These error did not stop the script: (.....) The corresponding line is $result | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Set-Content \\SERVER\c$\Folder\file.csv. Correct the script and reschedule the job. The error information returned by PowerShell is 'Cannot use interface. The IContentCmdletProvider interface is not implemented by this provider.'. Process Exit Code 0.

Could someone help me understand what I'm missing? This is my first PowerShell project, so I'm probably missing things that are obvious to other people..?

Solution

  • It seems that it was complaining because of the server name in the path.

    I originally had:

    ConvertTo-Csv -NoTypeInformation | 
    Select-Object -Skip 1 | 
    Set-Content  \\SERVER\c$\Folder\file.csv

    but CDing to the directory and specifying the file did the trick:

    $directoryPath = "C:\Folder"
cd $directoryPath
ConvertTo-Csv -NoTypeInformation | 
    Select-Object -Skip 1 | 
    Set-Content  C:\Folder\file.csv