Copy the following commands and run them in the SQL query window for the SpeechMiner 8.5.0 to 8.5.0.1 Upgrade Procedure:





ALTER TABLE dbo.segmentAudioTbl ADD arrivalTime int NULL
GO
          
GO
ALTER TABLE [dbo].[coachingStaticCallListCalls] DROP CONSTRAINT [FK_coachingStaticCallListCalls_callMetaTbl]
GO
          
GO
GO

ALTER TABLE [dbo].[agentEntityTbl] DROP CONSTRAINT [IX_agentEntityTbl]
GO

          
GO

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_agentFilterCleanByDays]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_agentFilterCleanByDays]
GO
ALTER PROCEDURE [dbo].[sp_rebuild_partition_help_tables]

WITH EXECUTE AS SELF

  AS 



  BEGIN TRAN

	  TRUNCATE TABLE partitionProgramTbl

	insert into partitionprogramTbl

	select distinct b.partitionid,a.programid

	from (select callId,programId from callMetaTbl union select textId as callId,programId from TextData) a, callPartitionTbl as b

	where a.callid=b.callid


  COMMIT

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[partitionAgentTbl]') AND type in (N'U'))
DROP TABLE [dbo].[partitionAgentTbl]
GO



          
GO
ALTER TABLE WordPrecision
ADD  "wer" float(20)
ALTER TABLE WordPrecision
ADD  "recall" float(20)
GO
          
GO

ALTER TABLE NGProbs1 
ADD  "backoff" float(20)
ALTER TABLE NGProbs2
ADD  "backoff" float(20)
ALTER TABLE NGProbs3 
ADD  "backoff" float(20)
ALTER TABLE NGProbs4 
ADD  "backoff" float(20)
GO
          
GO
IF NOT EXISTS(select * from sys.columns where Name = N'isBackupConfigurationServer' and Object_ID = Object_ID(N'ComputerList'))
BEGIN
ALTER TABLE [dbo].[ComputerList] ADD isBackupConfigurationServer bit NOT NULL DEFAULT 0
END

          
GO
CREATE TABLE wildcardGrammars
(
languageId int NOT NULL,
[key] nvarchar(50) NOT NULL,
value nvarchar(50) NOT NULL,
PRIMARY KEY (languageId,[key])
);

GO

INSERT INTO wildcardGrammars values 
(0,'[alphanum]','alphanum'), 
(0,'[boolean]','boolean'),
(0,'[ccexpdate]','ccexpdate'),
(0,'[creditcard]','creditcard'),
(0,'[currency]','currency'),
(0,'[date]','date'),
(0,'[digits]','digits'),
(0,'[number]','number'),
(0,'[phone]','phone'),
(0,'[socialsecurity]','socialsecurity'),
(0,'[time]','time'),
(0,'[voiceenroll]','voiceenroll'),
(0,'[zipcode]', 'zipcode'),
(2,'[alphanum]','alphanum'),
(2,'[boolean]','boolean'),
(2,'[ccexpdate]','ccexpdate'),
(2,'[creditcard]','creditcard'),
(2,'[currency]','currency'),
(2,'[date]','date'),
(2,'[digits]','digits'),
(2,'[number]','number'),
(2,'[phone]','phone'),
(2,'[time]','time'),
(2,'[voiceenroll]','voiceenroll'),
(2,'[zipcode]','zipcode');

GO
          
GO
ALTER TABLE NGProbs1
DROP COLUMN backoff
ALTER TABLE NGProbs2
DROP COLUMN backoff
ALTER TABLE NGProbs3
DROP COLUMN backoff
ALTER TABLE NGProbs4
DROP COLUMN backoff
GO
          
GO
ALTER TABLE RecognitionLanguages
ADD "WordConfidence" varchar(100)
GO

Update RecognitionLanguages
SET WordConfidence = 'wordconfidence'
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_German'  where index1 = 6
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_Catalan'  where index1 = 13
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_English'  where index1 in (0,3,14)
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_Spanish'  where index1 in (1,2)
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_Portuguese'  where index1 = 11
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_French'  where index1 = 5
GO

CREATE TABLE WordConfidenceInterpolation
(
Language int,
wer varchar(255),
precision varchar(255),
recall varchar(255)
)

grant SELECT on WordConfidenceInterpolation to Platform

INSERT INTO WordConfidenceInterpolation values(0,'-0.0356 0.8034','0.0431 0.3559','0.0272 0.5093')
INSERT INTO WordConfidenceInterpolation values(1,'-0.0383 0.7144','0.0226 0.5246','0.0338 0.3492')
INSERT INTO WordConfidenceInterpolation values(2,'-0.0369 0.8605','0.0418 0.3515','0.0343 0.1833')
INSERT INTO WordConfidenceInterpolation values(3,'-0.0328 0.7378','0.0395 0.3265','0.0246 0.3305')
INSERT INTO WordConfidenceInterpolation values(5,'-0.0334 0.9805','0.0386 0.1734','0.0386 0.1734')
INSERT INTO WordConfidenceInterpolation values(6,'-0.0179 0.6302','0.0295 0.3729','0.018 0.3883')
INSERT INTO WordConfidenceInterpolation values(11,'-0.0203 0.3161','0.0339 0.3161','0.0167 0.4534')
INSERT INTO WordConfidenceInterpolation values(13,'-0.0356 0.8034','0.0431 0.3559','0.0272 0.5093')
INSERT INTO WordConfidenceInterpolation values(14,'-0.033 0.6866','0.043 0.3835','0.0379 0.3249')
GO
          
GO
GRANT select ON [dbo].[wildcardGrammars] TO [SMART],[SMARTCompileGrammar],[InteractionReceiver],[Platform],[SMConfig],[Web] 
GO
          
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[callPartitionTbl]') AND name = N'IX_callPartitionTbl_partitionId')
DROP INDEX [IX_callPartitionTbl_partitionId] ON [dbo].[callPartitionTbl] WITH ( ONLINE = OFF )
GO


          
GO
Update RecognitionLanguages	
set Display='South African English',
    NuanceRecognizerLanguagePack='en-ZA', 
	AS_PER_CHAR_TIME_DURATION=0.075, 
	culture='en-ZA', 
	encoding='English_South Africa.20127'
where index1=15

Update RecognitionLanguages	
set encoding='French_France.28591' where index1=5
GO
          
GO
Update RecognitionLanguages	
set Display='',
    NuanceRecognizerLanguagePack=NULL, 
	AS_PER_CHAR_TIME_DURATION=NULL, 
	culture=NULL, 
	DictionaryName=NULL,
	encoding=NULL
where index1=15

          
GO

GO
Alter FUNCTION [dbo].[sp_getTopicEventSequences] 
(
@callid int, @threshold int
)
RETURNS varchar(max)
AS
BEGIN
declare @text as varchar(max)
set @text=''
select @text=@text + ltrim(rtrim(str(e.resourceID))) + ' ' 
from callEventstbl e
where e.callid = @callid
and e.type=1 and e.subtype=1
and e.confidence>=@threshold
and e.found=1	
order by e.starttime
RETURN substring(@text, 1, len(@text))
END
go
          
GO
update dbo.callrecognizer set ADJUST_CONF = 'False'
GO
          
GO
Delete from wildcardGrammars
where [key]='[voiceenroll]'
          
GO
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
EXECUTE sp_rename N'dbo.wildcardGrammars.[key]', N'Tmp_token', 'COLUMN' 
GO
EXECUTE sp_rename N'dbo.wildcardGrammars.value', N'Tmp_grammar_1', 'COLUMN' 
GO
EXECUTE sp_rename N'dbo.wildcardGrammars.Tmp_token', N'token', 'COLUMN' 
GO
EXECUTE sp_rename N'dbo.wildcardGrammars.Tmp_grammar_1', N'grammar', 'COLUMN' 
GO
ALTER TABLE dbo.wildcardGrammars SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
          
GO
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @table_name = N'ComputerList'
set @col_name = N'isBackupConfigurationServer'

select @Command = 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name
 from sys.tables t   
  join    sys.default_constraints d       
   on d.parent_object_id = t.object_id  
  join    sys.columns c      
   on c.object_id = t.object_id      
    and c.column_id = d.parent_column_id
 where t.name = @table_name
  and c.name = @col_name

execute (@Command)
GO

IF EXISTS(select * from sys.columns where Name = N'isBackupConfigurationServer' and Object_ID = Object_ID(N'ComputerList'))
BEGIN
ALTER TABLE [dbo].[ComputerList] DROP COLUMN isBackupConfigurationServer 
END
GO

          
GO

ALTER PROCEDURE [dbo].[sp_deleteTexts](@TextIDs varchar(max),@deletedTextsNum int OUTPUT)
	 WITH EXECUTE AS SELF
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ParmDefinition nvarchar(500);
	set @deletedTextsNum ='-1'
	declare @SelectCounter nvarchar(max)
	set @SelectCounter = N'select @sOUT = count(*) from textData where textId in (' + @TextIDs + ')'
	SET @ParmDefinition = N'@sOUT int OUTPUT';
    exec sp_executesql @SelectCounter,@ParmDefinition,@sOUT=@deletedTextsNum OUTPUT;
	declare @deleteTexts as nvarchar (max)
	set @deleteTexts = ''
	---------
	set @deleteTexts = @deleteTexts + 'DELETE FROM callMetaExTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM callcategoryTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM callEventsTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM callAgentTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM callPartitionTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM textparties where textId in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM textStatus where textId in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM textData where textId in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM repCategoryMetaTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM callSpeakersTbl where callid in (' + @TextIDs + ')'
	---------
	exec sp_executesql @deleteTexts
END

          
GO
ALTER TABLE callNotesTbl DROP CONSTRAINT FK_callNotesTbl_callMetaTbl
          
GO
UPDATE RecognitionLanguages 
set DictionaryName = 'pt_BR' where index1=11
GO
UPDATE RecognitionLanguages 
set DictionaryName = 'es_MX' where index1 = 1
GO
UPDATE RecognitionLanguages 
set DictionaryName = 'es_ES' where index1 = 2
GO
          
GO
delete from reportCachingQueue 
GO
alter table reportCachingQueue add runAtTime int NOT NULL
GO 

CREATE PROCEDURE [dbo].[sp_getReportCachingNextTask]
AS
BEGIN

	BEGIN TRAN

	declare @id int
	SELECT top 1 @id=id from reportCachingQueue with(updlock holdlock)
	where nextTimeToRun<dbo.time2tod(GetUtcDate())
	order by nextTimeToRun

	IF @id IS NOT NULL
	BEGIN
		update reportCachingQueue set nextTimeToRun=dbo.time2tod(GetUtcDate())+1*60*60 where id=@id
		select reportid, [partitions], runAtTime from reportCachingQueue where id=@id
	END
	COMMIT

END
GO 

grant execute on sp_getReportCachingNextTask to platform
GO

          
GO

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.indexParamsTbl ADD
	autoRestore bit NOT NULL CONSTRAINT DF_indexParamsTbl_autoRestore DEFAULT 0,
	timeToWaitForFolder int NULL
GO
ALTER TABLE dbo.indexParamsTbl SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

GRANT update(indexdirectorypath, dailyBackupLocation) ON [indexparamstbl]  TO [Platform]
GO
          
GO
grant update(status,stoppedForPurge) on computerList to interactionReceiver
grant execute on sp_enqueuePurgeTask to interactionReceiver


          
GO
ALTER PROCEDURE [dbo].[sp_getClusterTrendData]
	@clusterId int,
	@maxTrendWeeksBack int
AS
BEGIN

	declare @firstDayOfWeek as int
	set @firstDayOfWeek = dbo.getFirstDayOfWeek()
	set DATEFIRST @firstDayOfWeek
	
	declare @paramsId as int
	declare @reportDate as datetime

	set @paramsId = (select paramsId from ExplorationReports where reportid=(select reportid from ExplorationReportsData where clusterid=@clusterId))
	set @reportDate = (select dbo.tod2time(endTime) from ExplorationReportParams where id=@paramsId)

	IF @reportDate IS NULL
	BEGIN
		select NULL as[time], NULL as [numberOfCalls], NULL as [totalNumberOfCalls]
		RETURN -- the specified cluster id does not exist.
	END

	create table #trendData (
		[time] datetime NOT NULL,
		[numberOfCalls] int,
		[totalNumberOfCalls] int
	)

	insert into #trendData 
	select dbo.tod2time([time]) as [time], numberOfCalls, totalNumberOfCalls
	from dbo.ExplorationTrendData where clusterid=@clusterId

	IF NOT EXISTS(select [time] from #trendData)
	BEGIN
		insert into #trendData values(DATEADD(DAY, 1-DATEPART(WEEKDAY, @reportDate), @reportDate), NULL, NULL)
	END

	-- add the dates (start of week) from the last date with data to the report time
	while dateadd(week, 1, (select max(time) from #trendData)) < @reportDate
	BEGIN
		declare @newTime as datetime
		set @newTime = dateadd(week, 1, (select max(time) from #trendData))
		IF not exists(select * from #trendData where [time] = @newTime)
		BEGIN
			insert into #trendData values(@newTime, NULL, NULL)
		END
	END

	declare @currentTrendDate as datetime
	set @currentTrendDate = (select max(time) from #trendData)

	-- skip the first week if the current report date is the first day of the week
	if @currentTrendDate = @reportDate
	BEGIN
		set @currentTrendDate = dateadd(week, -1, @currentTrendDate)
	END

	declare @count as int
	set @count = 0
	while @count < @maxTrendWeeksBack - 1
	BEGIN
		IF not exists(select * from #trendData where [time] = @currentTrendDate)
		BEGIN
			insert into #trendData values(@currentTrendDate, NULL, NULL)
		END

		set @currentTrendDate = dateadd(week, -1, @currentTrendDate)
		set @count = @count + 1
	END


	set @currentTrendDate = dateadd(week, 1, @currentTrendDate)

	delete from #trendData where time<@currentTrendDate -- remove the old items if there are more than @maxTrendWeeksBack items

	select * from #trendData order by [time]

	drop table #trendData

END

          
GO
GO

GRANT UPDATE ON dbo.[TextStatus] TO Web

GO
          
GO

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TextData
	DROP CONSTRAINT FK_TextData_ResourceType
GO
ALTER TABLE dbo.ResourceType SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TextData
	DROP CONSTRAINT FK_TextData_programInfoTbl
GO
ALTER TABLE dbo.programInfoTbl SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TextData
	DROP CONSTRAINT DF_TextData_customerId
GO
ALTER TABLE dbo.TextData
	DROP CONSTRAINT DF_TextData_customerGroupId
GO
ALTER TABLE dbo.TextData
	DROP CONSTRAINT DF_TextData_programId
GO
CREATE TABLE dbo.Tmp_TextData
	(
	textId int NOT NULL,
	resourceTypeId int NOT NULL,
	originalTime int NULL,
	customerId varchar(256) NOT NULL,
	customerGroupId varchar(256) NOT NULL,
	subject nvarchar(MAX) NULL,
	body nvarchar(MAX) NULL,
	externalTextId varchar(256) NULL,
	sender varchar(8000) NULL,
	receiver varchar(8000) NULL,
	cc varchar(8000) NULL,
	bcc varchar(8000) NULL,
	programId int NOT NULL,
	originalBody varchar(MAX) NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TextData SET (LOCK_ESCALATION = TABLE)
GO
GRANT DELETE ON dbo.Tmp_TextData TO Platform  AS dbo
GO
GRANT INSERT ON dbo.Tmp_TextData TO Platform  AS dbo
GO
GRANT SELECT ON dbo.Tmp_TextData TO Web  AS dbo
GO
GRANT SELECT ON dbo.Tmp_TextData TO Platform  AS dbo
GO
GRANT SELECT ON dbo.Tmp_TextData TO SMART  AS dbo
GO
GRANT UPDATE ON dbo.Tmp_TextData TO Web  AS dbo
GO
GRANT UPDATE ON dbo.Tmp_TextData TO Platform  AS dbo
GO
ALTER TABLE dbo.Tmp_TextData ADD CONSTRAINT
	DF_TextData_customerId DEFAULT ('') FOR customerId
GO
ALTER TABLE dbo.Tmp_TextData ADD CONSTRAINT
	DF_TextData_customerGroupId DEFAULT ('') FOR customerGroupId
GO
ALTER TABLE dbo.Tmp_TextData ADD CONSTRAINT
	DF_TextData_programId DEFAULT ((0)) FOR programId
GO
IF EXISTS(SELECT * FROM dbo.TextData)
	 EXEC('INSERT INTO dbo.Tmp_TextData (textId, resourceTypeId, originalTime, customerId, customerGroupId, subject, body, externalTextId, sender, receiver, cc, bcc, programId, originalBody)
		SELECT textId, resourceTypeId, originalTime, customerId, customerGroupId, CONVERT(nvarchar(MAX), subject), CONVERT(nvarchar(MAX), body), externalTextId, sender, receiver, cc, bcc, programId, originalBody FROM dbo.TextData WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.TextStatus
	DROP CONSTRAINT FK_TextStatus_TextData
GO
DROP TABLE dbo.TextData
GO
EXECUTE sp_rename N'dbo.Tmp_TextData', N'TextData', 'OBJECT' 
GO
ALTER TABLE dbo.TextData ADD CONSTRAINT
	PK_TextDataTbl PRIMARY KEY CLUSTERED 
	(
	textId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX_externalTextId ON dbo.TextData
	(
	externalTextId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.TextData ADD CONSTRAINT
	FK_TextData_programInfoTbl FOREIGN KEY
	(
	programId
	) REFERENCES dbo.programInfoTbl
	(
	programId
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.TextData ADD CONSTRAINT
	FK_TextData_ResourceType FOREIGN KEY
	(
	resourceTypeId
	) REFERENCES dbo.ResourceType
	(
	resourceTypeId
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TextStatus WITH NOCHECK ADD CONSTRAINT
	FK_TextStatus_TextData FOREIGN KEY
	(
	textId
	) REFERENCES dbo.TextData
	(
	textId
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.TextStatus
	NOCHECK CONSTRAINT FK_TextStatus_TextData
GO
ALTER TABLE dbo.TextStatus SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

          
GO
UPDATE [webServiceParams] SET [sendFeedbackEmail]='customercare@genesys.com'


          
GO
delete wildcardGrammars where grammar='digits'
          
GO
GO

UPDATE indexer set calls_per_chunk=6000

GO
          
GO
update dbo.versionTbl set version= '8.5.7104' where resource in ('SM', 'SMART') 
go