EAM Cognos Report Refresh (semi automated - still need to work out how to get around manually pasting this into the destination environment - current type conversion issue with running the cte over linked server/possible MS bug)?

EAM_Environment_Refresh_Reports.sql

--Still a work in progress to make it fully SQL, but here’s a way to grab all of the XMLs and names from production – so it’s only the pasting side left to automate.

use [eam-rep] --eamProduction\SQL05
go
;with pathBuilderCte as --based on info from: http://cognospaul.com/2012/03/04/8-2-export-all-report-xmls-to-file-system/
 (

	select o.CMID id, cast('root' as nvarchar(max)) namePath
	from cmobjnames n
	inner join cmobjects o on o.cmid = n.cmid 
	where n.ISDEFAULT = 1
	and o.CMID = 0
	
	union all
	
	select o.CMID, p.namePath + '\' + cast(n.NAME as nvarchar(max))
	from cmobjnames n
	inner join cmobjects o on o.cmid = n.cmid 
	inner join pathBuilderCte p on p.id = o.PCMID
	where n.ISDEFAULT = 1
	and o.CMID > 0
 
 )
SELECT cte.namePath, n.name, CAST(SPEC as xml) SPEC
FROM cmobjnames n
inner join cmobjects o on o.cmid = n.cmid
inner join cmobjprops7 c on c.cmid = o.cmid
inner join cmclasses cls on cls.classid = o.classid
inner join pathBuilderCte cte on cte.id = n.CMID 
where n.ISDEFAULT = 1
AND UPPER (cls.NAME) = 'REPORT'
and cte.namePath like 'root\Public Folders\PROD\%'


--Then here’s the other bit for the EAM DB itself:

use [eam-s1prs]
go
declare @oldTenant nvarchar(12) = 'PROD'
, @newTenant nvarchar(12) = 'S1PRS'
update R5FUNCTIONS 
set FUN_FILE = stuff(FUN_FILE, charindex(@oldTenant, FUN_FILE), len(@oldTenant), @newTenant) --replace first occurrence only
where FUN_FILE like @oldTenant + N'/%'