0

I'm writing a powershell script to loop through a folder full of *.sql files, execute them one at a time, displaying the results after each file.

The problem is that while it seems to run Invoke-Sqlcmd each time through the loop, it doesn't display a resultset until it has looped through all of the files. At that point it only gives the first resultset.

I know that Invoke-SQLCmd will only return the first dataset of any given script you pass it, which is why I've split my scripts up into different files and I'm trying to:

  • Run script 1
  • Get resultant dataset 1
  • Then run script 2
  • Get resultant dataset2

Test setup is this:

One instance of SQL Server with two databases: Test1 and Test2 Each DB has a single table defined as follows

CREATE TABLE tblTest(
    [strTest] nvarchar(250)
)

Test1.dbo.tblTest has one row that says "This is a test"

Test2.dbo.tblTest has one row that says "Second test"

I have a folder with two scripts. They both look like this except the second one uses [Test2] and [Result2]

USE [Test1]
GO
SELECT [Result1] = strTest FROM tblTest

The powershell script I've written is:

$SQLServer = "TESTBED1"
$DB = "master"
$Folder = "C:\Test\SQLScripts"

$Files = Get-ChildItem $Folder -Filter *.sql

ForEach ($File in $Files){
    Write-Host "Running $($File.FullName)"
    Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -InputFile $File.FullName -VERBOSE
}

What I'm expecting as output is:

Running C:\Test\SQLScripts\Script1.sql
VERBOSE: Changed database context to 'Test1'.
Result1
-------
This is a test

Running C:\Test\SQLScripts\Script2.sql
VERBOSE: Changed database context to 'Test2'.
Result2
-------
Second Test

But what I'm actually getting as output is:

Running C:\Test\SQLScripts\Script1.sql
VERBOSE: Changed database context to 'Test1'.

Running C:\Test\SQLScripts\Script2.sql
VERBOSE: Changed database context to 'Test2'.
Result1
-------
This is a test

It seems like it keeps the SQLCMD object around, sending each file to it one at a time, then tells it to close, wherein it spits out the first dataset it came across.

Is there a way for me to have it close off its connection in between loops so that it runs the file, spits out the results, quits, then repeats?

Cryovenom
  • 1
  • 1
    Change Write-Host to Write-Output – Peter Schneider Jun 26 '20 at 13:24
  • The lack of synchronization between host and pipeline output is limited to PS v5+ and a very specific - albeit still common - scenario: implicitly _table_-formatted output for types that do _not_ have formatting data defined for them - see [this answer](https://stackoverflow.com/a/43691123/45375). The solution is to either do without `Write-Host` or, conversely, force the pipeline output synchronously to the host (display) with `Out-Host`. – mklement0 Jun 26 '20 at 13:31
  • If anyone stumbles across this thread in the future, the problem isn't Write-Host (same problem whether my Write-Host "Running..." lines are there or not), but piping the output of Invoke-Sqlcmd to Out-Host does resolve the issue. So I changed one line in my script to : Invoke-Sqlcmd -ServerInstance ....... -VERBOSE | Out-Host – Cryovenom Jun 26 '20 at 14:17
  • There's also another subtle issue with the output formatting - both results *are* being displayed in the same table, but the column headers in the table are determined by the first object in the input pipeline, which has a Result1 property so the second row appears blank because it only has a value in the Result2 property which isn't shown as a column in the table. If you ```out-host``` each value separately you'll get two tables with different column headers - Result1 in the first and Result2 in the second. – mclayton Jun 26 '20 at 14:19

0 Answers0