/****** 

Script for clearing token data from a TM-S TM, if data corruption has occurred that is preventing TUs from loading.

Can be used when TM-S system database and containers are on same sql server.

Edit the values of @systemDBName, @tmname and @lang below so they refer to the TM language direction in question.

******/

declare @tmname varchar(200)
declare @lang varchar(200)
declare @systemDBName varchar(200)

/****** 
Edit the values of these variables before running the script.
******/
set @systemDBName = 'TMService'
set @tmname = 'Co-op Pay'
set @lang = 'en-CA_fr-CA'

/****** 

******/
declare @cmd varchar(200)
declare @tmid varchar(200), @contid varchar(200)

print(' -- Running token-data-clearing script for TM [' + @tmname + '] in system DB [' + @systemDBName + '], language direction ' + @lang)

print('creating temporary tables')
IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END

CREATE TABLE #temp (id INT)

IF OBJECT_ID(N'tempdb..#Temp2') IS NOT NULL
BEGIN
DROP TABLE #Temp2
END

CREATE TABLE #temp2 (val1 varchar(200), val2 varchar(200))

set @cmd = 'insert into #temp2 select [TranslationMemoryId], [ContainerId] FROM [' + @systemDBName + '].[dbo].[TranslationMemory] where name = ''' + @tmname + ''''

print ('retrieving TMID and ContainerID')
exec (@cmd)
select @tmid = val1, @contid = val2 from #temp2
print('TMID is ' + @tmid + ' , ContainerID is ' + @contid)

declare @contname varchar(200)

print ('retrieving Container Name')
set @cmd = 'insert into #temp2 select [ContainerName], null FROM [' + @systemDBName + '].[dbo].[Container] where ContainerId = ''' + @contid + ''''

delete from #temp2
exec(@cmd)

select @contname = val1 from #temp2

print ('Container name is ' + @contname)

set @tmid = @tmid + '|' + @lang + '|' + @tmname

print ('retrieving TM integer ID in Container')
set @cmd = 'insert into #temp select id from [' + @contname + '].[dbo].[translation_memories] where lower(name) = lower(''' + @tmid + ''')'

exec (@cmd)

declare @id integer
select @id = id from #temp

print ('ID is ' + +cast( @id as varchar(20)))

set @cmd = 'update [' + @contname + '].[dbo].[translation_units_' +cast( @id as varchar(20)) + '] set source_token_data = null, target_token_data = null'

print('clearing token data for table with this cmd:')
print(@cmd)

exec( @cmd)

print('done')