Seaching Cognos 10 Content Store for an XML string

We recently migrated our knowledgebase from Collab to sharepoint, which meant all reports that had links to collab needed to be identified and edited.

This query proved helpful – it searches the content store for occurrences of the old string, ‘collaboration.au’, and displays the report name and location.

select
 names.name,
 substring(
 specs.spec ,
 charindex('', specs.spec) + 11,
 charindex('', specs.spec) - charindex('', specs.spec))
 from
 CMOBJNAMES names,
 CMOBJPROPS7 specs

where
 names.CMID = specs.CMID
 -- report name
 --and names.name LIKE '%REPORT NAME%'
 -- XML contains
 and specs.spec like '%collaboration.au.%'

The query does take a while to run though – almost 40 minutes against our beast-mode content store…

Tags:

0 Comments

You can be the first one to leave a comment.

Leave a Comment