Find reports in your COGNOS BI with old URL image paths or package names through content store

At my company we have a lot of reports with old image path URLs from old templates that were used in COGNOS BI 7.0 pointing to an old location on IIS DNS Alias that we wanted to decom. We have moved the images to a new location and updated the COGNOS 10.2.1 templates. But had no way of updating the existing reports. So I wrote a  little SQL select and update statements to fix the issue in the COGNOS content store. I hope this help others admins and has a lot of other useful practices. But a word of cation the content store need to be backed up and queries need to be tested before used. I always would copy the report in COGNOS and narrow the where clause down to the one report and test it before updating the bulks of the reports.

--Find reports in COGNOS BI 10.2.1 with Old URL Image or Package Name

--COGNOS BI 10.2.1

DECLARE @sql VARCHAR(50)

--Package Name

--SET @sql = '''Premier'''

--Image URL

SET @sql = '%http://essdev/cognos8/samples/images/UCB.gif%'

SELECT  r.[CMID] ,

c.[NAME] ,

[EXECERROR] ,

[CONFIGURATION] ,

[PORTALPREFS] ,

[CCREDENTIALS] ,

[CMODEL] ,

[SRC] ,

[SPEC] ,

[STATE] ,

[GOVERNORS] ,

[NAMESPFORMAT]

--,'%/content/package[@name='+@sql+']%'

FROM    [Cognos_10].[dbo].[CMOBJPROPS7] r

INNER JOIN [Cognos_10].[dbo].[CMOBJNAMES] c

ON r.[CMID] = c.[CMID]

INNER JOIN [Cognos_10].[dbo].[CMOBJECTS] cm

ON c.[CMID] = cm.[CMID]

INNER JOIN [Cognos_10].[dbo].CMCLASSES T3

ON T3.CLASSID = cm.CLASSID

WHERE   --Package Name

--[SPEC] LIKE '%/content/package[@name='+@sql+']%'

[SPEC] LIKE @sql

AND T3.CLASSID IN ( 10, 76 )

AND c.NAME = 'Mailing'

----------------------------------

-- UPDATE to Replace Company Image URL template

UPDATE r

--cast(replace(cast(myntext as nvarchar(max)),'find','replace') as ntext)

SET [SPEC] = cast(Replace(cast([SPEC] as nvarchar(max)),'http://essdev/cognos8/samples/images/UCB.gif','../samples/images/UCB.gif') as ntext)

FROM [Cognos_10].[dbo].[CMOBJPROPS7] r

INNER JOIN [Cognos_10].[dbo].[CMOBJNAMES] c

ON r.[CMID] = c.[CMID]

INNER JOIN [Cognos_10].[dbo].[CMOBJECTS] cm

ON c.[CMID] = cm.[CMID]

INNER JOIN [Cognos_10].[dbo].CMCLASSES T3

ON T3.CLASSID = cm.CLASSID

WHERE [SPEC] LIKE '%http://essdev/cognos8/samples/images/UCB.gif%'

AND T3.CLASSID IN (10,76)

--Test one report at a before you do the bulk.

AND c.NAME = 'Top_Deposit_Customers'

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s