Load .sql from folder

Question

Load .sql from folder

Bruin

SSCrazy

Points: 2010
More actions
April 29, 2021 at 4:42 pm

#3877474
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
}
```

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

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

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.

Bruin SSCrazy Points: 2010 More actions · Answer 2

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

Thx.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

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()
}

Bruin SSCrazy Points: 2010 More actions · Answer 4

It's reporting back errors, but not the FILE NAME that didn't process.

Thanks.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 5

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.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 6

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

Bruin SSCrazy Points: 2010 More actions · Answer 7

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

Thanks.