How to actually unset (set to Nothing) COM-object property in Powershell?

Question

I am having problems adapting this VBA/VB6 disconnected Recordset code to Powershell. Setting to Nothing fails to translate:

'disconnect the recordset and close the connection
Set rs.ActiveConnection = Nothing

1st attempt (use $null):

$rs.ActiveConnection = $null

ERROR: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
ERROR: + $rs.ActiveConnection = $null
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : OperationStopped: (:) [], COMException
ERROR:     + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

2nd attempt (do not perform set to nothing):

$conn = new-Object -com "ADODB.Connection"
$rs = New-Object -ComObject "ADODB.Recordset"
$conn.ConnectionString = "Provider=SQLNCLI11;Server=myserver;Database=mydb;Integrated Security=SSPI;"
$conn.Open()
$rs.CursorLocation = 3 # adUseClient '<<<< important!
$rs.Open("select * from mytable where 1<>1", $conn, 2, 4) # adOpenDynamic, adLockBatchOptimistic
#$rs.ActiveConnection = $null
$conn.Close()
$rs.AddNew()
...
ERROR: Operation is not allowed when the object is closed.
ERROR: + $rs.AddNew()
ERROR: + ~~~~~~~~~~~~
ERROR:     + CategoryInfo          : OperationStopped: (:) [], COMException
ERROR:     + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Note that I am not interested in the alternate solutions such as Invoke-Sqlcmd, ADOR and Out-DataTable. I have tried them all and they are not suitable in my case.

$PSVersionTable

Name                           Value                                                                                                                                                                                              
----                           -----                                                                                                                                                                                              
PSVersion                      5.1.14409.1012                                                                                                                                                                                     
PSEdition                      Desktop                                                                                                                                                                                            
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                                                                            
BuildVersion                   10.0.14409.1012                                                                                                                                                                                    
CLRVersion                     4.0.30319.42000                                                                                                                                                                                    
WSManStackVersion              3.0                                                                                                                                                                                                
PSRemotingProtocolVersion      2.3                                                                                                                                                                                                
SerializationVersion           1.1.0.1

Here the working source code for reference (it does work as VBA in Excel using ADO 6.1):

Sub Tester()

    Dim con As ADODB.Connection, rs As ADODB.Recordset
    Dim i As Long

    Set con = getConn()

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient '<<<< important!

    'get an empty recordset to add new records to
    rs.Open "select * from Table1 where false", con, _
             adOpenDynamic, adLockBatchOptimistic

    'disconnect the recordset and close the connection
    Set rs.ActiveConnection = Nothing
    con.Close
    Set con = Nothing

    'add some new records to our test recordset
    For i = 1 To 100
        rs.AddNew
        rs("UserName") = "Newuser_" & i
    Next i

    'reconnect to update
    Set con = getConn()
    Set rs.ActiveConnection = con

    rs.UpdateBatch '<<< transfer to DB happens here: no loop!

    rs.Close 

    'requery to demonstrate insert was successful
    rs.Open "select * from Table1", con, _
            adOpenDynamic, adLockBatchOptimistic

    Do While Not rs.EOF
        Debug.Print rs("ID").Value, rs("UserName").Value
        rs.MoveNext
    Loop

    rs.Close
    con.Close
End Sub

Function getConn() As ADODB.Connection
    Dim rv As New ADODB.Connection
    Dim strConn As String

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" _
     & "Data Source = " & ThisWorkbook.Path & "\Test.accdb"

    rv.Open strConn
    Set getConn = rv
End Function

UPDATE: Final working code (thanks to the answer below):

$ErrorActionPreference = 'Stop'
$src = Import-CSV -Path 'c:\mydata.csv'
$conn = new-Object -com "ADODB.Connection"
$rs = New-Object -ComObject "ADODB.Recordset"
$conn.ConnectionString = "Provider=SQLNCLI11;Server=myserver;Database=mydb;Integrated Security=SSPI;"
$conn.Open()
$rs.CursorLocation = 3 # adUseClient '<<<< important!

#get an empty recordset with fields defined
$rs.Open("select * from mytable where 1<>1", $conn, 2, 4) # adOpenDynamic, adLockBatchOptimistic

#
#disconnect the recordset
#

# Create a wrapper for the value null as per ZiggZagg's answer https://stackoverflow.com/a/49682554/2746150
[System.Runtime.InteropServices.UnknownWrapper]$nullWrapper = New-Object "System.Runtime.InteropServices.UnknownWrapper" -ArgumentList @($null);
# Get the the type for ADODB.Recordset as per ZiggZagg's answer https://stackoverflow.com/a/49682554/2746150
[Type]$recordSetType = [Type]::GetTypeFromProgID("ADODB.Recordset", $true);
# Write the property ActiveConnection as per ZiggZagg's answer https://stackoverflow.com/a/49682554/2746150
$recordSetType.InvokeMember([string]"ActiveConnection", [System.Reflection.BindingFlags]::SetProperty, [System.Reflection.Binder]$null, [object]$rs, [object[]]@($nullWrapper));
# Close connection
$conn.Close()

#fillup code
Foreach ($row in $src) {
    $rs.AddNew()
    Foreach ($col in $row.psobject.Properties.Name) {
        $fld = $rs.Fields.Item($col)
        $fld.Value = $row.$col
    }
}

#reconnect
$conn.Open()
$rs.ActiveConnection = $conn

#final update
$rs.UpdateBatch()
$rs.Close()

This code is used for bulk loading of 10K-50K of rows with 100+ fields (varying). Similar code using Out-DataTable has ten times more lines (as each field has to be defined) and has similar performance.

Your code makes no sense. You are closing the connection and then trying to call `AddNew`. Just do that the other way round. — DavidG, Mar 27 '18 at 10:28
@DavidG, it is about _disconnected_ recordset. And yes, is the other way round. Close the connection 1st and call AddNew afterwards. — Anton Krouglov, Mar 27 '18 at 10:58
Interesting stuff. `$Null` ought to work, but the PowerShell binders apparently don't handle this case correctly -- for whatever reason. By far the way of least resistance would be to keep this code as-is and use `cscript` to run it, or else migrate to anything using ADO.NET rather than classic ADO. But there really *ought* to be a solution in PowerShell itself. — Jeroen Mostert, Mar 27 '18 at 11:16
@Jeroen Mostert, I have tried various combinations of `$conn.Close()`, `Clear-Variable conn` and `[Runtime.InteropServices.Marshal]::ReleaseComObject($rs.ActiveConnection)` - no luck. Indeed there are should be a PS solution to that. I suspect there is some code in property setter for ActiveConnection that marks recordset as 'Disconnected' when `Nothing` comes in. — Anton Krouglov, Mar 27 '18 at 11:21
$rs.ActiveConnection is not the COM object. So you cannot release it. $rs is the COM object you could release it but then it is all gone. Releasing the ***connection*** is a function of the object itself. Not a function of powershell. — EBGreen, Mar 27 '18 at 12:34
@anton-krouglov I posted an article about Checking for Null maybe this will help with setting the object to null: https://powershellposse.com/2017/08/17/what-the-null/ — Thom Schumacher, Mar 27 '18 at 18:23
This is exceedingly ugly, but if you call `Marshal::FinalReleaseComObject` on `$rs.ActiveConnection`, the recordset is disconnected. The drawback is that the connection becomes unusable entirely since it's disconnected from its wrapper as well, so you have to omit `$conn.Close()` and you can't reconnect the recordset afterwards. I'm not even sure this properly closes the connection. Use at your own peril. — Jeroen Mostert, Mar 30 '18 at 12:57
I think cursor type should be adOpenStatic = 3. Dynamic cursors are server side cursors. Perhaps setting it to dynamic changes the cursor location to server — Jesús López, Apr 03 '18 at 05:36
@AntonKrouglov forget about disconnected recordsets. They were replaced by datasets 15 years ago, which are disconnected by definition. Powershell is *.NET*. You should use ADO.NET classes. Better yet, use the SQL Server [modules](https://learn.microsoft.com/en-us/sql/powershell/sql-server-powershell) and [commands](https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps), eg: `Invoke-Sqlcmd -InputFile "C:\ScriptFolder\TestSqlCmd.sql" | Out-File -FilePath "C:\ScriptFolder\TestSqlCmd.rpt"` — Panagiotis Kanavos, Apr 03 '18 at 12:10
The [Invoke-Sqlcmd](https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps) command can return results as objects or datasets `$DS = Invoke-Sqlcmd -ServerInstance "MyComputer" -Query "SELECT ID, Item FROM MyDB.dbo.MyTable" -As DataSet; $DS.Tables[0].Rows | %{ echo "{ $($_['ID']), $($_['Item']) }" };` — Panagiotis Kanavos, Apr 03 '18 at 12:14
As I have stated, contemporary methods just do not apply in my particular case. Good old disconnected recordset does the job in one line. — Anton Krouglov, Apr 03 '18 at 12:17

score 1 · Accepted Answer · answered Apr 05 '18 at 22:36

I have to admit I am not sure whether or not the following does the same as your VBA script. Would you mind checking if it does what your are expecting?

# Create a wrapper for the value null
[System.Runtime.InteropServices.UnknownWrapper]$nullWrapper = New-Object "System.Runtime.InteropServices.UnknownWrapper" -ArgumentList @($null);
# Get the the type for ADODB.Recordset
[Type]$recordSetType = [Type]::GetTypeFromProgID("ADODB.Recordset", $true);
# Write the property ActiveConnection
$recordSetType.InvokeMember([string]"ActiveConnection", [System.Reflection.BindingFlags]::SetProperty, [System.Reflection.Binder]$null, [object]$rs, [object[]]@($nullWrapper));

Thank you - it worked. Just some minor quirks needs to be added - see update in the question. — Anton Krouglov, Apr 06 '18 at 13:31

How to actually unset (set to Nothing) COM-object property in Powershell?

1 Answers1