http://bbadmintips.blogspot.com/2011/08/can-this-be-used-to-identify-broken.html Re-posted here for my own archives. I have been trying to identify legacy Blackboard content that is broken. One cause of this, as described in the KB article “Course copies retain content links to the source course causing broken content links“, is that an item has been copied to a new course site, but continues to point back to the original source. To the instructor and other users who have permissions to see both the source and destination sites the link to content will appear to work normally. However, students who are not enrolled in both sites will get an error. I would like to identify this situation before students encounter the problem so that we can proactively correct it. I would also like to eradicate this issue from legacy content once and for all as Blackboard claims to have resolved the underlying causes that introduce new cases… but we will continue to suffer until we resolve all of the existing cases. The KB article above links to a Blackboard provided repair utility to correct the broken content. I have not yet been able to resolve the issues I have seen with the utility. NOTE: I borrowed heavily from a post Jon Tyler made to the BBADMIN-L list (link) and full credit for the idea belongs to him. So far, the SQL below seems to identify broken content on Oracle… although it is probably flawed in some terrible way. Please feel free to tell me what I missed. SELECT cm.course_id, csfu.full_path, f.pk1 AS id FROM bb_bb60.files f, bb_bb60.course_contents_files ccf, bb_bb60.COURSE_CONTENTS cc, cms_doc.XYF_URLS csfu, bb_bb60.course_main cm WHERE f.pk1 = ccf.files_pk1 AND cc.pk1 = ccf.COURSE_CONTENTS_pk1 AND cc.crsmain_pk1 = cm.pk1 AND UPPER(csfu.full_path) NOT LIKE UPPER(‘%’ || cm.course_id || ‘%’ ) AND SUBSTR(f.file_name, 6, LENGTH(f.file_name)-7) = csfu.file_id AND f.storage_type=’CS’ — Use one of the two methods below to limit the scope is — recommended — Running without one of these (or some other limiting criteria) — may take a looong time and is untested AND cm.course_id like ‘insert criteria here‘ –AND cm.course_id IN (‘insert course ID(s) here‘) ORDER BY cm.course_id]]>
