I am Trying to Get the Results of a SQL Stored procedure that I am feeding multiple databases to restore. I have a function that Opens up the Connection with SQL as I'm going to be passing several databases to a single query. My problem is understanding how to get the results from the Stored procedure and being able to get values from those results.


function SQLRestoreDatabase {
    Param ($query, $connection)
       
    $sqlCommand = new-object System.Data.SqlClient.SqlCommand
    $sqlCommand.Connection= $connection
    $sqlCommand.CommandText= $query
                   
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $sqlCommand
    $DataSet = New-Object System.Data.DataSet   
    $SqlAdapter.Fill($DataSet)    
    $DataSet.Tables[0]
           
}

$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "server=" + $SQLServer + ";integrated security=true;database=" + $SQLDatabase
$SQLQuery = "exec dbo.restoredb"

SQLRestoreDatabase $SQLQuery $sqlConnection

These are the results that are returned every time I run the query.

1. I would like to be able to pull the value out of Col3. is there any way to do this or possibly a better way to run this SQL Stored Procedure to get the results that I am looking for?
2. Is there a way to make it so the results are not Output but I can still use them.
3. Also if there is a better way to format the output as Format-Table doesn't seem to do anything

 Col1                   Col2             Col3                       DateTime            
  ---------         ----------       ------------         -------------------            
             0                  0                 1                    8/29/2009 2:36:22 AM   

You could capture the array of DataRow returned from your function:
$dt = SQLRestoreDatabase $SQLQuery $sqlConnection
and then do something like this
$col3 = $dt | foreach {$_.Col3}
$col3[0]
Or using a select-object
$col3 = $dt | select Col3
$col3[0].Col3

I'm not sure what you mean by format-table doesn't do anything, the results you've posted are formated in table. Check get-help format-table for additional options.

EDIT: Looks Like this did not solve my issues When calling my Function

$dt = SQLRestoreDatabase $SQLQuery $sqlConnection
$col3 = $dt | select Col3
$col3[0].Col3

I get no result. This is my full code

function SQLRestoreDatabase {
    Param ($query, $connection)
       
    $sqlCommand = new-object System.Data.SqlClient.SqlCommand
    $sqlCommand.Connection= $connection
    $sqlCommand.CommandText= $query
                   
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $sqlCommand
    $DataSet = New-Object System.Data.DataSet   
    $SqlAdapter.Fill($DataSet)    
    $DataSet.Tables[0]
           
}

$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "server=" + $SQLServer + ";integrated security=true;database=" + $SQLDatabase
$SQLQuery = "exec dbo.restoredb"

$dt = SQLRestoreDatabase $SQLQuery $sqlConnection
$col3 = $dt | select Col3
$col3[0].Col3

I get No result displayed. When Debugging and going through my dt variable under the SyncRoot>[1]> shows the column names and then the values next to them. This lead me to believe that the function is returning the results that I am looking for I just can't figure out the proper syntax to retrieve the information I need. Thank you!!!!