• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1246
  • Last Modified:

PowerShell Output Log

I have created a PowerShell script to run a script on several SQL Servers. When I execute the PowerShell script, it displays the results of the first TSQL script, but doesn't show the results of the other TSQL scripts. Does anyone know how I can get this to show all output from the sql script?

Here is the output I am getting...

Serv1

Column1                                                                                            
-------                                                                                            
Microsoft SQL Server 2005 - 9.00.3073.00 (X64) ...    

Serv2

D0319D01

Column1                                                                                            
-------                                                                                            
Microsoft SQL Server 2005 - 9.00.3073.00 (X64) ...  

Here is the code in the script1.sql

SELECT @@VERSION

use database1;

SELECT TOP 10 * FROM table1

use database2;

SELECT TOP 10 * FROM table1

use database2;

SELECT TOP 10 * FROM table1
 
#Here is my code: 
 
$cmd = get-content "C:\script1.sql"
 
foreach ($svr in get-content "C:\serv.txt")
{
  $con = "server=$svr;database=master;Integrated Security=sspi"
  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
  $dt = new-object System.Data.DataTable
  $da.fill($dt) | out-null
  $svr
  $dt | Format-Table -autosize
}

Open in new window

0
computerstreber
Asked:
computerstreber
  • 2
1 Solution
 
BSonPoshCommented:
Try to move the format-table outside of the foreach
$cmd = get-content "C:\script1.sql"
 
@(foreach ($svr in get-content "C:\serv.txt")
{
  $con = "server=$svr;database=master;Integrated Security=sspi"
  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
  $dt = new-object System.Data.DataTable
  $da.fill($dt) | out-null
  $svr
  $dt 
}) | format-table

Open in new window

0
 
computerstreberAuthor Commented:
didn't work...
0
 
BSonPoshCommented:
I see... The problem would seem to be the SqlDataAdapter  class will only pass one statement at a time.

You can try this
SELECT @@VERSION
GO
use database1;
SELECT TOP 10 * FROM table1
GO
use database2;
SELECT TOP 10 * FROM table1
GO
use database2;
SELECT TOP 10 * FROM table1
GO

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now