[SQL] Error: 3704 Operation is not allowed when the object is closed for some records

I'm trying to run this piece of VBA code to get metadata from the PowerBI Rest API. For reference, I have other "Get" Functions with SQL queries in the same Module and they all work fine. However, when I run the below function I get two error messages:

• 
  GetDatasetTables Error: -1056112626 Method 'Open' of object '_Connection' failed
  
  
• 
  GetDatasetTables Error: 3704 Operation is not allowed when the object is closed
  

The function still extracts metadata from several reports but there's a lot of them that get the above errors and no data is extracted.

Private Sub GetDatasetTables(ByVal psWorkspaceID As String, ByVal psDatasetID As String, Optional ByVal psLocation As String = "us-north-central-c-primary")

On Error GoTo Err

'Power BI variables
Dim loPBIConn As ADODB.Connection
Dim lsPBIConn As String
Dim loPBIRS As Recordset
Dim lsPBISQL As String

'DB2 variables
Dim loConn As ADODB.Connection
Dim loCmd As ADODB.Command
Dim lsCmdText As String

WriteLog "Processing tables in dataset " & psDatasetID, , True

'Other variables
Dim lsout As String

'connect to DB2 database
Set loConn = New ADODB.Connection
loConn.ConnectionString = GetDB2ConnectionString()
loConn.ConnectionTimeout = 600
loConn.Open

'setup DB2 command
Set loCmd = New ADODB.Command
loCmd.ActiveConnection = loConn
loCmd.CommandTimeout = 600

'configure PBI connection string
lsPBIConn = "Provider=MSOLAP.7;Integrated Security=SSPI;Data Source=https://analysis.windows.net/powerbi/api;;"
lsPBIConn = lsPBIConn & "Initial Catalog=" & psDatasetID
lsPBIConn = lsPBIConn & ";Location=https://wabi-" & psLocation & "-redirect.analysis.windows.net/xmla?vs=sobe_wowvirtualserver&"
lsPBIConn = lsPBIConn & "db=" & psDatasetID
lsPBIConn = lsPBIConn & ";MDX Compatibility= 1; MDX Missing Member Mode= Error; Safety Options= 2; Update Isolation Level= 2"

'connect to PBI database
Set loPBIConn = New ADODB.Connection
loPBIConn.ConnectionString = lsPBIConn
loPBIConn.ConnectionTimeout = 600
loPBIConn.Open

'Check if connection is open
If loPBIConn.State = adStateOpen Then

'table insert

'create insert query
Set loCmd = New ADODB.Command
loCmd.ActiveConnection = loConn
loCmd.CommandTimeout = 600

'change to merge instead of delete->insert
lsCmdText = "MERGE INTO a.dataset_table dest "
lsCmdText = lsCmdText & "USING (VALUES('" & psWorkspaceID & "', '" & psDatasetID & "', ?, ?, ?, ?, ?, ?, CURRENT DATE)) "
lsCmdText = lsCmdText & "AS src (workspace_id, dataset_id, table_name, table_type, table_description, table_olap_type, date_created, date_modified, updateddate) "
lsCmdText = lsCmdText & "ON src.workspace_id = dest.workspace_id AND src.dataset_id = dest.dataset_id AND src.table_name = dest.table_name AND src.table_type = dest.table_type "
lsCmdText = lsCmdText & "WHEN MATCHED THEN UPDATE SET dest.workspace_id = src.workspace_id, dest.dataset_id = src.dataset_id, dest.table_name = src.table_name, dest.table_type = src.table_type, dest.table_description = src.table_description, dest.table_olap_type = src.table_olap_type, dest.date_created = src.date_created, dest.date_modified = src.date_modified, dest.updateddate = src.updateddate "
lsCmdText = lsCmdText & "WHEN NOT MATCHED THEN INSERT (workspace_id, dataset_id, table_name, table_type, table_description, table_olap_type, date_created, date_modified, updateddate) VALUES (src.workspace_id, src.dataset_id, src.table_name, src.table_type, src.table_description, src.table_olap_type, src.date_created, src.date_modified, src.updateddate) "

loCmd.CommandType = adCmdText
loCmd.CommandText = lsCmdText

loCmd.Parameters.Append loCmd.CreateParameter("table_name", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("table_type", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("table_description", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("table_olap_type", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("date_created", adDBTimeStamp)
loCmd.Parameters.Append loCmd.CreateParameter("date_modified", adDBTimeStamp)

'get PBI recordset - table

lsPBISQL = "SELECT * FROM $SYSTEM.DBSCHEMA_TABLES WHERE [TABLE_TYPE] = 'TABLE' "
Set loPBIRS = loPBIConn.Execute(lsPBISQL)

'For each row
Do While Not loPBIRS.EOF

'check for unneeded column
If InStr(1, Replace(loPBIRS("TABLE_NAME").Value & "", "$", ""), "DateTableTemplate", vbTextCompare) = 0 _
And InStr(1, Replace(loPBIRS("TABLE_NAME").Value & "", "$", ""), "LocalDateTable", vbTextCompare) = 0 Then

'set parameters
loCmd.Parameters("table_name").Value = loPBIRS("TABLE_NAME").Value & ""
loCmd.Parameters("table_type").Value = loPBIRS("TABLE_TYPE").Value & ""
loCmd.Parameters("table_description").Value = loPBIRS("DESCRIPTION").Value & ""
loCmd.Parameters("table_olap_type").Value = loPBIRS("TABLE_OLAP_TYPE").Value
loCmd.Parameters("date_created").Value = loPBIRS("DATE_CREATED").Value
loCmd.Parameters("date_modified").Value = loPBIRS("DATE_MODIFIED").Value

'execute insert
loCmd.Execute

End If

'idle
DoEvents

'fetch next record
loPBIRS.MoveNext

Loop

loPBIRS.Close


'column insert

'create insert query
Set loCmd = New ADODB.Command
loCmd.ActiveConnection = loConn
loCmd.CommandTimeout = 600

'change to merge instead of delete->insert
lsCmdText = "MERGE INTO a.dataset_table_column dest "
lsCmdText = lsCmdText & "USING (VALUES('" & psWorkspaceID & "', '" & psDatasetID & "', ?, ?, ?, ?, ?, ?, ?, ?, CURRENT DATE)) "
lsCmdText = lsCmdText & "AS src (workspace_id, dataset_id, table_name, column_name, column_description, column_olap_type, data_type, char_max_length, numeric_precision, numeric_scale, updateddate) "
lsCmdText = lsCmdText & "ON src.workspace_id = dest.workspace_id AND src.dataset_id = dest.dataset_id AND src.table_name = dest.table_name AND src.column_name = dest.column_name "
lsCmdText = lsCmdText & "WHEN MATCHED THEN UPDATE SET dest.workspace_id = src.workspace_id, dest.dataset_id = src.dataset_id, dest.table_name = src.table_name, dest.column_name = src.column_name, dest.column_description = src.column_description, dest.column_olap_type = src.column_olap_type, dest.data_type = src.data_type, dest.char_max_length = src.char_max_length, dest.numeric_precision = src.numeric_precision, dest.numeric_scale = src.numeric_scale, dest.updateddate = src.updateddate "
lsCmdText = lsCmdText & "WHEN NOT MATCHED THEN INSERT (workspace_id, dataset_id, table_name, column_name, column_description, column_olap_type, data_type, char_max_length, numeric_precision, numeric_scale, updateddate) VALUES (src.workspace_id, src.dataset_id, src.table_name, src.column_name, src.column_description, src.column_olap_type, src.data_type, src.char_max_length, src.numeric_precision, src.numeric_scale, src.updateddate) "

loCmd.CommandType = adCmdText
loCmd.CommandText = lsCmdText

loCmd.Parameters.Append loCmd.CreateParameter("table_name", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("column_name", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("column_description", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("column_olap_type", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("data_type", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("char_max_length", adInteger, , 0)
loCmd.Parameters.Append loCmd.CreateParameter("numeric_precision", adSmallInt, , 0)
loCmd.Parameters.Append loCmd.CreateParameter("numeric_scale", adSmallInt, , 0)



'get PBI recordset - table

lsPBISQL = "SELECT * FROM $SYSTEM.DBSCHEMA_COLUMNS WHERE [COLUMN_OLAP_TYPE] <> 'SCHEMA' "
Set loPBIRS = loPBIConn.Execute(lsPBISQL)

'For each row
Do While Not loPBIRS.EOF

'check for unneeded column
If InStr(1, Replace(loPBIRS("COLUMN_NAME").Value & "", "$", ""), "RowNumber-", vbTextCompare) = 0 _
And InStr(1, Replace(loPBIRS("TABLE_NAME").Value & "", "$", ""), "DateTableTemplate", vbTextCompare) = 0 _
And InStr(1, Replace(loPBIRS("TABLE_NAME").Value & "", "$", ""), "LocalDateTable", vbTextCompare) = 0 Then

'set parameters
loCmd.Parameters("table_name").Value = loPBIRS("TABLE_NAME").Value & ""
loCmd.Parameters("column_name").Value = loPBIRS("COLUMN_NAME").Value & ""
loCmd.Parameters("column_description").Value = loPBIRS("DESCRIPTION").Value & ""
loCmd.Parameters("column_olap_type").Value = loPBIRS("COLUMN_OLAP_TYPE").Value & ""
loCmd.Parameters("data_type").Value = loPBIRS("DATA_TYPE").Value & ""
loCmd.Parameters("char_max_length").Value = "0" & loPBIRS("CHARACTER_MAXIMUM_LENGTH").Value
loCmd.Parameters("numeric_precision").Value = "0" & loPBIRS("NUMERIC_PRECISION").Value & ""
loCmd.Parameters("numeric_scale").Value = "0" & loPBIRS("NUMERIC_SCALE").Value

'execute insert
loCmd.Execute

End If

'idle
DoEvents

'fetch next record
loPBIRS.MoveNext


Loop 'Do While Not loPBIRS.EOF



'-----------------------------------------------------------------------
'schema insert

'create insert query
Set loCmd = New ADODB.Command
loCmd.ActiveConnection = loConn
loCmd.CommandTimeout = 600

'change to merge instead of delete->insert
lsCmdText = "MERGE INTO a.dataset_catalog dest "
lsCmdText = lsCmdText & "USING (VALUES('" & psWorkspaceID & "', '" & psDatasetID & "', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT DATE)) "
lsCmdText = lsCmdText & "AS src (workspace_id, dataset_id, catalog_name, catalog_description, roles, date_modified, compatability_level, catalog_type, catalog_version, date_queried, currently_used, popularity, weightedpopularity, updateddate) "
lsCmdText = lsCmdText & "ON src.workspace_id = dest.workspace_id AND src.dataset_id = dest.dataset_id AND src.catalog_name = dest.catalog_name "
lsCmdText = lsCmdText & "WHEN MATCHED THEN UPDATE SET dest.workspace_id = src.workspace_id, dest.dataset_id = src.dataset_id, dest.catalog_name = src.catalog_name, dest.catalog_description = src.catalog_description, dest.roles = src.roles, dest.date_modified = src.date_modified, dest.compatability_level = src.compatability_level, dest.catalog_type = src.catalog_type, dest.catalog_version = src.catalog_version, dest.date_queried = src.date_queried, dest.currently_used = src.currently_used, dest.popularity = src.popularity, dest.weightedpopularity = src.weightedpopularity, dest.updateddate = src.updateddate "
lsCmdText = lsCmdText & "WHEN NOT MATCHED THEN INSERT (workspace_id, dataset_id, catalog_name, catalog_description, roles, date_modified, compatability_level, catalog_type, catalog_version, date_queried, currently_used, popularity, weightedpopularity, updateddate) VALUES (src.workspace_id, src.dataset_id, src.catalog_name, src.catalog_description, src.roles, src.date_modified, src.compatability_level, src.catalog_type, src.catalog_version, src.date_queried, src.currently_used, src.popularity, src.weightedpopularity, src.updateddate) "

loCmd.CommandType = adCmdText
loCmd.CommandText = lsCmdText

loCmd.Parameters.Append loCmd.CreateParameter("catalog_name", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("catalog_description", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("roles", adVarChar, adParamInput, 255, "")
loCmd.Parameters.Append loCmd.CreateParameter("date_modified", adDBTimeStamp)
loCmd.Parameters.Append loCmd.CreateParameter("compatability_level", adSmallInt, , 0)
loCmd.Parameters.Append loCmd.CreateParameter("catalog_type", adSmallInt, , 0)
loCmd.Parameters.Append loCmd.CreateParameter("catalog_version", adSmallInt, , 0)
loCmd.Parameters.Append loCmd.CreateParameter("date_queried", adDBTimeStamp)
loCmd.Parameters.Append loCmd.CreateParameter("currently_used", adSmallInt, , 0)
loCmd.Parameters.Append loCmd.CreateParameter("popularity", adSingle, , 0)
loCmd.Parameters.Append loCmd.CreateParameter("weightedpopularity", adDouble, , 0)

'get PBI recordset - table

lsPBISQL = "SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS "
Set loPBIRS = loPBIConn.Execute(lsPBISQL)

'For each row
Do While Not loPBIRS.EOF

'set parameters
loCmd.Parameters("catalog_name").Value = loPBIRS("CATALOG_NAME").Value & ""
loCmd.Parameters("catalog_description").Value = loPBIRS("DESCRIPTION").Value & ""
loCmd.Parameters("roles").Value = loPBIRS("ROLES").Value & ""
loCmd.Parameters("date_modified").Value = loPBIRS("DATE_MODIFIED").Value
loCmd.Parameters("compatability_level").Value = "0" & loPBIRS("COMPATIBILITY_LEVEL").Value
loCmd.Parameters("catalog_type").Value = "0" & loPBIRS("TYPE").Value
loCmd.Parameters("catalog_version").Value = "0" & loPBIRS("VERSION").Value
loCmd.Parameters("date_queried").Value = loPBIRS("DATE_QUERIED").Value
loCmd.Parameters("currently_used").Value = loPBIRS("CURRENTLY_USED").Value
loCmd.Parameters("popularity").Value = "0" & loPBIRS("POPULARITY").Value
loCmd.Parameters("weightedpopularity").Value = "0" & loPBIRS("WEIGHTEDPOPULARITY").Value

'execute insert
loCmd.Execute


'idle
DoEvents

'fetch next record
loPBIRS.MoveNext


Loop 'Do While Not loPBIRS.EOF

'close connections
loPBIRS.Close
loConn.Close

End If

'close connection
loPBIConn.Close

Exit Sub

Err:

'prevent errors
If Err.Number = 91 Then
Exit Sub
End If

Debug.Print "GetDatasetTables Error: " & Err.Number & " " & Err.Description
WriteLog "GetDatasetTables Error: " & Err.Number & " " & Err.Description, "Dataset: " & psDatasetID
Resume Next

End Sub


Thanks in advance.

Continue reading...