What Translation Memory (TM) Sequences are in use? What TM's or Sequences are currently in use and are being updated? Which TM's or Sequences appear to be out of use? |
--Query to pull the TM Sequences in use along with the write to --TM database ID, the write to TM database name and the last modified date --which tells us how current the data is DECLARE @statement NVARCHAR(4000), @id NVARCHAR(10),@rowcount int, @istm NVARCHAR (10), @tmid NVARCHAR (10) DECLARE @result_tbl Table ( TM_GROUP_NAME nvarchar(255), WRITE_TO_TM nvarchar(255), WRITE_TO_TM_NAME nvarchar(255), LAST_MODIFIED_DATE nvarchar(255) ) set @rowcount = (select COUNT(*) from tmgroups) set @id = '1' WHILE @id <= @rowcount BEGIN set @istm = (select implicit from TMGROUPS where TMGROUPID = (select tmgroupid from (select ROW_NUMBER ()OVER (order by getdate()) as RN,tmgroupid from TMGROUPS) q where q.RN =@id)) IF @istm = 0 GOTO updateCounter; set @tmid = (select writeTMdatabaseid --set @tmid = (select tmgroupid from --(select ROW_NUMBER ()OVER (order by getdate()) as RN,tmgroupid (select ROW_NUMBER ()OVER (order by getdate()) as RN,writeTMdatabaseid from TMGROUPS) q where RN =@id ) if object_id('ZTM' + @tmid + '_TRANSLATIONS', 'U') is not null GOTO process_tm; GOTO updateCounter; process_tm: --Print @id --print @tmid SET @statement = N'select distinct tmgroups.name as "TM_GROUP_NAME", tmgroups.writeTMdatabaseid as "WRITE_TO_TM", tmdatabases.name as "WRITE_TO_TM_NAME", max(ZTM' + @tmid + '_TRANSLATIONS.attr4) as "LAST_MODIFIED_DATE" from tmgroups, tmgrouptmdatabases, tmdatabases,ZTM' + @tmid + '_TRANSLATIONS where tmgroups.name like ''NA_%'' and tmgroups.implicit = 0 and tmgroups.writeTMdatabaseid = ' + @tmid + '-- tmgroupid (1) to tmdatabaseid and tmgrouptmdatabases.tmGroupId=tmgroups.tmgroupid and tmgroups.writetmdatabaseid=tmdatabases.tmdatabaseid group by tmgroups.name,tmgroups.writeTMdatabaseid, tmdatabases.name' --,ZTM'+@tmid+'_TRANSLATIONS.attr4' insert into @result_tbl EXECUTE sp_executesql @statement updateCounter: SET @id = @id + 1; END; select distinct TM_GROUP_NAME as "TM Sequence Name", 'ZTM' + WRITE_TO_TM as "Write To TM ", WRITE_TO_TM_NAME as "Write TO TM Name", max(DATEADD(SS, CONVERT(INT, CONVERT(BIGINT, LAST_MODIFIED_DATE)/1000), '19700101')) as "Last Modified Date" from @result_tbl where TM_GROUP_NAME like 'NA_%' group by TM_GROUP_NAME, 'ZTM' + WRITE_TO_TM, WRITE_TO_TM_NAME,LAST_MODIFIED_DATE order by TM_GROUP_NAME |