Advanced Search


No announcement yet.

SQL - Find all annotations

  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL - Find all annotations

    One or more of the below resolutions require knowledge of T-SQL and/or SQL Management Studio; consult a qualified SQL administrator for assistance with these procedures. Back up any targeted SQL databases before executing any SQL statements. Assisting with these procedures is outside the scope of normal Ipro application support.

    Here is an example of what the query will return: Capture.PNG

    EDIT 2019/05/02 - The query was updated to include what the redaction name is, if the 'annotation type' is 'redaction', as well as including the imagekey (page), if applicable. 2019-05-02 11_05_13.png

    EDIT 2019/06/16 - Cleaned up the query by separating the annotation type from the text. The new column contains any related annotation information. Capture99.PNG

    If you do not know the Eclipse case database name, run the below query first against your ADD/Nucleus database to get the case and database name.
    use --ADD/Nucleus database
    select [Name], DatabaseName from
    where ProductId=3
    order by [Name] asc
    use --Eclipse Case Database
    ,ISNULL(DP.ImageKey,'') as 'ImageKey (Page)'
           when (DA.AnnotationType is null AND DA.RedactionCatId is not null) then 'REDACTION'
           when (DA.AnnotationType = 0 AND DA.RedactionCatId is null) then 'HIGHLIGHT'
           when  DA.AnnotationType = 1 then 'STICKY NOTE'
           when  DA.AnnotationType = 2 then 'EMBEDDED TEXT'
           when  DA.AnnotationType = 3 then 'MARKUP'
        end as "Annotation Type"
           when (DA.AnnotationType is null AND DA.RedactionCatId is not null) then RC.Label
           when  DA.AnnotationType = 1 then DA.AnnotationText
           when  DA.AnnotationType = 2 then DA.AnnotationText
           else ''
        end as "Annotation Text"
    from DocumentAnnotations DA
        left join Documents D
        on D.DocId=DA.DocId
        left join DocumentPages DP
        on DP.PageId = DA.PageId
        left join dbo.RedactionCategories RC
        on RC.RedactionCatId=DA.RedactionCatId
    ORDER BY D.DocumentKey asc
    Last edited by Andrew Coody; 06-13-2019, 06:37 AM.
    ​​​​​​​Andrew Coody
    Technical Support Engineer

  • #2
    Nice work Andrew.