Heterogeneous Query no longer returns data

Toad Data Point
1

I have the following code that links to 2 different MS SQL servers and the results are imported into an Oracle 11gR2 database.

SELECT WorkgroupDVAudit.Workgroup_Name,
AAS.User_Id,
AAS.LoginTime,
AAS.NotReadyTime,
AAS.IdleTime,
MAS.WrapTime,
MAS.PreviewTime,
MAS.ActiveTime,
MAS.HoldTime,
MAS.InboundCalls,
MAS.OutboundCalls,
MAS.ManualCalls,
MAS.TransferredCalls,
MAS.WrapCalls,
AAS.LoginDate
FROM N2UIPRIDATA01\UIP_CONFIG (CcproQuery), config_epro.config_epro.dbo.WorkgroupDVAudit WorkgroupDVAudit
INNER JOIN
(SELECT lower(AgentActivitySummary.User_Id) AS User_Id,
AgentActivitySummary.WorkGroup_Id,
SUM(
AgentActivitySummary.TotalLoginTime

  • ISNULL(TotalGapTime, 0))
    AS LoginTime,
    SUM(TotalNotreadyTime) AS NotReadyTime,
    SUM(TotalIdleTime) AS IdleTime,
    CAST(CONVERT(varchar,AgentActivitySummary.BeginTimePeriodDt,101) AS Date) AS LoginDate
    FROM N2UIREPDATA01\UIP_REPORTING (CcproQuery), summary_epro.summary_epro.dbo.AgentActivitySummary AgentActivitySummary
    WHERE AgentActivitySummary.LoginDt = ‘’ /Yesterday/
    AND AgentActivitySummary.Service_Id = 0
    GROUP BY AgentActivitySummary.WorkGroup_Id,
    AgentActivitySummary.User_Id,
    CONVERT(varchar,AgentActivitySummary.BeginTimePeriodDt,101)) AAS

ON (AAS.WorkGroup_Id = WorkgroupDVAudit.WorkGroup_Id)
LEFT OUTER JOIN
(SELECT lower(MediaAgentSummary.User_Id) AS User_Id,
MediaAgentSummary.WorkGroup_Id,
SUM(CASE WHEN CallTypeId IN (1,25) THEN TotalAgentCalls ELSE 0 END) AS InboundCalls,
SUM(CASE WHEN CallTypeId NOT IN (1,25) THEN TotalAgentCalls ELSE 0 END) AS OutboundCalls,
SUM(CASE WHEN CallTypeId IN (9) THEN TotalAgentCalls ELSE 0 END) AS ManualCalls,
SUM(CASE WHEN CallActionId IN (3,18,19,35) THEN TotalAgentCalls ELSE 0 END) AS TransferredCalls,
SUM(WrapActiveCount) As WrapCalls,
SUM(WrapTime) AS WrapTime,
SUM(PreviewTime) AS PreviewTime,
SUM(ActiveTime) AS ActiveTime,
SUM(HoldTime) AS HoldTime,
CAST(CONVERT(varchar,MediaAgentSummary.BeginTimePeriodDt,101) AS Date) AS CallDate
FROM N2UIREPDATA01\UIP_REPORTING (CcproQuery), summary_epro.summary_epro.dbo.MediaAgentSummary MediaAgentSummary
WHERE MediaAgentSummary.BeginTimePeriodDt = ‘’ /Yesterday/
GROUP BY MediaAgentSummary.WorkGroup_Id, MediaAgentSummary.User_Id, CONVERT(varchar,MediaAgentSummary.BeginTimePeriodDt,101)) MAS
ON ( AAS.User_Id = MAS.User_Id
AND AAS.WorkGroup_Id = MAS.WorkGroup_Id AND AAS.LoginDate = MAS.CallDate)

ORDER BY Workgroup_Name, User_Id, LoginDate

Up until 4/1/2014 this worked perfectly in an automated task. Now it returns no data at all.

If I take the portions of code and run them on a direct connect to the database in question, I get results.

On a possibly related note, the automation runs on a box with TOAD DA Pro 3.2 – when I create a heterogeneous query editor on that machine, I get a SAXParseException

2

You may need to clear out clear out your local storage, which is also where you keep your mappings. If you have anything important in local storage, do not procede and let me know. Otherwise, go to options->Local Storage->General

LocalStorage.png
LocalStorage.png860×532 35.2 KB

Find this folder and rename it (If something goes wrong, we can rename it back to its former name). Then try reexecuting and let me know the result.

3

It appears the SAXParseException on the server was the major issue in the automation not working. However as I also noted this didn’t work on my local machine running TOAD DA Pro 3.5 beta. I tried Ryan fix above and I still get no results on my local machine (it works on the 3.2 Pro machine). It appears to be something related to the DateField = ’ ’ /Yesterday/ – if I use actual dates I get data back.

4

I created an issue so we can look into this, QAT-2422

5

I can’t explain why the SQL would return data in the past and now stop returning data. If you are trying to filter by null dates you need to change to user “IS NULL”. That will return the correct info. Does this resolve your issue?

6

I “upgraded” the beta to the latest build 3.5.0.2904 and I no longer have this issue.