Post reply

File not found error when calling CreateTextFile

  • September 22, 2016 at 3:30 pm

    #317728

    Hi,

    Wondering if anyone has any ideas what might be happening here. I've set up a SQL Server Agent Job that has a Powershell step in it. The job keeps failing when it tries to execute the Powershell script.

    Here's the Powershell script:

    $dbName = "MyTest"

    # Get current date

    [datetime] $date = Get-Date

    # Get CSV file properties

    $filePath = "C:\Test Folder\"

    $fileName = "TestFile"

    #_YYYY_MM_DD" -replace "<<YYYY_MM_DD>>", $date.ToString("yyyy_MM_dd")

    $fileNameExtension = "csv"

    $headerRow = "COLUMN1, COLUMN2, COLUMN3"

    $path = $filePath + $fileName + "." + $fileNameExtension

    # Create CSV file and append header rows

    $fso = new-object -comobject scripting.filesystemobject

    $file = $fso.CreateTextFile($path,$true)

    $file.WriteLine($headerRow)

    # Call stored procedure and append rows to CSV file

    $sql = "DECLARE

    @FirstDayOfPreviousMonth AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) AS DATE)

    EXEC MyTestSproc @FirstDayOfPreviousMonth "

    Invoke-SqlCmd -database $dbName -Query $sql | ForEach-Object { $file.WriteLine( $_[0].ToString() + "," + $_[1].ToString() + "," + $_[2].ToString() ) }

    $file.close()

    Here's the error I'm receiving:

    A job step received an error at line 17 in a PowerShell script. The corresponding line is '$file = $fso.CreateTextFile($path,$true)'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "CreateTextFile" with "2" argument(s): "Exception from HRESULT: 0x800A004C (CTL_E_PATHNOTFOUND" '.

    I've verified that C:\Test Folder\ exists both in my local C: as well as the C: on the server hosting the databases, yet neither of them is getting recognized by the job. I'm quite new to PowerShell and I'm probably missing something pretty obvious. Any ideas?

  • September 22, 2016 at 3:40 pm

    #1902670

    Here's the error I'm receiving:

    A job step received an error at line 17 in a PowerShell script. The corresponding line is '$file = $fso.CreateTextFile($path,$true)'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "CreateTextFile" with "2" argument(s): "Exception from HRESULT: 0x800A004C (CTL_E_PATHNOTFOUND" '.

    I've verified that C:\Test Folder\ exists both in my local C: as well as the C: on the server hosting the databases, yet neither of them is getting recognized by the job. I'm quite new to PowerShell and I'm probably missing something pretty obvious. Any ideas?

    Looks like possibly the SQL Server Agent service account does not have the necessary permissions to that folder. I'd take a look at that.

    Sue

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply