CODEWITHSUNDEEP
sql-serverpowershell
Adam Hoge
Adam Hoge

Reputation: 39

Run multiple *.sql query files log to file

My goal is to have a PowerShell script run several Sqlquery.sql files against a specific SQL server and then log the output to a log file.

I can't get the logging to work and I don't know what I'm missing. My log file is always empty and I'm at a loss for that I am missing.

Contents of C:\Temp:

Build1.SQL
Build2.SQL
Build3.sql
Build4.sql
Build5.SQL
Build6.SQL
$PatchPostConvSQLScripts = Get-ChildItem -Path C::\Temp -Filter *.sql -Name
$Queries = $PatchPostConvSQLScripts
foreach ($query in $Queries){
    Write-Host "Starting: $query"
    Invoke-Sqlcmd -ServerInstance $DBServer -InputFile $query |
        Out-File "C:\TEMP\scriptResults.log"
    Write-Host "Completed: $query"
}

Once I get it logging to a file, I'll need to get a newline each time with a `n`r, but baby steps right now.

Is there a better way to do this that I just don't know?

Upvotes: 1

Views: 459

Answers (2)

gofr1
gofr1

Reputation: 15977

The main reason you got nothing in log file is that Output-File rewrite whole data in it on each run. Try to use -Verbose as mentioned in answer by TechSpud to collect print/server statements, or write output to temp file and Add-Content to main log file:

$DBServer = "MYPC\SQLEXPRESS"
$sqlPath = "C:\TEMP\"
$log = "scriptResults.log"
$tempOut = "temp.log"

$files = Get-ChildItem -Path $sqlPath -Filter *.sql -Name

foreach ($file in $files){
    Write-Host "Starting: $file"
    Invoke-SQLcmd -ServerInstance $DBServer -InputFile $sqlPath$file | Out-File $sqlPath$tempOut
    Get-Content $sqlPath$tempOut | Add-Content $sqlPath$log 
    Write-Host "Completed: $file"
}

Upvotes: 1

TechSpud
TechSpud

Reputation: 3518

Firstly, as @Ben Thul has mentioned in his comment, check that your SQL files actually output something (a resultset, or messages), by running them in Management Studio.

Then, you'll need to use the -Verbose flag, as this command will tell you.

Get-Help Invoke-Sqlcmd -Full

Invoke-Sqlcmd does not return SQL Server message output, such as the output of PRINT statements, unless you use the PowerShell -Verbose parameter.

$Queries = Get-ChildItem -Path C::\Temp -Filter *.sql -Name

Clear-Content -Path "C:\TEMP\scriptResults.log" -Force

foreach ($query in $Queries){
    Write-Host "Starting: $query"
    Invoke-Sqlcmd -ServerInstance $DBServer -InputFile $query -Verbose |
        Add-Content -Path "C:\TEMP\scriptResults.log"
    Write-Host "Completed: $query"
}

Upvotes: 0

Related Questions