Why is my SQL executed via Start-Job not returning the answer I expect?

Question

I have a database hosted on SQL Server 2008 which my regular user account does not have access to. In order to query it, I need to use my special "admin" account (just another AD account, but in different groups from my regular user account).

I came up with an idea to use background jobs in Powershell via Start-Job to run queries against this database, as you can start the job with different credentials from your logged in user, and thus integrated security on the database works properly. Since my issue, I've googled a lot this afternoon and seen a few people adopt this approach for the same reason, but their results seem to actually work - whereas mine isn't for some reason.

I have the follow powershell code:

[scriptblock]$sql_block = {
    $Query = "select * from some_table"
    $CW_DBConnection = New-Object Data.SqlClient.SQLConnection
    $CW_DBConnection.ConnectionString = "Data Source=someserver;Initial Catalog=some_database;Integrated Security=SSPI;"
    $CW_DBConnection.Open()

    $Command = New-Object Data.SqlClient.SqlCommand($Query,$CW_DBConnection)
    $Adapter = New-Object Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object Data.DataSet

    $Adapter.SelectCommand = $Command
    [void]$Adapter.Fill($DataSet)

    $CW_DBConnection.Close()

    return $DataSet
}

Which I execute via:

$mySQLJob = Start-Job -ScriptBlock $sql_block -Credential $(Get-Credential -UserName AD\MyAdminAccount -Message "Enter Admin Password")
Wait-Job $mySQLJob
$results = Receive-Job $mySQLJob

All this goes swimmingly. However when I come to interrogate the results object, I see this :

$results
RunspaceId              : 975030ec-d336-4583-9260-48439bb34292
RemotingFormat          : Xml
SchemaSerializationMode : IncludeSchema
CaseSensitive           : False
DefaultViewManager      : {System.Data.DataViewManagerListItemTypeDescriptor}
EnforceConstraints      : True
DataSetName             : NewDataSet
Namespace               :
Prefix                  :
ExtendedProperties      : {}
HasErrors               : False
IsInitialized           : True
Locale                  : en-GB
Site                    :
Relations               : {}
Tables                  : {System.Data.DataRow}
Container               :
DesignMode              : False
ContainsListCollection  : True

and when I try to get to the Tables bit:

$results.Tables[0]
System.Data.DataRow

$results.Tables[0].GetType()
IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     ArrayList                                System.Object

$results.Tables[0][0]
System.Data.DataRow

$results.Tables[0][0].GetType()
IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     String                                   System.Object

So literally, the result is just the string "System.Data.DataRow".

Where have I cocked up?

Note - running this from a powershell session actually executing as my Admin id and not doing it via Start-Job (i.e. just execute the SQL bits inline) works as expected, and I get actual data from the database back.

Answer 1

I'd already read the question at Can Powershell Receive-Job return a DataSet? when I posted this one - but apparently I didn't read it carefully enough - as the working answer returns not the top level DataSet object, but the Tables[0] property on it.

I changed my scriptblock this morning to return that, and lo-and-behold, I now get actual SQL data back.

So it appears that Receive-Job doesn't serialize the objects it returns to sufficient depth that you can return arbitrary ones (although I guess you could attempt to serialize them yourself - I've not tried that yet. UPDATE:See below).

So, in summary a one line change of

return $DataSet

to

return $DataSet.Tables[0]

Did the trick.

UPDATE: I've now tried the 'serialize it yourself' approach, and this seems to work ok. So first you update the script block to do this at the end:

$Serialized_DataSet = [System.Management.Automation.PSSerializer]::Serialize($DataSet,2)
return $Serialized_DataSet

and then when you want the results back:

$results = Receive-Job $mySQLJob
$deserialized_results = [System.Management.Automation.PSSerializer]::Deserialize($results)

and you can then see that $deserialized_results.Tables[0] actually contains results you can use.