Unexpected variable type returned by Receive-Job
Solution 1:
- Is there a way to get the correct/expected variable type to be returned when calling
Receive-Job
?
Due to using a background job, you lose type fidelity: the objects you're getting back are method-less emulations of the original types.
Manually recreating the original types is not worth the effort and may not even be possible - though perhaps working with the emulations is enough.
Update: As per your own answer, switching from working with System.DataSet
to System.DataTable
resulted in serviceable emulations for you.[1]
See the bottom section for more information.
- Is there a better way to run SQL queries under a different AD account, using the Invoke-Sqlcmd command?
You need an in-process invocation method in order to maintain type fidelity, but I don't think that is possible with arbitrary commands if you want to impersonate another user.
For instance, the in-process (thread-based) alternative to Start-Job
- Start-ThreadJob
- doesn't have a -Credential
parameter.
Your best bet is therefore to try to make Invoke-SqlCmd
's -Credential
parameter work for you or find a different in-process way of running your queries with a given user's credentials.
Serialization and deserialization of objects in background jobs / remoting / mini-shells:
Whenever PowerShell marshals objects across process boundaries, it employs XML-based serialization at the source, and deserialization at the destination, using a format known as CLI XML (Common Language Infrastructure XML).
This happens in the context of PowerShell remoting (e.g., Invoke-Command
calls with the-ComputerName
parameter) as well as in background jobs (Start-Job
) and so-called mini-shells (which are implicitly used when you call the PowerShell CLI from inside PowerShell itself with a script block; e.g., powershell.exe { Get-Item / }
).
This deserialization maintains type fidelity only for a limited set of known types, as specified in MS-PSRP, the PowerShell Remoting Protocol Specification. That is, only instances of a fixed set of types are deserialized as their original type.
Instances of all other types are emulated: list-like types become [System.Collections.ArrayList]
instances, dictionary types become [hasthable]
instances, and other types become method-less (properties-only) custom objects ([pscustomobject]
instances), whose .pstypenames
property contains the original type name prefixed with Deserialized.
(e.g., Deserialized.System.Data.DataTable
), as well as the equally prefixed names of the type's base types (inheritance hierarchy).
Additionally, the recursion depth for object graphs of non-[pscustomobject]
instances is limited to 1
level - note that this includes instance of PowerShell custom classes, created with the class
keyword: That is, if an input object's property values aren't instance of well-known types themselves (the latter includes single-value-only types, including .NET primitive types such as [int]
, as opposed to types composed of multiple properties), they are replaced by their .ToString()
representations (e.g., type System.IO.DirectoryInfo
has a .Parent
property that is another System.IO.DirectoryInfo
instance, which means that the .Parent
property value serializes as the .ToString()
representation of that instance, which is its full path string); in short: Non-custom (scalar) objects serialize such that property values that aren't themselves instances of well-known types are replaced by their .ToString()
representation; see this answer for a concrete example.
By contrast, explicit use of CLI XML serialization via Export-Clixml
defaults to a depth of 2
(you can specify a custom depth via -Depth
and you can similarly control the depth if you use the underlying System.Management.Automation.PSSerializer
type directly).
Depending on the original type, you may be able to reconstruct instances of the original type manually, but that is not guaranteed.
(You can get the original type's full name by calling .pstypenames[0] -replace '^Deserialized\.'
on a given custom object.)
Depending on your processing needs, however, the emulations of the original objects may be sufficient.
[1] Using System.DataTable
results in usable emulated objects, because you get a System.Collections.ArrayList
instance that emulates the table, and custom objects with the original property values for its System.DataRow
instances. The reason this works is that PowerShell has built-in logic to treat System.DataTable
implicitly as an array of its data rows, whereas the same doesn't apply to System.DataSet
.
Solution 2:
I can't say for question 2 as I've never used the job commands but when it comes to running the Invoke-Sqlcmd
I always make sure that the account that runs the script has the correct access to run the SQL.
The plus to this is that you don't need to store the credentials inside the script, but is usually a moot point as the scripts are stored out of reach of most folks, although some bosses can be nit picky!
Out of curiosity how do the results compare if you pipe them to Get-Member
?