Hi all.

So I want to create a connection to a SQL database, run a query and then output results, item by item:

 

$DBconn = new-object System.Data.SqlClient.SqlConnection
$DBconn.ConnectionString = "server=server01;database=databasename;Integrated Security=sspi"
$DBconn.Open()

$DBqry = new-object System.Data.SqlClient.SqlCommand
$DBqry.CommandTimeout = 30
$DBqry.CommandText = "SELECT a bunch of stuff WHERE onestuff = $strComputer"
$DBread = $DBconn.ExecuteReader()

Here is where I get stuck.  I know I am getting 13 specific data points returned by my query.  Is there a For Each loop I can use?  like:

foreach (recordset (array of the records returned) in $DBread) {

write-host array[0]
write-host array[1]
etc.

}

or

While ($DBread.Read())
{
write-host $DBread.array[0]
write-host $DBread.array[1]
etc.

}

$DBconn.Close()

 

Thanks!!

I think what your looking for is this.

while(!$DBread.Read())
{
$DBread["value"]
}

So the ExecuteReader stuff is not working.  I must not be calling something I need from .Net in order to use it. 

So I used the following code and can get a record retruned..I just need to know how to get the data once returned so I can output the values to Excel:

 

$strComputer = "targetsys01"

$SQLconn = new-object System.Data.SqlClient.SqlConnection "server=db01;database=db01;Integrated Security=sspi"
$SQLconn.Open()

$SQLcmd = new-object System.Data.SqlClient.SqlCommand
$SQLcmd.CommandTimeout = 30
$SQLcmd.CommandText = "SELECT NAME, ROOM_AREA, JACK_RACK FROM computersys WHERE NAME LIKE '" + $strComputer + "'"
write-host $SQLcmd.CommandText
$SQLcmd.Connection = $SQLconn

$SQLadapter = new-object System.Data.SqlClient.SqlDataAdapter
$SQLadapter.selectcommand = $SQLcmd
$DataSet = new-object System.Data.DataSet

$nRecs = $SqlAdapter.Fill($DataSet)

write-host ($nRecs.ToString() + " Records retrieved."

$SQLconn.Close()

if ($nRecs -gt 0)
{

write-host $DataSet.Tables[0].Rows[0]

}

 

This gives me 1 record returned.  If I type:

PS H:\> $DataSet.Tables | Select-Object -Expand Rows

I get:

NAME                                    ROOM_AREA                               JACK_RACK
----                                    ---------                               ---------
Server01                            DATA CENTER 01                      Row 50, Cluster 10

So what would I do to assign name, room_area, and jack_rack values to my Excel?

Something like:

$WS.Cells.Item($count, 12) = $DataSet.Tables[0].Rows[0].Columns[1].Record[0]
$WS.Cells.Item)$count, 13) = $DataSet.Tables[0].Rows[0].Columns{2].Record{0]

 

Thanks for the help!!

If you want the results sent to excel, you could use Export-CSV.

if ($nRecs -gt 0)
{
$nRecs.Tables[0].Rows | Export-Csv -NoTypeInformation - Path .\yourfile.csv
}

MMM...

Getting the following error:

Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null.
At line:3 char:32
+ $nRecs.Tables.Rows | Export-Csv <<<< -NoTypeInformation -Path "c:\ps\export.csv"

Sorry, the formating on the posts changed the code.. After the $nRecs.Tables there should be a "[" and then a "0" and then "]" then .Rows | Export-CSV -NoTypeInformation -Path "C:\ps\export.csv"

Ok...so solved.

 

Got the ExecuteReader() working and that is a very easy way to do it.

 

$dr = $SQLcmd.ExecuteReader()
      while($dr.Read())
       {
   
       $WS.Cells.Item($count,10) = $dr.GetValue(1) + $dr.GetValue(2)
       $WS.Cells.Item($count,16) = $dr.GetValue(3)

       }

BTW, I like the way you were writing your SQL query to the console to verify it. Good idea. However, consider using Write-Debug instead. That way you can leave the command in your script and shut off the debug pipeline using $DebugPreference.

And if you want varying levels of verbosity, I would also recommend Write-Verbose. That gives you three levels of consistent verbosity:

- normal (quiet)
- routine output (verbose)
- high amount of output (debug)

And when things go wrong, and you are writing Trap {}'s and such, you also have Write-Warning and Write-Error.

I also like to put a $Verbose parameter in "production" scripts. All it does is if ($Verbose) { $VerbosePreference = "Continue" }, which is basically what a cmdlet with -Verbose does.