I'm new to Powershell, and have been having an odd problem trying to use the SQL reader to get rows from an MSSQL 2005 table.

I'm using the following code to try to access the DB and get a reader object with which to obtain the values. When I step through the code in a debugger, I'm seeing that, on execution of the ExecuteReader command, a Reader object with rows and with visible fields is being created. However, when I step into the while($reader.read()) loop, the Read method returns False, and I skip the commands in the loop.

I've run the query in SQL Management Studio, and confirmed that values are returned. I'm not getting any error messages in the connection commands... and most baffling: this code seemed to work up until about four days ago, when it stopped returning a reader I could, well, read, with no changes to the machine I've been running this on whatsoever.

Could someone shed a little light on this problem? Thanks in advance, and I apologize if this has been raised before (I searched, but didn't see a similar problem).

Thanks,

Rob

$query = "Select machine_name, job_number, time_start From job_status"
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=DATABASE_SERVER;Initial Catalog=compatibility_database;User ID=sa;Password=Password"
$conn.Open()
$cmd = new-object System.Data.SqlClient.SqlCommand ($query, $conn)
$r = $cmd.ExecuteReader()
while($r.Read())
{Write-Host("hey")}

My standby SQL script has a few extra bits such as setting the SqlCommand.CommandType and creating a DataTable object to hold the results.  It looks like this:

function Invoke-SqlQuery {

    param (
        [string]$Query = $(Throw "You must specify a SQL statement.")
    )
    # Script variables
    $script:dbserver = "dbserver"
    $script:database = "some_table"
  
    #init the connection
    $connString = "server=$dbserver;Integrated Security=SSPI;database=$database"
    $cn = new-object "System.Data.SqlClient.SqlConnection" $connString
  
    # create the command
    $cmd = new-object "System.Data.SqlClient.SqlCommand"
    $cmd.CommandType = [System.Data.CommandType]"Text"
    $cmd.CommandText = $Query
    $cmd.Connection = $cn
    
    #get the data
    $dt = new-object "System.Data.DataTable"
    
    $cn.Open()
    $rdr = $cmd.ExecuteReader()
    
    $dt.Load($rdr)
    $cn.Close()
    
    write-output $dt
}