Load .sql from folder

  • I'm using this process to load a folder full of .sql files there are Stored Procedures that I would like to add to a Newly created DB. It runs

    into errors but doesn't log with .sql file it had a problem loading.

    Any help finding which ones loaded and which failed in some sort of LOG file?

    Thanks.

    foreach ($f in Get-ChildItem -path "f:\20210421190105\StoredProcedure\" -Filter *.sql | sort-object)
    {
    $out = "C:\temp\" + $f.name.split(".")[0] + ".txt" ;
    invoke-sqlcmd –ServerInstance (local) -Database test1-InputFile $f.fullname | format-table | out-file -filePath $out
    }

     

  • The issue is that Out-File overwrites the file so you're only seeing the last result.  You can either use -Append or Add-Content instead of Out-File.

  • I added -append but still didn't get any output from the process for failed SP's

    Thx.

  • Ah right it's sending the errors to error output not standard out so it's not getting caught.  You can add this at the end of the entire script or after each Invoke SQL if you want to add other logging as well.

     

    If($error.Count -ge 1)
    {

    $error.ForEach({( ($_.ToString() + "n" + $_.Exception + "n" + $_.ErrorDetails.Message + "n" + $_.InvocationInfo.PositionMessage + "n") | Out-File "<Your Error File Here>" -Append)})
    $error.Clear()
    }
  • It's reporting back errors, but not the FILE NAME that didn't process.

    Thanks.

  • I guess it's not reporting the file name back in the error when you use a variable, you'll have to add that explicitly.

  • Honestly, use something like FlywayDB Community or a similar framework. No need to reinvent this stuff.

  • just wanted to expand upon what I already was using.. maybe make it better by trapping what passed and failed.

     

    Thanks.

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

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