VBA execute stored procedure gave “Operation is not allowed when the object is closed”

Recently we have a requirement which use macro to print some recordset value from a database table. Basically the VBA code which reference to DB part in Word Macro is as below


    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Dim totalcount As Integer

    strConn = "Provider=SQLOLEDB.1; Data Source= myserver\myinstance;Initial Catalog=mydb; Integrated Security=SSPI;"
    Set conn = New ADODB.Connection
    conn.Open strConn

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandText = "SpGetSomethingFromSomeDbTable"
    cmd.CommandType = adCmdStoredProc

    cmd.Parameters.Refresh
    cmd.Parameters(1).Value = CInt(divisionid)
    cmd.Parameters(2).Value = 1
    Set rs = cmd.Execute()

    totalcount = 0
    Do While Not rs.EOF
        ReDim Preserve membersFor(totalcount) As memberinfo
        membersFor(totalcount).field1 = RemoveQCInLastName(rs("fieldname1"))
        membersFor(totalcount).field2 = RemoveQCInLastName(rs("fieldname2"))
        membersFor(totalcount).field3 = RemoveQCInLastName(rs("fieldname3"))
        rs.MoveNext
        totalcount = totalcount + 1
    Loop

And part of the stored procedure is as below

if object_id('tempdb..#mytemptable') IS NOT NULL 
drop table #mytemptable

;with table1_CTE 
as(SELECT * FROM
	(SELECT *, ROW_NUMBER() OVER(PARTITION BY field1, field2 ORDER BY FieldDateTime DESC) AS rn
		FROM AnotherDatabase.dbo.table1 WHERE fieldDateTime < @specificdate) as T
 WHERE rn = 1  AND IsFlag = 1
 )
SELECT  m.field3, a.field4, a.field5
into #mytemptable
FROM table1_CTE  m JOIN someothertable a ON m.samefield = a.samefield
WHERE m.field1 = @currentvalue 

When I run macro, the error “Operation is not allowed when the object is closed” popup and stop at line
Set rs = cmd.Execute()

Actually this issue is not due to temp table or CTE table but the insert/delete/update DDL operation in stored procedure. The root reason is the count of the number of rows affected by those DDL statements will return as part of the result set. So the solution is easy and simple just need to add the following behind “AS” before the “BEGIN” in the stored procedure.

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

It took me 2 hours to figure out, hope it is helpful.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s