Skip to content
Advertisement

VBA ADODB error (80040e14) when trying to retrieve data from SQL Server

I am trying to connect to SQL Server from Excel VBA using the following code

    Sub query()
    
    Dim obj_Connection As New ADODB.Connection
    Dim obj_RecordSet As New ADODB.Recordset
    Dim str_ConnString As String
    Dim str_CellTo As String
    Dim str_QuerySQL As String
    str_QuerySQL = "R1"
    str_QuerySQL.Text
    str_CellTo = "A1"
    

    str_ConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=GIMDB;Data Source=UDPEXTDB03"
    
    obj_Connection.CommandTimeout = 0

    obj_Connection.Open str_ConnString
    obj_RecordSet.Source = obj_Connection
    obj_RecordSet.Open str_QuerySQL, obj_Connection

    Range(str_CellTo).CopyFromRecordset obj_RecordSet
    
    obj_RecordSet.Close
    obj_Connection.Close
    Set obj_RecordSet = Nothing
    Set obj_Connection = Nothing
   
    End Sub

and with some specific simple queries it actually works like a charm.

My problem shows up whenever I try to run the query right below to which I get the following error:

‘-2147217900 (80040e14)’ An expression of non-boolean type specified in a context where a condition is expected, near ‘ir’.

DECLARE @start_ts datetime, @end_ts datetime
SET @start_ts = '2021-07-01 00:00:00.000'
SET @end_ts = '2021-07-01 23:59:59.000'
SELECT
MSF.TENANT_KEY
AS 'ACD',
msf.start_ts_time AS 'DIA',
case
when IT.INTERACTION_TYPE_CODE <> 'INBOUND' AND IRF.CUSTOMER_TALK_DURATION > 0 then 1
when irf.CUSTOMER_HANDLE_COUNT > 0 and td.TECHNICAL_RESULT_CODE = 'DIVERTED'
and td.RESULT_REASON_CODE in ('ANSWEREDBYAGENT' , 'ANSWEREDBYOTHER') then 2
when mt.IS_ONLINE = 1 and irf.CUSTOMER_HANDLE_COUNT = 0 and td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE = 'ANSWEREDBYAGENT'
and irf.CONS_RCV_TALK_COUNT + irf.TALK_COUNT + irf.POST_CONS_XFER_TALK_COUNT + irf.CONF_JOIN_TALK_COUNT > 0 then 2
when mt.IS_ONLINE = 0 and it.INTERACTION_SUBTYPE_CODE <> 'INTERNALCOLLABORATIONREPLY' and irf.CONS_RCV_TALK_COUNT > 0 then 2
when td.RESOURCE_ROLE_CODE <> 'RECEIVEDCONSULT' and it.INTERACTION_SUBTYPE_CODE not in
('INTERNALCOLLABORATIONINVITE' , 'INTERNALCOLLABORATIONREPLY' , 'INBOUNDCOLLABORATIONREPLY' , 'OUTBOUNDCOLLABORATIONINVITE')
and td.TECHNICAL_RESULT_CODE in ('ABANDONED' , 'CUSTOMERABANDONED') then 3
when td.RESOURCE_ROLE_CODE <> 'RECEIVEDCONSULT' and it.INTERACTION_SUBTYPE_CODE
not in ('INTERNALCOLLABORATIONINVITE' , 'INTERNALCOLLABORATIONREPLY' , 'INBOUNDCOLLABORATIONREPLY' , 'OUTBOUNDCOLLABORATIONINVITE')
and td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE = 'ABANDONEDWHILERINGING'
and coalesce(irf.CUSTOMER_RING_COUNT, 1) > 0 then 3
when (td.RESOURCE_ROLE_CODE = 'RECEIVEDCONSULT' or (coalesce(irf.CUSTOMER_HANDLE_COUNT, 1) + coalesce(irf.CUSTOMER_RING_COUNT, 1) = 0
and it.INTERACTION_SUBTYPE_CODE <> 'INTERNALCOLLABORATIONREPLY')) and td.TECHNICAL_RESULT_CODE = 'ABANDONED' then 3
when td.RESOURCE_ROLE_CODE <> 'RECEIVEDCONSULT' and it.INTERACTION_SUBTYPE_CODE not in
('INTERNALCOLLABORATIONINVITE' , 'INTERNALCOLLABORATIONREPLY' , 'INBOUNDCOLLABORATIONREPLY' , 'OUTBOUNDCOLLABORATIONINVITE')
and td.TECHNICAL_RESULT_CODE in ('CLEARED' , 'ABNORMALSTOP') then 4
when (td.RESOURCE_ROLE_CODE = 'RECEIVEDCONSULT' or (coalesce(irf.CUSTOMER_HANDLE_COUNT, 1) + coalesce(irf.CUSTOMER_RING_COUNT, 1) = 0 and it.INTERACTION_SUBTYPE_CODE <> 'INTERNALCOLLABORATIONREPLY')) and td.TECHNICAL_RESULT_CODE = 'CLEARED' then 4
when td.RESOURCE_ROLE_CODE <> 'RECEIVEDCONSULT' and it.INTERACTION_SUBTYPE_CODE not in ('INTERNALCOLLABORATIONINVITE' , 'INTERNALCOLLABORATIONREPLY' , 'INBOUNDCOLLABORATIONREPLY' , 'OUTBOUNDCOLLABORATIONINVITE')
and coalesce(irf.CUSTOMER_HANDLE_COUNT, 1) + coalesce(irf.CUSTOMER_RING_COUNT, 1) > 0 and td.TECHNICAL_RESULT_CODE = 'DIVERTED'
and td.RESULT_REASON_CODE not in ('REDIRECTED' , 'REJECTED' , 'REVOKED' , 'ROUTEONNOANSWER' , 'ANSWEREDBYAGENT'
, 'ANSWEREDBYOTHER' , 'ABANDONEDWHILERINGING' , 'ABNORMALSTOPWHILERINGING') then 7
when (td.RESOURCE_ROLE_CODE = 'RECEIVEDCONSULT' or (coalesce(irf.CUSTOMER_HANDLE_COUNT, 1) + coalesce(irf.CUSTOMER_RING_COUNT, 1) = 0 and it.INTERACTION_SUBTYPE_CODE <> 'INTERNALCOLLABORATIONREPLY')) and td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE not in ('REDIRECTED' , 'REJECTED' , 'REVOKED' , 'ROUTEONNOANSWER' , 'ANSWEREDBYAGENT' , 'ANSWEREDBYOTHER' , 'ABANDONEDWHILERINGING') then 7
else 8 end AS 'DISPOSITION',
MSF.START_TS_TIME AS 'INICIO',
IFG.END_TS_TIME
AS 'FIM',
MSF.INTERACTION_ID AS 'CALLID',
R.RESOURCE_NAME
AS 'VQ',
CASE WHEN R2.EMPLOYEE_ID IS NULL THEN R2.RESOURCE_NAME ELSE R2.EMPLOYEE_ID
END
AS 'ID_OPERADOR',
msf.MEDIATION_DURATION  AS 'TEMPO_EM_ESPERA',
ISNULL(IRF.STOP_ACTION, 0) AS 'DESCONECTADO',
(case when irf.INTERACTION_RESOURCE_ID is null then 0 when td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE IN ('ANSWEREDBYAGENT', 'ANSWEREDBYOTHER') and irf.CUSTOMER_HANDLE_COUNT > 0 then irf.CUSTOMER_TALK_DURATION else 0 end) +
(case when irf.INTERACTION_RESOURCE_ID is null then 0 when mt.IS_ONLINE = 1 and irf.CUSTOMER_HANDLE_COUNT = 0 and td.TECHNICAL_RESULT_CODE = 'DIVERTED'
and td.RESULT_REASON_CODE = 'ANSWEREDBYAGENT' then irf.CONS_RCV_TALK_DURATION + irf.TALK_DURATION + irf.POST_CONS_XFER_TALK_DURATION + irf.CONF_JOIN_TALK_DURATION when mt.IS_ONLINE = 0 then irf.CONS_RCV_TALK_DURATION else 0 end) +
(case when irf.INTERACTION_RESOURCE_ID is null then 0 when irf.CUSTOMER_HANDLE_COUNT = 0 and td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE IN ('ANSWEREDBYAGENT', 'ANSWEREDBYOTHER') then irf.CONS_RCV_HOLD_DURATION + irf.HOLD_DURATION + irf.POST_CONS_XFER_HOLD_DURATION + irf.CONF_JOIN_HOLD_DURATION else 0 end) +
(case when irf.INTERACTION_RESOURCE_ID is null then 0 when td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE IN ('ANSWEREDBYAGENT', 'ANSWEREDBYOTHER') and irf.CUSTOMER_HANDLE_COUNT = 0 then irf.CONS_RCV_ACW_DURATION + irf.AFTER_CALL_WORK_DURATION else 0 end) +
(case when irf.INTERACTION_RESOURCE_ID is null then 0 when irf.CUSTOMER_HANDLE_COUNT > 0 and irf.CONS_RCV_TALK_COUNT > 0 and td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE IN ('ANSWEREDBYAGENT', 'ANSWEREDBYOTHER') then irf.CONS_RCV_TALK_DURATION else 0 end) +
(case when irf.INTERACTION_RESOURCE_ID is null then 0 when irf.CUSTOMER_HANDLE_COUNT > 0 and irf.CONS_RCV_HOLD_COUNT > 0 and td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE IN ('ANSWEREDBYAGENT', 'ANSWEREDBYOTHER') then irf.CONS_RCV_HOLD_DURATION else 0 end) +
(case when irf.INTERACTION_RESOURCE_ID is null then 0 when td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE IN ('ANSWEREDBYAGENT', 'ANSWEREDBYOTHER') and irf.CUSTOMER_HANDLE_COUNT > 0 then irf.CONS_RCV_ACW_DURATION else 0 end) +
(case when irf.INTERACTION_RESOURCE_ID is null then 0 when td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE IN ('ANSWEREDBYAGENT', 'ANSWEREDBYOTHER') and irf.CUSTOMER_HANDLE_COUNT > 0 then irf.CUSTOMER_HOLD_DURATION else 0 end) +
(case when irf.INTERACTION_RESOURCE_ID is null then 0 when td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE IN ('ANSWEREDBYAGENT', 'ANSWEREDBYOTHER') then irf.CUSTOMER_ACW_DURATION else 0 end) AS 'TEMPO_FALADO - AHT',
(case when irf.INTERACTION_RESOURCE_ID is null then 0 when td.TECHNICAL_RESULT_CODE = 'DIVERTED' and td.RESULT_REASON_CODE IN ('ANSWEREDBYAGENT', 'ANSWEREDBYOTHER') and irf.CUSTOMER_HANDLE_COUNT > 0 then irf.CUSTOMER_TALK_DURATION else 0 end) AS 'TALK - TMA',
case when irf.transfer_init_agent = 1
and (irf.INTERACTION_RESOURCE_ID = irf2.RECEIVED_FROM_IXN_RESOURCE_ID and irf2.RESOURCE_KEY in (select r_.RESOURCE_KEY from RESOURCE_ r_ where r_.RESOURCE_TYPE_CODE in ('AGENT' , 'QUEUE' , 'OTHER', 'ROUTINGPOINT')))
AND irf2.TECHNICAL_DESCRIPTOR_KEY IN (SELECT TECHNICAL_DESCRIPTOR_KEY FROM TECHNICAL_DESCRIPTOR WHERE RESOURCE_ROLE_CODE = 'RECEIVEDTRANSFER')
AND IRF2.RECEIVED_FROM_IXN_RESOURCE_ID is not null
then 1 else 0 end AS 'TRANSFERIDA',
irf.INTERACTION_RESOURCE_ID as 'ID PERNA'
from MEDIATION_SEGMENT_FACT_GI2 msf with(NOLOCK)
INNER JOIN INTERACTION_FACT_GI2 IFG with(nolock) ON IFG.INTERACTION_ID = msf.INTERACTION_ID
inner join TECHNICAL_DESCRIPTOR td with(nolock) ON (msf.TECHNICAL_DESCRIPTOR_KEY = td.TECHNICAL_DESCRIPTOR_KEY)
inner join MEDIA_TYPE mt with(nolock) ON (mt.MEDIA_TYPE_KEY=msf.MEDIA_TYPE_KEY)
inner join INTERACTION_TYPE it with(nolock) ON (it.INTERACTION_TYPE_KEY = msf.INTERACTION_TYPE_KEY)
inner join RESOURCE_ r with(nolock) ON (r.RESOURCE_KEY = msf.RESOURCE_KEY and r.RESOURCE_SUBTYPE = 'VirtualQueue')
left outer join INTERACTION_RESOURCE_FACT_GI2  irf with(NOLOCK) ON (msf.TARGET_IXN_RESOURCE_ID = irf.INTERACTION_RESOURCE_ID and irf.RESOURCE_KEY in (select r_.RESOURCE_KEY from RESOURCE_ r_ where r_.RESOURCE_TYPE_CODE in ('AGENT' , 'QUEUE' ,'OTHER', 'ROUTINGPOINT')))
left outer join RESOURCE_ r2 with(nolock) ON (irf.RESOURCE_KEY = r2.RESOURCE_KEY)
left outer join TECHNICAL_DESCRIPTOR td2 with(nolock) ON (irf.TECHNICAL_DESCRIPTOR_KEY = td2.TECHNICAL_DESCRIPTOR_KEY)
left outer join IRF_USER_DATA_KEYS irfud with(NOLOCK) ON (msf.MEDIATION_SEGMENT_ID = irfud.INTERACTION_RESOURCE_ID)
left outer join INTERACTION_RESOURCE_FACT irf2 with(NOLOCK) ON (irf.INTERACTION_RESOURCE_ID = irf2.RECEIVED_FROM_IXN_RESOURCE_ID and irf2.RESOURCE_KEY in (select r_.RESOURCE_KEY from RESOURCE_ r_ where r_.RESOURCE_TYPE_CODE in ('AGENT' , 'QUEUE','OTHER' , 'ROUTINGPOINT'))) AND irf2.TECHNICAL_DESCRIPTOR_KEY IN (SELECT TECHNICAL_DESCRIPTOR_KEY FROM TECHNICAL_DESCRIPTOR WHERE RESOURCE_ROLE_CODE = 'RECEIVEDTRANSFER') AND IRF2.RECEIVED_FROM_IXN_RESOURCE_ID is not null
left outer join resource_ rq with(nolock) on rq.RESOURCE_KEY = irf2.LAST_VQUEUE_RESOURCE_KEY
left outer join resource_ rp with(nolock) on rp.RESOURCE_KEY = irf2.LAST_RP_RESOURCE_KEY
left outer join resource_ rat with(nolock) on rat.RESOURCE_KEY = irf2.RESOURCE_KEY
left outer join resource_ rp1 with(nolock) on rp1.RESOURCE_KEY = irf.LAST_RP_RESOURCE_KEY
where
msf.start_ts_time BETWEEN @start_ts AND @end_ts AND(1=1) and ((1=1)
and (not (it.INTERACTION_SUBTYPE_CODE in ('INTERNALCOLLABORATIONINVITE' , 'INTERNALCOLLABORATIONREPLY' , 'INBOUNDCOLLABORATIONREPLY' , 'OUTBOUNDCOLLABORATIONINVITE') and irf.INTERACTION_RESOURCE_ID is null) and not (it.INTERACTION_SUBTYPE_CODE in ('INTERNALCOLLABORATIONREPLY' , 'INBOUNDCOLLABORATIONREPLY') and irf.CONS_RCV_TALK_DURATION = 0)))
 AND MT.[MEDIA_NAME] = 'CHAT'
 AND R.RESOURCE_NAME IN ('VQ_WRPL_TELEVENDAS_BRASTEMP','VQ_WRPL_TELEVENDAS_COMPRA_CERTA','VQ_WRPL_TELEVENDAS_CONSUL','VQ_WRPL_TELEVENDAS_KITCHENAID')
 --AND MSF.INTERACTION_ID = '947686' -- PESQUISAR CALLID
order by 2 desc

I’m no DBA or anything like that, the query above was being used on SQL Server to extract data and then copy it into Excel, so all I wanted to do was trying to fetch that with ADODB so I would kill the manual process.

I’ve been searching for related problems on the web and I actually came up understanding that the problem is in the query itself. Thing is, I can’t understand how is this possible since the above query actually works when executed manually on SQL Server.

I’d be really grateful if anybody could enlighten me with the solution to this problem.

Thanks.

Advertisement

Answer

Finally found the solution!

I followed Dale K‘s hint about stripping down the code to the minimum required and came up the the conclusion that it actually was a problem about maximum query length topping higher than 8000 characters (just like AlwaysLearning suggested).

The problem happened whenever i added .Text at the very end of the query variable, causing this 8k characters limit and an error output while executing the VBA.

I solved it by calling the query variable and adding .Value at the very end of it. (str_QuerySQL.Value) and now i managed to run even queries that have 15k+ characters.

Thank you all for the hints.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement