1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussão em 'Outras Linguagens' iniciado por Stack, Julho 28, 2021.

  1. Stack

    Stack Membro Participativo

    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...

Compartilhe esta Página