Can I use multiple Invoke-Sqlcmd commands in the same PowerShell script against different SQL servers?

Question

Here's what I have:

Add-PSSnapin sqlservercmdletsnapin100
Add-PSSnapin sqlserverprovidersnapin100

Invoke-Sqlcmd -inputfile "somefile0.sql" -Server "server0" -Username user0 -Password password0
Invoke-Sqlcmd -inputfile "somefile1.sql" -Server "server1" -Username user1 -Password password1

Each Invoke-Sqlcmd run by itself runs fine. When placed in a script that reads like above, the last Invoke-Sqlcmd returns no results to the screen.

If the first Invoke-Sqlcmd does and the second one doesn't, the problem is most likely with the second SQL script. Did you try running the SQL statements manually? — Ansgar Wiechers
– Ansgar Wiechers, Commented Oct 9, 2013 at 14:09
Thanks for your response! No matter the order of the Invoke-Sqlcmd statements, the first one executed runs just fine. It appears that the second one runs fine as well, but no output is sent to the screen. I've actually proposed an answer below that sets the result of each Invoke-Sqlcmd to a variable and then does a Write-Host on each variable's ItemArray member. Seems to work just fine now. — Bryan Kahlig
– Bryan Kahlig, Commented Oct 9, 2013 at 15:29

Bryan Kahlig · Accepted Answer · 2013-10-09 15:24:12Z

1

I figured this out. Turns out that it works well when I return the results of each Invoke-Sqlcmd to a variable and then output the variable. Like this:

Add-PSSnapin sqlservercmdletsnapin100
Add-PSSnapin sqlserverprovidersnapin100

$result0 = Invoke-Sqlcmd -inputfile "somefile0.sql" -Server "server0" -Username user0 -Password password0
$result1 = Invoke-Sqlcmd -inputfile "somefile1.sql" -Server "server1" -Username user1 -Password password1

Write-Host $result0.ItemArray
Write-Host $result1.ItemArray

The output to the screen is now visible, but poorly formatted. That'll get straightened out next.

answered Oct 9, 2013 at 15:24

Bryan Kahlig

191 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ansgar Wiechers Over a year ago

Now that you mention that, someone had a similar phenomenon some time ago. It seems to have something to do with how PowerShell outputs tabular data.

Collectives™ on Stack Overflow

Can I use multiple Invoke-Sqlcmd commands in the same PowerShell script against different SQL servers?

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related