[SQL] 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.

Continue reading...