?
Solved

PowerShell Output Log

Posted on 2009-02-16
3
Medium Priority
?
1,239 Views
Last Modified: 2012-05-06
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
Comment
Question by:computerstreber
  • 2
3 Comments
 
LVL 18

Expert Comment

by:BSonPosh
ID: 23656093
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
 
LVL 3

Author Comment

by:computerstreber
ID: 23663669
didn't work...
0
 
LVL 18

Accepted Solution

by:
BSonPosh earned 2000 total points
ID: 23664348
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Screencast - Getting to Know the Pipeline

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question