Remote scope to local

Question

thanks a lot @brianlist, I have tried your suggestion and it gives me this error when I use it with bulkcopy, not sure if the value has passed on to $value.

Cannot convert argument "0", with value: "System.Object[]", for "WriteToServer"
to type "System.Data.DataRow[]": "Cannot convert the "System.Data.DataRow" value
of type "Deserialized.System.Data.DataRow" to type "System.Data.DataRow"."
At C:\test\modified.ps1:11 char:24
+ $bulkCopy.WriteToServer <<<< ($value)
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

Get-Content 'C:\test\computers.txt' | ? { $_.trim() -ne "" } | ForEach-Object {
    $value =  Invoke-Command -Computer $_ -ScriptBlock {
        Param($computer)
        #..
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $global:mdtable = New-Object System.Data.DataTable
        $SqlAdapter.SelectCommand = $SqlCmd
        $nRecs = $SqlAdapter.Fill($mdtable)
        $mdtable
    } -ArgumentList $_ -Credential $cred 
} | test

Function test {
    $Database = 'Test1'
    $table = 'dbo.table_2'
    $connectionstring = "Data Source=mylocal;Integrated Security=True;Initial Catalog=$Database"
    $Conn  = New-Object System.Data.SqlClient.SQLConnection($connectionstring)
    $conn.open()
    $bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($conn)
    $bulkcopy.DestinationTableName = $table
    $bulkCopy.WriteToServer($value)
    $conn.close()
}

You should comment on the answer itself to discuss it (that also notifies the answerer), though I appreciate you editing the error into the question. It looks like you might be returning more than just the value of `$mdtable` from the scriptblock. Make sure none of the other statements you're using return a value. If they do, pipe to `Out-Null` to suppress them (or assign to `$null`). — briantist, Dec 07 '15 at 03:14
[Maybe related](http://stackoverflow.com/q/1918190/1630171). — Ansgar Wiechers, Dec 07 '15 at 07:08
I have made $nRecs | Out-Null and it still gives me the same error. This is the last step for this script to work, can anyone help me find out the issue — Saikiran Paramkusham, Dec 08 '15 at 04:32
What is that supposed to achieve? Echoing a variable and piping the output into `Out-Null` is the same as not echoing the variable in the first place. Did you try returning `,$mdtable.Tables[0]`? (note the leading comma) — Ansgar Wiechers, Dec 08 '15 at 08:01
no luck i still get the same error. "Cannot convert the "System.Data.DataRow" value of type "Deserialized.System.Data.DataRow" to type "System.Data.DataRow"." — Saikiran Paramkusham, Dec 08 '15 at 10:42
apparently $mdtable is of type of System.Data.DataRow but the $value is of type Deserialized.System.Data.DataRow, is there anyway to change the type in order to make this work — Saikiran Paramkusham, Dec 08 '15 at 12:52
This is resolved by piping the output of the $value to Out-DataTable — Saikiran Paramkusham, Dec 09 '15 at 03:08

score 4 · Accepted Answer · answered Dec 06 '15 at 19:56

You're already returning the value of $mdtable from the Invoke-Command scriptblock. To get the value outside of that, just asign the result of Invoke-Command to a variable:

Get-Content 'C:\test\computers.txt'| ? {$_.trim() -ne "" } | ForEach-object{
    $value = Invoke-Command -Computer $_ -ScriptBlock {
        Param($computer)
        # ...
        $mdtable
    }

In this example, $value will contain what $mdtable had.

Remote scope to local

1 Answers1