WorldServer - How to find information about Translation Memory Sequences and their usage

Article Number:000010394 | Last Updated:7/22/2019 6:29 PM

Scope/Environment

SDL WorldServer

Question

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?

Answer

--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

Reference

Send Article Feedback