0

I want to find only failed user details, but the below query is giving duplicate records. I am not able to find a proper solution.

To find failed job details I'm using the below query:

select * from executionlog e
join catalog c on e.reportid = c.itemid
where c.name like '%reportname%'
and timestart>= '2013-04-15 09:00:00.000' 
and status <> 'rsSuccess'

However, the above query is giving duplicate values for a particular report.

How can I get unique details?

Note: we cannot apply distinct or group by because the table contains columns of ntext and image data types

Jeroen
  • 60,696
  • 40
  • 206
  • 339
Prasad
  • 65
  • 4
  • 13

1 Answers1

2

If you want only "failed user details" then don't select the ntext or image columns at all. That way you can do a DISTINCT normally:

SELECT  DISTINCT
      --Parameters,
      --Content,
      --Property,
      --Parameter,
      InstanceName, ReportID, UserName, RequestType, Format,  TimeStart, TimeEnd, 
      TimeDataRetrieval, TimeProcessing, TimeRendering, Source, Status, ByteCount, 
      [RowCount], ItemID, Path, Name, ParentID, Type,  Intermediate, SnapshotDataID, 
      LinkSourceID,  Description, Hidden, CreatedByID, CreationDate, ModifiedByID, 
      ModifiedDate, MimeType, SnapshotLimit, PolicyID, PolicyRoot, ExecutionFlag, 
      ExecutionTime
FROM  executionlog e
JOIN  catalog c ON e.reportid = c.itemid
WHERE c.name LIKE '%reportname%'
      AND timestart>= '2013-04-15 09:00:00.000' 
      AND status <> 'rsSuccess'

You can even trim many more columns. Note that doing SELECT * is a bad practice for many cases anyway.

If you're interested in the corresponding ntext and/or image values you can always join the catalog against the above subquery again.

Community
  • 1
  • 1
Jeroen
  • 60,696
  • 40
  • 206
  • 339
  • Hi, thank you for reply , you cannot apply distinct due to image and ntext data types. if you keep status<>'rssucess' you will get duplicates.because many things are there under status other than rssucess i.e rsprocessingaborted , rsdenied,rsdatabaseservererror etc.. sothat we should get duplicates for same parameters. – Prasad May 05 '13 at 06:33
  • I'm sorry, but your comment makes absolutely no sense to me (maybe a language barrier?). – Jeroen May 05 '13 at 14:53