Does anyone have any information on running custom reports and getting either unknown exception error or invalid query responses for a custom report that worked once?
I randomly get these messages in 2 of my environments even though I have been able to run this report on multiple Operations Center databases ( I have 5 different ones). I have also run this report once successfully on the database where I randomly get these errors. My second database where i get these errors, the report has never run successfully and I have over 5 million tables here.
This is the report I am trying to run:
SELECT B.friendlyName as 'Master Server Name',
( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Number of days since Last successful'
from DBA.domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyType = A.policyType AND
CKP.scheduleType = A.scheduleType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ),
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
A.scheduleName as 'Schedule Name',
UtcBigIntToNomTime(A.endTime) as 'Date of failure',
A.statusCode as 'Status Code',
( select UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
from DBA.domain_JobArchive BKP
where BKP.masterServerId = A.masterServerId AND
BKP.clientId = A.clientId AND
BKP.policyId = A.policyId AND
BKP.policyType = A.policyType AND
BKP.scheduleType = A.scheduleType AND
( BKP.statusCode = 0 OR BKP.statusCode = 1 ) )
FROM "domain_JobArchive" A , "domain_masterserver" B
WHERE
A.masterServerId = B.id AND
DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 120
AND A.statusCode > 1 AND
( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Number of days since Last successful'
from DBA.domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyType = A.policyType AND
CKP.scheduleType = A.scheduleType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ) > 0
ORDER BY B.friendlyName , 'Number of days since Last successful' ;
