8.5.2 to 8.5.201 Database Changes - Commands
Copy the following commands and run them in the SQL query window for the SpeechMiner 8.5.2 to 8.5.201 Upgrade Procedure:
INSERT INTO [dbo].[objectPermissionsTbl]
([objectId]
,[groupId]
,[description]
,[values]
,[configurable]
,[explanation])
VALUES
(71
,14
,'View All Reports'
,'<Values xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Value><display>View All Reports</display><value>2</value></Value><Value><display>View Only My Reports And Shared Reports</display><value>0</value></Value></Values>'
,1
,'')
GO
GO
GO
INSERT INTO [dbo].[rolePermissionsTbl]
([objectId]
,[role]
,[permission])
VALUES
(71
,5
,2)
GO
GO
GRANT UPDATE ON [dbo].[configServer] TO SMConfig AS dbo
GO
GO
INSERT INTO [dbo].[objectPermissionsTbl]
([objectId]
,[groupId]
,[description]
,[values]
,[configurable]
,[explanation])
VALUES
(711
,41
,'Recording Panel'
,'<Values xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Value><display>Show</display><value>2</value></Value><Value><display>Hide</display><value>0</value></Value></Values>'
,1
,'')
GO
GO
GO
ALTER TABLE [dbo].[recentSMConfigApply] DROP CONSTRAINT [DF__recentSMC__textA__77FFC2B3]
GO
/****** Object: Table [dbo].[recentSMConfigApply] Script Date: 18/08/2014 14:19:22 ******/
DROP TABLE [dbo].[recentSMConfigApply]
GO
/****** Object: Table [dbo].[recentSMConfigApply] Script Date: 18/08/2014 14:19:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[recentSMConfigApply](
[sitesApply] [int] NOT NULL,
[reportsApply] [int] NOT NULL,
[licenseApply] [int] NOT NULL,
[servicesApply] [int] NOT NULL,
[audioManagerApply] [int] NOT NULL,
[indexApply] [int] NOT NULL,
[textAnalyticsApply] [int] NOT NULL,
[recordingParametersApply] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[recentSMConfigApply] ADD CONSTRAINT [DF__recentSMC__textA__77FFC2B3] DEFAULT ((0)) FOR [textAnalyticsApply]
GO
INSERT INTO [dbo].[recentSMConfigApply]
([sitesApply]
,[reportsApply]
,[licenseApply]
,[servicesApply]
,[audioManagerApply]
,[indexApply]
,[textAnalyticsApply]
,[recordingParametersApply])
VALUES
(0,
0,
0,
0,
0,
0,
0,
0)
GO
GRANT SELECT ON [dbo].[recentSMConfigApply] TO SMConfig AS dbo
GRANT UPDATE ON [dbo].[recentSMConfigApply] TO SMConfig AS dbo
GO
GO
INSERT INTO [dbo].[rolePermissionsTbl]
([objectId]
,[role]
,[permission])
VALUES
(711
,5
,2)
GO
GO
update RecognitionLanguages
set Display = 'Japanese', NuanceRecognizerLanguagePack = 'ja-JP', MinIndexConfidence = 40, culture='ja-JP', DictionaryName = 'ja_JP' where index1 = 17
update RecognitionLanguages set SetWordAsKeywordLength = 2 where index1 = 17
update RecognitionLanguages set AS_PER_CHAR_TIME_DURATION = 0.25 where index1 = 17
GO
delete RecognitionParams where language=17
insert into RecognitionParams values(17, 5, 'swirec_lmweight', '0.4')
insert into RecognitionParams values(17, 5, 'swirec_max_arcs', '3300')
insert into RecognitionParams values(17, 5, 'swirec_word_penalty', '0.1')
GO
delete LVCSRGrammarParams where language=17
insert into LVCSRGrammarParams values(17, 'ngram_order', '3')
insert into LVCSRGrammarParams values(17, 'cutoffs', '1 1')
GO
delete WordconfidenceInterpolation where language=17
insert into WordConfidenceInterpolation values(17, '-0.03 0.7', '0.04 0.35', '0.03 0.3')
update WordConfidenceInterpolation set wer = '-0.0757 0.8914' where language = 17
update WordConfidenceInterpolation set precision = '0.0486 0.3022' where language = 17
update WordConfidenceInterpolation set recall = '0.0735 0.1606' where language = 17
GO
delete stopwords where language=17
insert into stopWords values(17,N'これ,あれ,この,その,あの,そこ,あそこ,私,貴方,貴方方,私達,彼,の,から,より,と,彼に,中に,で,に,それで,私に,私たち,場合', '','','');
GO
GO
Update RecognitionLanguages
set MinIndexConfidence = 45 where index1 = 11
GO
Update RecognitionLanguages
set MinIndexConfidence = 60 where index1 = 14
GO
GO
delete wildcardGrammars where languageId=17
insert into wildcardGrammars values(17, '[*]','[*]',1.5,null)
insert into wildcardGrammars values(17, '[alphanum]','alphanum',1,null)
insert into wildcardGrammars values(17, '[boolean]','boolean',1.5,null)
insert into wildcardGrammars values(17, '[creditcard]','creditcard',3.5,null)
insert into wildcardGrammars values(17, '[currency]','currency',2,null)
insert into wildcardGrammars values(17, '[date]','date',2,null)
insert into wildcardGrammars values(17, '[number]','number',1,null)
insert into wildcardGrammars values(17, '[phone]','phone',3.5,null)
insert into wildcardGrammars values(17, '[postcode]','postcode',1.5,null)
insert into wildcardGrammars values(17, '[time]','time',1.5,null)
GO
GO
delete wordPronunciationKeys where language=17
insert into wordPronunciationKeys values('b',17,1)
insert into wordPronunciationKeys values('by',17,2)
insert into wordPronunciationKeys values('d',17,3)
insert into wordPronunciationKeys values('dy',17,4)
insert into wordPronunciationKeys values('g',17,5)
insert into wordPronunciationKeys values('gy',17,6)
insert into wordPronunciationKeys values('k',17,7)
insert into wordPronunciationKeys values('kk',17,8)
insert into wordPronunciationKeys values('ky',17,9)
insert into wordPronunciationKeys values('p',17,10)
insert into wordPronunciationKeys values('pp',17,11)
insert into wordPronunciationKeys values('py',17,12)
insert into wordPronunciationKeys values('t',17,13)
insert into wordPronunciationKeys values('tt',17,14)
insert into wordPronunciationKeys values('f',17,15)
insert into wordPronunciationKeys values('h',17,16)
insert into wordPronunciationKeys values('hy',17,17)
insert into wordPronunciationKeys values('s',17,18)
insert into wordPronunciationKeys values('S',17,19)
insert into wordPronunciationKeys values('ss',17,20)
insert into wordPronunciationKeys values('SS',17,21)
insert into wordPronunciationKeys values('z',17,22)
insert into wordPronunciationKeys values('ts',17,23)
insert into wordPronunciationKeys values('tS',17,24)
insert into wordPronunciationKeys values('dZ',17,25)
insert into wordPronunciationKeys values('m',17,26)
insert into wordPronunciationKeys values('my',17,27)
insert into wordPronunciationKeys values('n',17,28)
insert into wordPronunciationKeys values('ny',17,29)
insert into wordPronunciationKeys values('N',17,30)
insert into wordPronunciationKeys values('4',17,31)
insert into wordPronunciationKeys values('4y',17,32)
insert into wordPronunciationKeys values('y',17,33)
insert into wordPronunciationKeys values('w',17,34)
insert into wordPronunciationKeys values('a',17,35)
insert into wordPronunciationKeys values('aa',17,36)
insert into wordPronunciationKeys values('E',17,37)
insert into wordPronunciationKeys values('I',17,38)
insert into wordPronunciationKeys values('II',17,39)
insert into wordPronunciationKeys values('M',17,40)
insert into wordPronunciationKeys values('MM',17,41)
insert into wordPronunciationKeys values('O',17,42)
insert into wordPronunciationKeys values('OO',17,42)
GO
GO
delete WordconfidenceInterpolation where language=17
insert into WordConfidenceInterpolation values(17, '-0.03 0.7', '0.04 0.35', '0.03 0.3')
update WordConfidenceInterpolation set wer = '-0.0467 0.7316' where language = 17
update WordConfidenceInterpolation set precision = '0.0337 0.5312' where language = 17
update WordConfidenceInterpolation set recall = '0.0411 0.3426' where language = 17
GO
GO
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
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.LVCSRTrainingMaterial
DROP CONSTRAINT DF_LVCSRTrainingMaterial_source
GO
CREATE TABLE dbo.Tmp_LVCSRTrainingMaterial
(
weight decimal(18, 2) NOT NULL,
language int NOT NULL,
sentence nvarchar(MAX) NOT NULL,
source int NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_LVCSRTrainingMaterial SET (LOCK_ESCALATION = TABLE)
GO
GRANT DELETE ON dbo.Tmp_LVCSRTrainingMaterial TO Platform AS dbo
GO
GRANT INSERT ON dbo.Tmp_LVCSRTrainingMaterial TO Platform AS dbo
GO
GRANT SELECT ON dbo.Tmp_LVCSRTrainingMaterial TO Platform AS dbo
GO
GRANT SELECT ON dbo.Tmp_LVCSRTrainingMaterial TO SMART AS dbo
GO
ALTER TABLE dbo.Tmp_LVCSRTrainingMaterial ADD CONSTRAINT
DF_LVCSRTrainingMaterial_source DEFAULT ((0)) FOR source
GO
IF EXISTS(SELECT * FROM dbo.LVCSRTrainingMaterial)
EXEC('INSERT INTO dbo.Tmp_LVCSRTrainingMaterial (weight, language, sentence, source)
SELECT weight, language, CONVERT(nvarchar(MAX), sentence), source FROM dbo.LVCSRTrainingMaterial WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.LVCSRTrainingMaterial
GO
EXECUTE sp_rename N'dbo.Tmp_LVCSRTrainingMaterial', N'LVCSRTrainingMaterial', 'OBJECT'
GO
COMMIT
GO
update RecognitionLanguages set Display = 'English - USA' where index1 = 0
update RecognitionLanguages set Display = 'Spanish - USA' where index1 = 1
update RecognitionLanguages set Display = 'Spanish' where index1 = 2
update RecognitionLanguages set Display = 'English – British' where index1 = 3
update RecognitionLanguages set Display = 'French – Canadian' where index1 = 4
update RecognitionLanguages set Display = 'French' where index1 = 5
update RecognitionLanguages set Display = 'German' where index1 = 6
update RecognitionLanguages set Display = 'Italian' where index1 = 7
update RecognitionLanguages set Display = 'Portuguese – Brazil' where index1 = 11
update RecognitionLanguages set Display = 'Catalan' where index1 = 13
update RecognitionLanguages set Display = 'Korean' where index1 = 15
update RecognitionLanguages set Display = 'English – South African' where index1 = 16
update RecognitionLanguages set Display = 'Japanese' where index1 = 17
Go
GO
update RecognitionLanguages set DictionaryName = null where index1 = 17
Go
GO
ALTER TABLE [dbo].[TextStatus] DROP CONSTRAINT [FK_TextStatus_TextData]
GO
ALTER TABLE [dbo].[TextData] DROP CONSTRAINT [PK_TextDataTbl]
GO
ALTER TABLE [dbo].[TextData] ADD CONSTRAINT [PK_TextDataTbl] PRIMARY KEY NONCLUSTERED
(
[textId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TextStatus] WITH NOCHECK ADD CONSTRAINT [FK_TextStatus_TextData] FOREIGN KEY([textId])
REFERENCES [dbo].[TextData] ([textId])
GO
ALTER TABLE [dbo].[TextStatus] NOCHECK CONSTRAINT [FK_TextStatus_TextData]
GO
CREATE CLUSTERED INDEX [IX_originalTime] ON [dbo].[TextData]
(
[originalTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- this SP creates the schema and the function for DB partitioning, and set callMetaTbl, textMeta and repCategoryMetaTbl to use them
ALTER PROCEDURE [dbo].[sp_create_DB_storage_partitions]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @statement as nvarchar(max)
set @statement = '
CREATE PARTITION FUNCTION fnPartitionCalls (int)
AS RANGE RIGHT
FOR VALUES (
dbo.time2tod(''1/1/2014''),
dbo.time2tod(''1/11/2014''),
dbo.time2tod(''1/21/2014''),
dbo.time2tod(''2/1/2014''),
dbo.time2tod(''2/11/2014''),
dbo.time2tod(''2/21/2014''),
dbo.time2tod(''3/1/2014''),
dbo.time2tod(''3/11/2014''),
dbo.time2tod(''3/21/2014''),
dbo.time2tod(''4/1/2014''),
dbo.time2tod(''4/11/2014''),
dbo.time2tod(''4/21/2014''),
dbo.time2tod(''5/1/2014''),
dbo.time2tod(''5/11/2014''),
dbo.time2tod(''5/21/2014''),
dbo.time2tod(''6/1/2014''),
dbo.time2tod(''6/11/2014''),
dbo.time2tod(''6/21/2014''),
dbo.time2tod(''7/1/2014''),
dbo.time2tod(''7/11/2014''),
dbo.time2tod(''7/21/2014''),
dbo.time2tod(''8/1/2014''),
dbo.time2tod(''8/11/2014''),
dbo.time2tod(''8/21/2014''),
dbo.time2tod(''9/1/2014''),
dbo.time2tod(''9/11/2014''),
dbo.time2tod(''9/21/2014''),
dbo.time2tod(''10/1/2014''),
dbo.time2tod(''10/11/2014''),
dbo.time2tod(''10/21/2014''),
dbo.time2tod(''11/1/2014''),
dbo.time2tod(''11/11/2014''),
dbo.time2tod(''11/21/2014''),
dbo.time2tod(''12/1/2014''),
dbo.time2tod(''12/11/2014''),
dbo.time2tod(''12/21/2014''),
dbo.time2tod(''1/1/2015''),
dbo.time2tod(''1/11/2015''),
dbo.time2tod(''1/21/2015''),
dbo.time2tod(''2/1/2015''),
dbo.time2tod(''2/11/2015''),
dbo.time2tod(''2/21/2015''),
dbo.time2tod(''3/1/2015''),
dbo.time2tod(''3/11/2015''),
dbo.time2tod(''3/21/2015''),
dbo.time2tod(''4/1/2015''),
dbo.time2tod(''4/11/2015''),
dbo.time2tod(''4/21/2015''),
dbo.time2tod(''5/1/2015''),
dbo.time2tod(''5/11/2015''),
dbo.time2tod(''5/21/2015''),
dbo.time2tod(''6/1/2015''),
dbo.time2tod(''6/11/2015''),
dbo.time2tod(''6/21/2015''),
dbo.time2tod(''7/1/2015''),
dbo.time2tod(''7/11/2015''),
dbo.time2tod(''7/21/2015''),
dbo.time2tod(''8/1/2015''),
dbo.time2tod(''8/11/2015''),
dbo.time2tod(''8/21/2015''),
dbo.time2tod(''9/1/2015''),
dbo.time2tod(''9/11/2015''),
dbo.time2tod(''9/21/2015''),
dbo.time2tod(''10/1/2015''),
dbo.time2tod(''10/11/2015''),
dbo.time2tod(''10/21/2015''),
dbo.time2tod(''11/1/2015''),
dbo.time2tod(''11/11/2015''),
dbo.time2tod(''11/21/2015''),
dbo.time2tod(''12/1/2015''),
dbo.time2tod(''12/11/2015''),
dbo.time2tod(''12/21/2015''),
dbo.time2tod(''1/1/2016''),
dbo.time2tod(''1/11/2016''),
dbo.time2tod(''1/21/2016''),
dbo.time2tod(''2/1/2016''),
dbo.time2tod(''2/11/2016''),
dbo.time2tod(''2/21/2016''),
dbo.time2tod(''3/1/2016''),
dbo.time2tod(''3/11/2016''),
dbo.time2tod(''3/21/2016''),
dbo.time2tod(''4/1/2016''),
dbo.time2tod(''4/11/2016''),
dbo.time2tod(''4/21/2016''),
dbo.time2tod(''5/1/2016''),
dbo.time2tod(''5/11/2016''),
dbo.time2tod(''5/21/2016''),
dbo.time2tod(''6/1/2016''),
dbo.time2tod(''6/11/2016''),
dbo.time2tod(''6/21/2016''),
dbo.time2tod(''7/1/2016''),
dbo.time2tod(''7/11/2016''),
dbo.time2tod(''7/21/2016''),
dbo.time2tod(''8/1/2016''),
dbo.time2tod(''8/11/2016''),
dbo.time2tod(''8/21/2016''),
dbo.time2tod(''9/1/2016''),
dbo.time2tod(''9/11/2016''),
dbo.time2tod(''9/21/2016''),
dbo.time2tod(''10/1/2016''),
dbo.time2tod(''10/11/2016''),
dbo.time2tod(''10/21/2016''),
dbo.time2tod(''11/1/2016''),
dbo.time2tod(''11/11/2016''),
dbo.time2tod(''11/21/2016''),
dbo.time2tod(''12/1/2016''),
dbo.time2tod(''12/11/2016''),
dbo.time2tod(''12/21/2016''),
dbo.time2tod(''1/1/2017''),
dbo.time2tod(''1/11/2017''),
dbo.time2tod(''1/21/2017''),
dbo.time2tod(''2/1/2017''),
dbo.time2tod(''2/11/2017''),
dbo.time2tod(''2/21/2017''),
dbo.time2tod(''3/1/2017''),
dbo.time2tod(''3/11/2017''),
dbo.time2tod(''3/21/2017''),
dbo.time2tod(''4/1/2017''),
dbo.time2tod(''4/11/2017''),
dbo.time2tod(''4/21/2017''),
dbo.time2tod(''5/1/2017''),
dbo.time2tod(''5/11/2017''),
dbo.time2tod(''5/21/2017''),
dbo.time2tod(''6/1/2017''),
dbo.time2tod(''6/11/2017''),
dbo.time2tod(''6/21/2017''),
dbo.time2tod(''7/1/2017''),
dbo.time2tod(''7/11/2017''),
dbo.time2tod(''7/21/2017''),
dbo.time2tod(''8/1/2017''),
dbo.time2tod(''8/11/2017''),
dbo.time2tod(''8/21/2017''),
dbo.time2tod(''9/1/2017''),
dbo.time2tod(''9/11/2017''),
dbo.time2tod(''9/21/2017''),
dbo.time2tod(''10/1/2017''),
dbo.time2tod(''10/11/2017''),
dbo.time2tod(''10/21/2017''),
dbo.time2tod(''11/1/2017''),
dbo.time2tod(''11/11/2017''),
dbo.time2tod(''11/21/2017''),
dbo.time2tod(''12/1/2017''),
dbo.time2tod(''12/11/2017''),
dbo.time2tod(''12/21/2017''),
dbo.time2tod(''1/1/2018''),
dbo.time2tod(''1/11/2018''),
dbo.time2tod(''1/21/2018''),
dbo.time2tod(''2/1/2018''),
dbo.time2tod(''2/11/2018''),
dbo.time2tod(''2/21/2018''),
dbo.time2tod(''3/1/2018''),
dbo.time2tod(''3/11/2018''),
dbo.time2tod(''3/21/2018''),
dbo.time2tod(''4/1/2018''),
dbo.time2tod(''4/11/2018''),
dbo.time2tod(''4/21/2018''),
dbo.time2tod(''5/1/2018''),
dbo.time2tod(''5/11/2018''),
dbo.time2tod(''5/21/2018''),
dbo.time2tod(''6/1/2018''),
dbo.time2tod(''6/11/2018''),
dbo.time2tod(''6/21/2018''),
dbo.time2tod(''7/1/2018''),
dbo.time2tod(''7/11/2018''),
dbo.time2tod(''7/21/2018''),
dbo.time2tod(''8/1/2018''),
dbo.time2tod(''8/11/2018''),
dbo.time2tod(''8/21/2018''),
dbo.time2tod(''9/1/2018''),
dbo.time2tod(''9/11/2018''),
dbo.time2tod(''9/21/2018''),
dbo.time2tod(''10/1/2018''),
dbo.time2tod(''10/11/2018''),
dbo.time2tod(''10/21/2018''),
dbo.time2tod(''11/1/2018''),
dbo.time2tod(''11/11/2018''),
dbo.time2tod(''11/21/2018''),
dbo.time2tod(''12/1/2018''),
dbo.time2tod(''12/11/2018''),
dbo.time2tod(''12/21/2018''),
dbo.time2tod(''12/21/2028'')
)
CREATE PARTITION SCHEME CallScheme
AS PARTITION fnPartitionCalls
ALL to ([Primary])
DROP INDEX [IX_callTime] ON [dbo].[callMetaTbl]
CREATE CLUSTERED INDEX [IX_callTime] ON [dbo].[callMetaTbl]
(
[callTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [CallScheme]([callTime])
DROP INDEX [IX_callTime] ON [dbo].[repCategoryMetaTbl]
CREATE CLUSTERED INDEX [IX_callTime] ON [dbo].[repCategoryMetaTbl]
(
[callTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [CallScheme]([callTime])
DROP INDEX [IX_originalTime] ON [dbo].[TextData]
CREATE CLUSTERED INDEX [IX_originalTime] ON [dbo].[TextData]
(
[originalTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [CallScheme]([originalTime])'
IF CHARINDEX('Enterprise', CONVERT(varchar(1000), SERVERPROPERTY('edition'))) > 0
BEGIN
EXEC dbo.sp_executesql @statement
End
END
GO
GO
update audioConversionTypesTbl set conversionType=4,externalToolPath=null,parametersForTool=null where fromFormat=3 and toFormat=3
GO
/****** Object: StoredProcedure [dbo].[sp_createMaintenanceJob] Script Date: 9/17/2014 11:40:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_createMaintenanceJob]
@targetSpeechMinerDB varchar(50)
AS
BEGIN
DECLARE @connectorService varchar(256)
DECLARE @connectorComputer varchar(24)
-------------------------------------------------------------------------------------------------------------------------------
--------- Set the connector service name and computer for the job to stop the connector before continuing maintenance ---------
-------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--------- Turn this flag on to create the agents tree from the calls, if you do not have the agent information ---------
------------------------------------------------------------------------------------------------------------------------
DECLARE @createAgentsFromPartitions bit
SET @createAgentsFromPartitions = 0
DECLARE @createAgentsDaysToKeep int
SET @createAgentsDaysToKeep = 30
/****** Object: Job [SpeechMiner_Maintenance] ******/
BEGIN TRANSACTION
DECLARE @jobName VARCHAR(100)
SET @jobName='SpeechMiner_Maintenance_job - ' + @targetSpeechMinerDB
DECLARE @oldJobId AS uniqueidentifier
SELECT @oldJobId = job_id FROM msdb.dbo.sysjobs_view WHERE name = @jobName
IF @oldJobId IS NOT NULL
EXEC msdb.dbo.sp_delete_job @job_id=@oldJobId, @delete_unused_schedule=1
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@jobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'dbuser', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Shutdown System] ******/
DECLARE @stopCmd as nvarchar(max)
SET @stopCmd = 'update computerlist set status = 0'
IF @connectorService is not null AND @connectorComputer is not null
BEGIN
SET @stopCmd = @stopCmd + '
declare @r as int
exec @r = dbo.sp_control_service 0,''' + @connectorService + ''',''' + @connectorComputer + '''
select ''result of stopping connector ''+ Cast(@r as nvarchar(1))'
END
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Shutdown System',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=4,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@stopCmd,
@database_name=@targetSpeechMinerDB,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Rebuild Index] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Rebuild Index',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXECUTE sp_rebuild_indexes',
@database_name=@targetSpeechMinerDB,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Turn on System] ******/
DECLARE @startCmd as nvarchar(max)
SET @startCmd = 'update computerlist set status = 1'
IF @connectorService is not null AND @connectorComputer is not null
BEGIN
SET @startCmd = @startCmd + '
declare @r as int
exec @r = dbo.sp_control_service 1,''' + @connectorService + ''',''' + @connectorComputer + '''
select ''result of start uconnector ''+ Cast(@r as nvarchar(1))'
END
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Turn on System',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@startCmd,
@database_name=@targetSpeechMinerDB,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Purge old msg logs] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge old msg logs',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'delete from msgLogTbl
where [time] < dateadd(month,-1,getdate())',
@database_name=@targetSpeechMinerDB,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Purge old user events] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge old user events',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'delete from userEventsTbl
where eventTime < dateadd(month,-12,getdate())',
@database_name=@targetSpeechMinerDB,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Purge report agents filter] ******/
DECLARE @agentsCmd as nvarchar(max)
SET @agentsCmd = ''
IF @createAgentsFromPartitions = 1
BEGIN
SET @agentsCmd = @agentsCmd + '
exec sp_createAgentsFromPartitions ' + cast(@createAgentsDaysToKeep as varchar(10))
END
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update agents',
@step_id=6,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@agentsCmd,
@database_name=@targetSpeechMinerDB,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Clean stuck calls] ******/
DECLARE @CleanCallsCmd as nvarchar(max)
SET @CleanCallsCmd = 'update CallStatusTbl set startrectime=0
where startrectime=-1 and endRecTime=0
and not exists (select callid from CallQTbl where CallQTbl.callid=CallStatusTbl.callid)'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Clean stuck calls',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=1,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@CleanCallsCmd,
@database_name=@targetSpeechMinerDB,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/*********************************************/
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/* Uncomment to schedule job (parameters are for weekly run)
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20070820,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
*/
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
END
GO
grant select on ComputerList to CreatePerfCounters
GO
ALTER TABLE wordPronunciation
ALTER COLUMN Word nvarchar(64)
Insert into wordPronunciation values(17,N'微々たる','PREDEFINED','bIbIta4M')
Insert into wordPronunciation values(17,N'山々','PREDEFINED','yamayama')
Insert into wordPronunciation values(17,N'益々','PREDEFINED','masMmasM')
Insert into wordPronunciation values(17,N'我々','PREDEFINED','wa4Ewa4E')
Insert into wordPronunciation values(17,N'色々','PREDEFINED','I4OI4O')
Insert into wordPronunciation values(17,N'度々','PREDEFINED','tabItabI')
Insert into wordPronunciation values(17,N'様々','PREDEFINED','samazama')
Insert into wordPronunciation values(17,N'面々','PREDEFINED','mENmEN')
Insert into wordPronunciation values(17,N'代々木','PREDEFINED','yOyOgI')
Insert into wordPronunciation values(17,N'後々','PREDEFINED','atOatO')
Insert into wordPronunciation values(17,N'細々','PREDEFINED','kOmagOma')
Insert into wordPronunciation values(17,N'重々','PREDEFINED','dZMMdZMM')
Insert into wordPronunciation values(17,N'延々と','PREDEFINED','ENENtO')
Insert into wordPronunciation values(17,N'常々','PREDEFINED','tsMnEzMnE')
Insert into wordPronunciation values(17,N'黙々と','PREDEFINED','mOkMmOkMtO')
Insert into wordPronunciation values(17,N'少々','PREDEFINED','SOOSOO')
Insert into wordPronunciation values(17,N'近々の','PREDEFINED','tSIkadZIkanO')
Insert into wordPronunciation values(17,N'諸々','PREDEFINED','mO4OmO4O')
Insert into wordPronunciation values(17,N'往々','PREDEFINED','OOOO')
Insert into wordPronunciation values(17,N'一々','PREDEFINED','ItSIItSI')
Insert into wordPronunciation values(17,N'木々','PREDEFINED','kIgI')
Insert into wordPronunciation values(17,N'生々しい','PREDEFINED','namanamaSII')
Insert into wordPronunciation values(17,N'長々','PREDEFINED','naganaga')
Insert into wordPronunciation values(17,N'丸々','PREDEFINED','ma4Mma4M')
Insert into wordPronunciation values(17,N'云々','PREDEFINED','MNnMN')
Insert into wordPronunciation values(17,N'先々','PREDEFINED','sakIzakI')
Insert into wordPronunciation values(17,N'佐々木','PREDEFINED','sasakI')
Insert into wordPronunciation values(17,N'次々','PREDEFINED','tsMgItsMgI')
Insert into wordPronunciation values(17,N'早々','PREDEFINED','sOOsOO')
Insert into wordPronunciation values(17,N'元々','PREDEFINED','mOtOmOtO')
Insert into wordPronunciation values(17,N'散々','PREDEFINED','saNzaN')
Insert into wordPronunciation values(17,N'酒々井','PREDEFINED','SIsMI')
Insert into wordPronunciation values(17,N'時々','PREDEFINED','tOkIdOkI')
Insert into wordPronunciation values(17,N'段々','PREDEFINED','daNdaN')
Insert into wordPronunciation values(17,N'数々','PREDEFINED','kazMkazM')
Insert into wordPronunciation values(17,N'日々','PREDEFINED','hIbI')
Insert into wordPronunciation values(17,N'続々','PREDEFINED','zOkMzOkM')
Insert into wordPronunciation values(17,N'国々','PREDEFINED','kMnIgMnI')
Insert into wordPronunciation values(17,N'久々','PREDEFINED','hIsabIsa')
Insert into wordPronunciation values(17,N'淡々','PREDEFINED','taNtaN')
Insert into wordPronunciation values(17,N'堂々','PREDEFINED','dOOdOO')
Insert into wordPronunciation values(17,N'近々','PREDEFINED','tSIkadZIka')
Insert into wordPronunciation values(17,N'等々','PREDEFINED','tOOtOO')
Insert into wordPronunciation values(17,N'月々','PREDEFINED','tsMkIzMkI')
Insert into wordPronunciation values(17,N'種々','PREDEFINED','SMdZM')
Insert into wordPronunciation values(17,N'着々','PREDEFINED','tSakMtSakM')
Insert into wordPronunciation values(17,N'方々','PREDEFINED','katagata')
Insert into wordPronunciation values(17,N'年々','PREDEFINED','nENnEN')
Insert into wordPronunciation values(17,N'人々','PREDEFINED','hItObItO')
Insert into wordPronunciation values(17,N'個々','PREDEFINED','kOkO')
Insert into wordPronunciation values(17,N'多々','PREDEFINED','tata')
Insert into wordPronunciation values(17,N'徐々に','PREDEFINED','dZOdZOnI')
Insert into wordPronunciation values(17,N'点々','PREDEFINED','tENtEN')
Insert into wordPronunciation values(17,N'大々的','PREDEFINED','daIdaItEkI')
Insert into wordPronunciation values(17,N'個々人','PREDEFINED','kOkOdZIN')
Insert into wordPronunciation values(17,N'色々ー','PREDEFINED','I4OI4OO')
Insert into wordPronunciation values(17,N'時々ー','PREDEFINED','tOkIdOkII')
GO
GRANT select ON [dbo].[wordPronunciation] TO [SMARTCompileGrammar],[InteractionReceiver],[Platform],[SMConfig],[Web]
GO
GO
/****** Adding filter words in Korean ******/
insert into wordFilterTbl values (17, N'はち', 0)
insert into wordFilterTbl values (17, N'八', 0)
insert into wordFilterTbl values (17, N'じゅうはち', 0)
insert into wordFilterTbl values (17, N'十八', 0)
insert into wordFilterTbl values (17, N'はちじゅう', 0)
insert into wordFilterTbl values (17, N'八十', 0)
insert into wordFilterTbl values (17, N'じゅういち', 0)
insert into wordFilterTbl values (17, N'十一', 0)
insert into wordFilterTbl values (17, N'じゅうご', 0)
insert into wordFilterTbl values (17, N'十五', 0)
insert into wordFilterTbl values (17, N'ごじゅう', 0)
insert into wordFilterTbl values (17, N'五十', 0)
insert into wordFilterTbl values (17, N'ご', 0)
insert into wordFilterTbl values (17, N'五', 0)
insert into wordFilterTbl values (17, N'よんじゅう', 0)
insert into wordFilterTbl values (17, N'四十', 0)
insert into wordFilterTbl values (17, N'よん', 0)
insert into wordFilterTbl values (17, N'し', 0)
insert into wordFilterTbl values (17, N'四', 0)
insert into wordFilterTbl values (17, N'じゅうよん', 0)
insert into wordFilterTbl values (17, N'十四', 0)
insert into wordFilterTbl values (17, N'ひゃく', 0)
insert into wordFilterTbl values (17, N'百', 0)
insert into wordFilterTbl values (17, N'きゅう', 0)
insert into wordFilterTbl values (17, N'く', 0)
insert into wordFilterTbl values (17, N'九', 0)
insert into wordFilterTbl values (17, N'じゅうきゅう', 0)
insert into wordFilterTbl values (17, N'十九', 0)
insert into wordFilterTbl values (17, N'きゅうじゅう', 0)
insert into wordFilterTbl values (17, N'九十', 0)
insert into wordFilterTbl values (17, N'いち', 0)
insert into wordFilterTbl values (17, N'一', 0)
insert into wordFilterTbl values (17, N'なな', 0)
insert into wordFilterTbl values (17, N'しち', 0)
insert into wordFilterTbl values (17, N'七', 0)
insert into wordFilterTbl values (17, N'じゅうなな', 0)
insert into wordFilterTbl values (17, N'じゅうしち', 0)
insert into wordFilterTbl values (17, N'十七', 0)
insert into wordFilterTbl values (17, N'ななじゅう', 0)
insert into wordFilterTbl values (17, N'七十', 0)
insert into wordFilterTbl values (17, N'ろく', 0)
insert into wordFilterTbl values (17, N'六', 0)
insert into wordFilterTbl values (17, N'じゅうろく', 0)
insert into wordFilterTbl values (17, N'十六', 0)
insert into wordFilterTbl values (17, N'ろくじゅう', 0)
insert into wordFilterTbl values (17, N'六十', 0)
insert into wordFilterTbl values (17, N'じゅう', 0)
insert into wordFilterTbl values (17, N'十', 0)
insert into wordFilterTbl values (17, N'じゅうさん', 0)
insert into wordFilterTbl values (17, N'十三', 0)
insert into wordFilterTbl values (17, N'さんじゅう', 0)
insert into wordFilterTbl values (17, N'三十', 0)
insert into wordFilterTbl values (17, N'せん', 0)
insert into wordFilterTbl values (17, N'千', 0)
insert into wordFilterTbl values (17, N'さん', 0)
insert into wordFilterTbl values (17, N'三', 0)
insert into wordFilterTbl values (17, N'じゅうに', 0)
insert into wordFilterTbl values (17, N'十二', 0)
insert into wordFilterTbl values (17, N'にじゅう', 0)
insert into wordFilterTbl values (17, N'二十', 0)
insert into wordFilterTbl values (17, N'に', 0)
insert into wordFilterTbl values (17, N'二', 0)
insert into wordFilterTbl values (17, N'ゼロ', 0)
insert into wordFilterTbl values (17, N'零', 0)
GO
GO
/****** Object: Table [dbo].[alertMailMessageTbl] Script Date: 09/22/2014 11:01:48 ******/
/****** Object: Table [dbo].[alertMailMessageTbl] Script Date: 09/22/2014 11:01:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp_alertMailMessageTbl](
[ID] [int] NOT NULL,
[Text] [nvarchar](256) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
GRANT SELECT ON dbo.[tmp_alertMailMessageTbl] TO Web,utopy AS dbo
GO
GRANT INSERT ON dbo.[tmp_alertMailMessageTbl] TO utopy AS dbo
GO
GRANT DELETE ON dbo.[tmp_alertMailMessageTbl] TO utopy AS dbo
GO
GRANT UPDATE ON dbo.[tmp_alertMailMessageTbl] TO utopy AS dbo
GO
IF EXISTS(SELECT * FROM dbo.alertMailMessageTbl)
EXEC('INSERT INTO dbo.[tmp_alertMailMessageTbl] (ID,[Text])
SELECT ID, CONVERT(nvarchar(256), Text) FROM dbo.alertMailMessageTbl WITH (HOLDLOCK TABLOCKX)')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[alertMailMessageTbl]') AND type in (N'U'))
DROP TABLE [dbo].[alertMailMessageTbl]
GO
EXECUTE sp_rename N'dbo.tmp_alertMailMessageTbl', N'alertMailMessageTbl', 'OBJECT'
ALTER TABLE alertMailMessageTbl ADD CONSTRAINT [PK_alertMailMessageTbl] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_alertTopicTbl_alertTbl]') AND parent_object_id = OBJECT_ID(N'[dbo].[alertTopicTbl]'))
ALTER TABLE [dbo].[alertTopicTbl] DROP CONSTRAINT [FK_alertTopicTbl_alertTbl]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_alertCategoryTbl_alertTbl]') AND parent_object_id = OBJECT_ID(N'[dbo].[alertCategoryTbl]'))
ALTER TABLE [dbo].[alertCategoryTbl] DROP CONSTRAINT [FK_alertCategoryTbl_alertTbl]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__alertTbl__timeCr__4D4C0586]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[alertTbl] DROP CONSTRAINT [DF__alertTbl__timeCr__4D4C0586]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__alertTbl__timeUp__4E4029BF]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[alertTbl] DROP CONSTRAINT [DF__alertTbl__timeUp__4E4029BF]
END
/****** Object: Table [dbo].[alertTbl] Script Date: 09/22/2014 12:04:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp_alertTbl](
[alertId] [int] IDENTITY(1000,1) NOT NULL,
[alertName] [nvarchar](256) NULL,
[creator] [nvarchar](256) NOT NULL,
[timeCreated] [datetime] NOT NULL,
[timeUpdated] [datetime] NOT NULL,
[lastMonitorTime] [datetime] NULL,
[lastAlertTime] [datetime] NULL,
[alertTypeId] [int] NOT NULL,
[alertType] [int] NOT NULL) ON [PRIMARY]
GO
GO
GRANT SELECT ON dbo.[tmp_alertTbl] TO Platform,utopy AS dbo
GO
GRANT INSERT ON dbo.[tmp_alertTbl] TO utopy AS dbo
GO
GRANT DELETE ON dbo.[tmp_alertTbl] TO utopy AS dbo
GO
GRANT UPDATE ON dbo.[tmp_alertTbl] TO utopy AS dbo
GO
GRANT UPDATE ON dbo.[tmp_alertTbl](lastMonitorTime) TO platform AS dbo
GO
GRANT VIEW DEFINITION ON dbo.[tmp_alertTbl] TO utopy AS dbo
GO
SET ANSI_PADDING ON
GO
SET IDENTITY_INSERT dbo.[tmp_alertTbl] ON
IF EXISTS(SELECT * FROM [dbo].alertTbl)
EXEC('INSERT INTO dbo.[tmp_alertTbl] (alertId,[alertName],[creator],[timeCreated],[timeUpdated],[lastMonitorTime],[lastAlertTime],[alertTypeId],[alertType])
SELECT alertId, CONVERT(nvarchar(256), alertName), CONVERT(nvarchar(1000), creator),[timeCreated],[timeUpdated],[lastMonitorTime],alertTypeId,[alertType] FROM dbo.[alertTbl] WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.[tmp_alertTbl] OFF
GO
/****** Object: Table [dbo].[alertTbl] Script Date: 09/22/2014 12:04:37 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[alertTbl]') AND type in (N'U'))
DROP TABLE [dbo].[alertTbl]
GO
EXECUTE sp_rename N'dbo.tmp_alertTbl', N'alertTbl', 'OBJECT'
GO
ALTER TABLE [dbo].[alertTbl] ADD CONSTRAINT [PK_alertTbl] PRIMARY KEY CLUSTERED
(
[alertId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE [dbo].[alertTbl] ADD CONSTRAINT [DF__alertTbl__timeCr__4D4C0586] DEFAULT (getutcdate()) FOR [timeCreated]
GO
ALTER TABLE [dbo].[alertTbl] ADD CONSTRAINT [DF__alertTbl__timeUp__4E4029BF] DEFAULT (getutcdate()) FOR [timeUpdated]
GO
ALTER TABLE [dbo].[alertTopicTbl] WITH CHECK ADD CONSTRAINT [FK_alertTopicTbl_alertTbl] FOREIGN KEY([alertId])
REFERENCES [dbo].[alertTbl] ([alertId])
GO
ALTER TABLE [dbo].[alertTopicTbl] CHECK CONSTRAINT [FK_alertTopicTbl_alertTbl]
GO
ALTER TABLE [dbo].[alertCategoryTbl] WITH CHECK ADD CONSTRAINT [FK_alertCategoryTbl_alertTbl] FOREIGN KEY([alertId])
REFERENCES [dbo].[alertTbl] ([alertId])
GO
ALTER TABLE [dbo].[alertCategoryTbl] CHECK CONSTRAINT [FK_alertCategoryTbl_alertTbl]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Link between alertTbl and alertCategoryTbl' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'alertCategoryTbl', @level2type=N'CONSTRAINT',@level2name=N'FK_alertCategoryTbl_alertTbl'
GO
GO
/****** Object: Table [dbo].[alertUsersTbl] Script Date: 09/22/2014 12:01:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp_alertUsersTbl](
[userLogin] [nvarchar](256) NOT NULL,
[alertId] [int] NOT NULL) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
GRANT SELECT ON dbo.[tmp_alertUsersTbl] TO Platform,utopy AS dbo
GO
GRANT INSERT ON dbo.[tmp_alertUsersTbl] TO utopy AS dbo
GO
GRANT DELETE ON dbo.[tmp_alertUsersTbl] TO utopy AS dbo
GO
IF EXISTS(SELECT * FROM dbo.alertUsersTbl)
EXEC('INSERT INTO dbo.[tmp_alertUsersTbl] (ID,[Text])
SELECT ID, CONVERT(nvarchar(256), Text) FROM dbo.alertUsersTbl WITH (HOLDLOCK TABLOCKX)')
GO
/****** Object: Table [dbo].[alertUsersTbl] Script Date: 09/22/2014 12:01:25 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[alertUsersTbl]') AND type in (N'U'))
DROP TABLE [dbo].[alertUsersTbl]
GO
EXECUTE sp_rename N'dbo.tmp_alertUsersTbl', N'alertUsersTbl', 'OBJECT'
ALTER TABLE alertUsersTbl ADD CONSTRAINT [PK__alertUsersTbl__5AA469F6] PRIMARY KEY CLUSTERED
(
[userLogin] ASC,
[alertId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[alertUsersTbl] Script Date: 09/22/2014 12:01:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp_alertUsersTbl](
[userLogin] [nvarchar](256) NOT NULL,
[alertId] [int] NOT NULL) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
GRANT SELECT ON dbo.[tmp_alertUsersTbl] TO Platform,utopy AS dbo
GO
GRANT INSERT ON dbo.[tmp_alertUsersTbl] TO utopy AS dbo
GO
GRANT DELETE ON dbo.[tmp_alertUsersTbl] TO utopy AS dbo
GO
IF EXISTS(SELECT * FROM dbo.alertUsersTbl)
EXEC('INSERT INTO dbo.[tmp_alertUsersTbl] (ID,[Text])
SELECT ID, CONVERT(nvarchar(256), Text) FROM dbo.alertUsersTbl WITH (HOLDLOCK TABLOCKX)')
GO
/****** Object: Table [dbo].[alertUsersTbl] Script Date: 09/22/2014 12:01:25 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[alertUsersTbl]') AND type in (N'U'))
DROP TABLE [dbo].[alertUsersTbl]
GO
EXECUTE sp_rename N'dbo.tmp_alertUsersTbl', N'alertUsersTbl', 'OBJECT'
ALTER TABLE alertUsersTbl ADD CONSTRAINT [PK__alertUsersTbl__5AA469F6] PRIMARY KEY CLUSTERED
(
[userLogin] ASC,
[alertId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_coachingSessionsTbl_coachingSessionNotesTbl]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingSessionNotes]'))
ALTER TABLE [dbo].[coachingSessionNotes] DROP CONSTRAINT [FK_coachingSessionsTbl_coachingSessionNotesTbl]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_coachingSessionNotes_timeCreated]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[coachingSessionNotes] DROP CONSTRAINT [DF_coachingSessionNotes_timeCreated]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_coachingSessionNotes_timeUpdated]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[coachingSessionNotes] DROP CONSTRAINT [DF_coachingSessionNotes_timeUpdated]
END
GO
/****** Object: Table [dbo].[coachingSessionNotes] Script Date: 09/18/2014 11:05:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp_coachingSessionNotes](
[ID] [int] IDENTITY(1000,1) NOT NULL,
[creator] [nvarchar](256) NOT NULL,
[timeCreated] [datetime] NOT NULL,
[timeUpdated] [datetime] NOT NULL,
[noteText] [nvarchar](max) NOT NULL,
[sessionId] [int] NOT NULL,
[permissions] [nvarchar](100) NOT NULL) ON [PRIMARY]
GO
ALTER TABLE dbo.[tmp_coachingSessionNotes] SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.[tmp_coachingSessionNotes] ON
GO
GRANT SELECT ON dbo.[tmp_coachingSessionNotes] TO reports,utopy AS dbo
GO
GRANT INSERT ON dbo.[tmp_coachingSessionNotes] TO utopy AS dbo
GO
GRANT DELETE ON dbo.[tmp_coachingSessionNotes] TO utopy,web AS dbo
GO
GRANT UPDATE ON dbo.[tmp_coachingSessionNotes] TO utopy AS dbo
go
GRANT UPDATE ON dbo.[tmp_coachingSessionNotes] TO web AS dbo
GO
IF EXISTS(SELECT * FROM dbo.coachingSessionNotes)
EXEC('INSERT INTO dbo.[tmp_coachingSessionNotes] (Id,creator,timeCreated,timeUpdated,noteText,sessionId,permissions)
SELECT Id,CONVERT(nvarchar(256), creator),timeCreated,timeUpdated, CONVERT(nvarchar(max), noteText), sessionId, CONVERT(nvarchar(100), permissions) FROM dbo.[coachingSessionNotes] WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.[tmp_coachingSessionNotes] OFF
GO
SET ANSI_PADDING ON
GO
/****** Object: Table [dbo].[coachingSessionNotes] Script Date: 09/18/2014 11:05:14 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[coachingSessionNotes]') AND type in (N'U'))
DROP TABLE [dbo].[coachingSessionNotes]
GO
EXECUTE sp_rename N'dbo.tmp_coachingSessionNotes', N'coachingSessionNotes', 'OBJECT'
go
ALTER TABLE [dbo].[coachingSessionNotes] ADD CONSTRAINT [PK_coachingSessionNotesTbl] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE [dbo].[coachingSessionNotes] WITH CHECK ADD CONSTRAINT [FK_coachingSessionsTbl_coachingSessionNotesTbl] FOREIGN KEY([sessionId])
REFERENCES [dbo].[coachingSessions] ([sessionId])
GO
ALTER TABLE [dbo].[coachingSessionNotes] CHECK CONSTRAINT [FK_coachingSessionsTbl_coachingSessionNotesTbl]
GO
ALTER TABLE [dbo].[coachingSessionNotes] ADD CONSTRAINT [DF_coachingSessionNotes_timeCreated] DEFAULT (getutcdate()) FOR [timeCreated]
GO
ALTER TABLE [dbo].[coachingSessionNotes] ADD CONSTRAINT [DF_coachingSessionNotes_timeUpdated] DEFAULT (getutcdate()) FOR [timeUpdated]
GO
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoachingSessionTypeTbl_coachingSessions]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingSessions]'))
ALTER TABLE [dbo].[coachingSessions] DROP CONSTRAINT [FK_CoachingSessionTypeTbl_coachingSessions]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_coachingSessions_timeCreated]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[coachingSessions] DROP CONSTRAINT [DF_coachingSessions_timeCreated]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_coachingSessions_timeUpdated]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[coachingSessions] DROP CONSTRAINT [DF_coachingSessions_timeUpdated]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_coachingSessions_accepted]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[coachingSessions] DROP CONSTRAINT [DF_coachingSessions_accepted]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__coachingS__isAct__642DD430]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[coachingSessions] DROP CONSTRAINT [DF__coachingS__isAct__642DD430]
END
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_coachingDynamicCallLists_coachingDynamicCallLists]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingDynamicCallLists]'))
ALTER TABLE [dbo].[coachingDynamicCallLists] DROP CONSTRAINT [FK_coachingDynamicCallLists_coachingDynamicCallLists]
GO
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_coachingSessionActionItems_coachingSessions]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingSessionActionItems]'))
ALTER TABLE [dbo].[coachingSessionActionItems] DROP CONSTRAINT [FK_coachingSessionActionItems_coachingSessions]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_coachingSessionsTbl_coachingSessionNotesTbl]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingSessionNotes]'))
ALTER TABLE [dbo].[coachingSessionNotes] DROP CONSTRAINT [FK_coachingSessionsTbl_coachingSessionNotesTbl]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_coachingSessionResources_coachingSessions]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingSessionResources]'))
ALTER TABLE [dbo].[coachingSessionResources] DROP CONSTRAINT [FK_coachingSessionResources_coachingSessions]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_coachingStaticCallLists_coachingSessions]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingStaticCallLists]'))
ALTER TABLE [dbo].[coachingStaticCallLists] DROP CONSTRAINT [FK_coachingStaticCallLists_coachingSessions]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FormsAnsweredTbl_coachingSessions]') AND parent_object_id = OBJECT_ID(N'[dbo].[FormsAnsweredTbl]'))
ALTER TABLE [dbo].[FormsAnsweredTbl] DROP CONSTRAINT [FK_FormsAnsweredTbl_coachingSessions]
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[userMessagesView]'))
DROP VIEW [dbo].[userMessagesView]
GO
CREATE TABLE [dbo].[tmp_coachingSessions](
[sessionId] [int] IDENTITY(1000,1) NOT NULL,
[name] [nvarchar](256) NULL,
[creator] [nvarchar](256) NOT NULL,
[coach] [nvarchar](256) NULL,
[trainee] [nvarchar](256) NULL,
[dueDate] [datetime] NULL,
[dueEnd] [datetime] NULL,
[status] [nvarchar](20) NOT NULL,
[ScheduleStart] [datetime] NULL,
[ScheduleEnd] [datetime] NULL,
[duration] [float] NULL,
[timeCreated] [datetime] NOT NULL,
[timeUpdated] [datetime] NOT NULL,
[accepted] [bit] NOT NULL,
[sessionType] [int] NULL,
[isActive] [bit] NOT NULL,
[ScheduleLastStart] [datetime] NULL) ON [PRIMARY]
SET ANSI_PADDING ON
GO
ALTER TABLE dbo.tmp_coachingSessions SET (LOCK_ESCALATION = TABLE)
GO
GO
GRANT SELECT ON dbo.tmp_coachingSessions TO Web,reports,utopy AS dbo
GO
GRANT INSERT ON dbo.tmp_coachingSessions TO utopy AS dbo
GO
GRANT DELETE ON dbo.tmp_coachingSessions TO utopy,web AS dbo
GO
GRANT UPDATE ON dbo.tmp_coachingSessions TO utopy AS dbo
GO
GRANT VIEW DEFINITION ON dbo.tmp_coachingSessions TO utopy AS dbo
GO
SET IDENTITY_INSERT dbo.tmp_coachingSessions ON
GO
IF EXISTS(SELECT * FROM dbo.coachingSessions)
EXEC('INSERT INTO dbo.tmp_coachingSessions ([sessionId],[name],[creator],[coach],[trainee],[dueDate],[dueEnd],[status],[ScheduleStart],[ScheduleEnd],[duration],[timeCreated],[timeUpdated],[accepted],[sessionType],[isActive],[ScheduleLastStart])
SELECT sessionId, CONVERT(nvarchar(256), name),CONVERT(nvarchar(256), creator),CONVERT(nvarchar(256), coach),CONVERT(nvarchar(256), trainee),dueDate,dueEnd,CONVERT(nvarchar(20), status),ScheduleStart,ScheduleEnd,duration,timeCreated,timeUpdated,accepted,sessionType,isActive,ScheduleLastStart FROM dbo.coachingSessions WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.tmp_coachingSessions OFF
GO
/****** Object: Table [dbo].[coachingSessions] Script Date: 09/18/2014 07:16:34 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[coachingSessions]') AND type in (N'U'))
DROP TABLE [dbo].[coachingSessions]
GO
EXECUTE sp_rename N'dbo.tmp_coachingSessions', N'coachingSessions', 'OBJECT'
GO
ALTER TABLE [coachingSessions] ADD CONSTRAINT [PK_coachingSessionsTbl] PRIMARY KEY CLUSTERED
(
[sessionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[coachingSessions] WITH CHECK ADD CONSTRAINT [FK_CoachingSessionTypeTbl_coachingSessions] FOREIGN KEY([sessionType])
REFERENCES [dbo].[CoachingSessionTypeTbl] ([TypeID])
GO
ALTER TABLE [dbo].[coachingSessions] CHECK CONSTRAINT [FK_CoachingSessionTypeTbl_coachingSessions]
GO
ALTER TABLE [dbo].[coachingSessions] ADD CONSTRAINT [DF_coachingSessions_timeCreated] DEFAULT (getutcdate()) FOR [timeCreated]
GO
ALTER TABLE [dbo].[coachingSessions] ADD CONSTRAINT [DF_coachingSessions_timeUpdated] DEFAULT (getutcdate()) FOR [timeUpdated]
GO
ALTER TABLE [dbo].[coachingSessions] ADD CONSTRAINT [DF_coachingSessions_accepted] DEFAULT ((0)) FOR [accepted]
GO
ALTER TABLE [dbo].[coachingSessions] ADD CONSTRAINT [DF__coachingS__isAct__642DD430] DEFAULT ((1)) FOR [isActive]
GO
ALTER TABLE [dbo].[coachingDynamicCallLists] WITH CHECK ADD CONSTRAINT [FK_coachingDynamicCallLists_coachingDynamicCallLists] FOREIGN KEY([sessionId])
REFERENCES [dbo].[coachingSessions] ([sessionId])
GO
ALTER TABLE [dbo].[coachingDynamicCallLists] CHECK CONSTRAINT [FK_coachingDynamicCallLists_coachingDynamicCallLists]
GO
ALTER TABLE [dbo].[coachingSessionActionItems] WITH CHECK ADD CONSTRAINT [FK_coachingSessionActionItems_coachingSessions] FOREIGN KEY([sessionID])
REFERENCES [dbo].[coachingSessions] ([sessionId])
GO
ALTER TABLE [dbo].[coachingSessionActionItems] CHECK CONSTRAINT [FK_coachingSessionActionItems_coachingSessions]
GO
ALTER TABLE [dbo].[coachingSessionNotes] WITH CHECK ADD CONSTRAINT [FK_coachingSessionsTbl_coachingSessionNotesTbl] FOREIGN KEY([sessionId])
REFERENCES [dbo].[coachingSessions] ([sessionId])
GO
ALTER TABLE [dbo].[coachingSessionNotes] CHECK CONSTRAINT [FK_coachingSessionsTbl_coachingSessionNotesTbl]
GO
ALTER TABLE [dbo].[coachingSessionResources] WITH CHECK ADD CONSTRAINT [FK_coachingSessionResources_coachingSessions] FOREIGN KEY([sessionID])
REFERENCES [dbo].[coachingSessions] ([sessionId])
GO
ALTER TABLE [dbo].[coachingSessionResources] CHECK CONSTRAINT [FK_coachingSessionResources_coachingSessions]
GO
ALTER TABLE [dbo].[coachingStaticCallLists] WITH CHECK ADD CONSTRAINT [FK_coachingStaticCallLists_coachingSessions] FOREIGN KEY([sessionId])
REFERENCES [dbo].[coachingSessions] ([sessionId])
GO
ALTER TABLE [dbo].[coachingStaticCallLists] CHECK CONSTRAINT [FK_coachingStaticCallLists_coachingSessions]
GO
GO
ALTER TABLE [dbo].[FormsAnsweredTbl] WITH CHECK ADD CONSTRAINT [FK_FormsAnsweredTbl_coachingSessions] FOREIGN KEY([CoachingSessionId])
REFERENCES [dbo].[coachingSessions] ([sessionId])
GO
ALTER TABLE [dbo].[FormsAnsweredTbl] CHECK CONSTRAINT [FK_FormsAnsweredTbl_coachingSessions]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
create VIEW [dbo].[userMessagesView]
AS
SELECT cft.callForwardTaskID AS taskId, dbo.utc2localTime(dbo.tod2time(cft.creationDate)) AS date, cft.creator AS 'from', cft.subject, 2 AS type,
1 AS subType, '' as permaLink, 0 AS action, cfts.recipient AS userLogin, cft.callid as callid, cft.externalId as externalId
FROM dbo.callForwardTasksTbl AS cft INNER JOIN
dbo.callForwardTasksStatusTbl AS cfts ON cfts.callForwardTaskID = cft.callForwardTaskID
WHERE (cfts.notificationMessageDeleted = 0)
UNION
SELECT sessionId AS taskId, dbo.utc2localTime(timeCreated) AS date, creator AS 'from', name AS subject, 1 AS type, 0 AS subType, '' AS permalink, 0 AS action,
trainee AS userLogin, -1 as callid, '' as externalId
FROM dbo.coachingSessions AS cs
WHERE (accepted = 0)
UNION
SELECT noteId AS taskId, dbo.utc2localTime(creationDate) AS date, creator AS 'from', subject, 3 AS type, 0 AS subType, '' AS permalink,
0 AS action, recipient AS userLogin, -1 as callid, '' as externalId
FROM dbo.notesTbl AS nt
WHERE (messageNotificationDeleted = 0)
GO
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_coachingResourceViews_coachingSessionResources]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingResourceViews]'))
ALTER TABLE [dbo].[coachingResourceViews] DROP CONSTRAINT [FK_coachingResourceViews_coachingSessionResources]
GO
CREATE TABLE [dbo].[TMP_coachingResourceViews](
[id] [int] IDENTITY(1,1) NOT NULL,
[resourceID] [int] NOT NULL,
[resourceViewer] [Nvarchar](256) NOT NULL,
[timestamp] [datetime] NOT NULL) ON [PRIMARY]
GO
ALTER TABLE dbo.[TMP_coachingResourceViews] SET (LOCK_ESCALATION = TABLE)
GO
GO
GRANT SELECT ON dbo.[TMP_coachingResourceViews] TO reports,utopy AS dbo
GO
GRANT INSERT ON dbo.[TMP_coachingResourceViews] TO utopy AS dbo
GO
GRANT DELETE ON dbo.[TMP_coachingResourceViews] TO utopy AS dbo
GO
SET IDENTITY_INSERT dbo.[TMP_coachingResourceViews] ON
GO
IF EXISTS(SELECT * FROM dbo.[coachingResourceViews])
EXEC('INSERT INTO dbo.[TMP_coachingResourceViews] (Id,resourceID,resourceViewer,timestamp)
SELECT Id,resourceID, CONVERT(nvarchar(50), resourceViewer), timestamp FROM dbo.[coachingResourceViews] WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.[TMP_coachingResourceViews] OFF
GO
/****** Object: Table [dbo].[coachingResourceViews] Script Date: 09/18/2014 10:54:34 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[coachingResourceViews]') AND type in (N'U'))
DROP TABLE [dbo].[coachingResourceViews]
GO
GO
EXECUTE sp_rename N'dbo.TMP_coachingResourceViews', N'coachingResourceViews', 'OBJECT'
ALTER TABLE coachingResourceViews ADD CONSTRAINT [PK_coachingResourceViews] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_coachingSessionResources_coachingSessions]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingSessionResources]'))
ALTER TABLE [dbo].[coachingSessionResources] DROP CONSTRAINT [FK_coachingSessionResources_coachingSessions]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp_coachingSessionResources](
[resourceID] [int] IDENTITY(1,1) NOT NULL,
[sessionID] [int] NOT NULL,
[description] [varchar](256) NOT NULL,
[resourceType] [int] NOT NULL,
[linkPath] [varchar](2000) NOT NULL,
[IsActive] [bit] NOT NULL,
[creator] [varchar](256) NOT NULL,
[timeCreated] [datetime] NOT NULL) ON [PRIMARY]
GO
ALTER TABLE dbo.[tmp_coachingSessionResources] SET (LOCK_ESCALATION = TABLE)
GO
GO
GRANT SELECT ON dbo.[tmp_coachingSessionResources] TO utopy AS dbo
GO
GRANT INSERT ON dbo.[tmp_coachingSessionResources] TO utopy AS dbo
GO
GRANT DELETE ON dbo.[tmp_coachingSessionResources] TO utopy,web AS dbo
GO
GRANT UPDATE ON dbo.[tmp_coachingSessionResources] TO utopy AS dbo
GO
SET IDENTITY_INSERT dbo.[tmp_coachingSessionResources] ON
GO
IF EXISTS(SELECT * FROM dbo.[coachingSessionResources])
EXEC('INSERT INTO dbo.[tmp_coachingSessionResources] (resourceID,sessionID,description, resourceType,linkPath,IsActive,creator,timeCreated)
SELECT resourceID,sessionID, CONVERT(nvarchar(256), description), resourceType,linkPath,IsActive, CONVERT(nvarchar(256), creator),timeCreated FROM dbo.[https://docs.genesys.com/Documentation/SPMI/8.5.3/Upgrade/tmp_coachingSessionResources tmp_coachingSessionResources] WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.[tmp_coachingSessionResources] OFF
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[coachingSessionResources]') AND type in (N'U'))
DROP TABLE [dbo].[coachingSessionResources]
GO
EXECUTE sp_rename N'dbo.[tmp_coachingSessionResources]', N'coachingSessionResources', 'OBJECT'
ALTER TABLE [coachingSessionResources] ADD CONSTRAINT [PK_coachingSessionResources] PRIMARY KEY CLUSTERED
(
[resourceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[coachingSessionResources] WITH CHECK ADD CONSTRAINT [FK_coachingSessionResources_coachingSessions] FOREIGN KEY([sessionID])
REFERENCES [dbo].[coachingSessions] ([sessionId])
GO
ALTER TABLE [dbo].[coachingSessionResources] CHECK CONSTRAINT [FK_coachingSessionResources_coachingSessions]
GO
ALTER TABLE [dbo].[coachingResourceViews] WITH CHECK ADD CONSTRAINT [FK_coachingResourceViews_coachingSessionResources] FOREIGN KEY([resourceID])
REFERENCES [dbo].[coachingSessionResources] ([resourceID])
GO
ALTER TABLE [dbo].[coachingResourceViews] CHECK CONSTRAINT [FK_coachingResourceViews_coachingSessionResources]
GO
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_QMQueuesTbl_searchTbl]') AND parent_object_id = OBJECT_ID(N'[dbo].[QMQueuesTbl]'))
ALTER TABLE [dbo].[QMQueuesTbl] DROP CONSTRAINT [FK_QMQueuesTbl_searchTbl]
GO
/****** Object: Table [dbo].[searchTbl] Script Date: 09/18/2014 13:16:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp_searchTbl](
[searchId] [int] NOT NULL,
[searchName] [nvarchar](50) NULL,
[searchFilter] [xml] NOT NULL,
[stopCondition] [xml] NOT NULL,
[isActive] [bit] NOT NULL,
[createdBy] [nvarchar](256) NOT NULL,
[belongsToGroups] [xml] NOT NULL,
[sharedWithUsers] [xml] NULL,
[creationTime] [datetime] NOT NULL,
[processCalls] [bit] NOT NULL,
[isDeactivating] [bit] NOT NULL,
[grammarName] [nvarchar](max) NULL,
[searchType] [int] NULL) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
GO
ALTER TABLE dbo.tmp_searchTbl SET (LOCK_ESCALATION = TABLE)
GO
GO
GRANT SELECT ON dbo.tmp_searchTbl TO utopy,Platform,web AS dbo
GO
GRANT INSERT ON dbo.tmp_searchTbl TO Platform,web AS dbo
GO
GRANT DELETE ON dbo.tmp_searchTbl TO utopy,web,Platform,web AS dbo
GO
GRANT UPDATE ON dbo.tmp_searchTbl TO Platform ,web AS dbo
go
IF EXISTS(SELECT * FROM dbo.searchTbl)
EXEC('INSERT INTO dbo.tmp_searchTbl ([searchId],[searchName],[searchFilter],[stopCondition],[isActive],[createdBy],[belongsToGroups],[sharedWithUsers],[creationTime],[processCalls],[isDeactivating],[grammarName],[searchType])
SELECT [searchId],CONVERT(nvarchar(50), searchName),[searchFilter],[stopCondition],[isActive],CONVERT(nvarchar(256), createdBy),[belongsToGroups],[sharedWithUsers],[creationTime],[processCalls],[isDeactivating],CONVERT(nvarchar(256), grammarName),[searchType] FROM dbo.searchTbl WITH (HOLDLOCK TABLOCKX)')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[searchTbl]') AND type in (N'U'))
DROP TABLE [dbo].[searchTbl]
GO
EXECUTE sp_rename N'dbo.tmp_searchTbl', N'searchTbl', 'OBJECT'
ALTER TABLE [searchTbl] ADD CONSTRAINT [PK_searchTbl] PRIMARY KEY CLUSTERED
(
[searchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
/****** Object: Table [dbo].[searchTbl] Script Date: 09/18/2014 13:16:10 ******/
ALTER TABLE [dbo].[QMQueuesTbl] WITH CHECK ADD CONSTRAINT [FK_QMQueuesTbl_searchTbl] FOREIGN KEY([searchID])
REFERENCES [dbo].[searchTbl] ([searchId])
GO
ALTER TABLE [dbo].[QMQueuesTbl] CHECK CONSTRAINT [FK_QMQueuesTbl_searchTbl]
GO
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_QMquotaParametersTbl_QMParameterTypeTbl]') AND parent_object_id = OBJECT_ID(N'[dbo].[QMquotaParametersTbl]'))
ALTER TABLE [dbo].[QMquotaParametersTbl] DROP CONSTRAINT [FK_QMquotaParametersTbl_QMParameterTypeTbl]
GO
/****** Object: Table [dbo].[QMParameterTypeTbl] Script Date: 09/18/2014 14:14:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp_QMParameterTypeTbl](
[ParameterTypeID] [int] NOT NULL,
[name] [nvarchar](256) NOT NULL,
[possibleValues] [xml] NULL,
[ParameterDisplayType] [int] NOT NULL) ON [PRIMARY]
GO
ALTER TABLE dbo.[tmp_QMParameterTypeTbl] SET (LOCK_ESCALATION = TABLE)
GO
GRANT SELECT ON dbo.[tmp_QMParameterTypeTbl] TO utopy AS dbo
GO
IF EXISTS(SELECT * FROM dbo.QMParameterTypeTbl)
EXEC('INSERT INTO dbo.[tmp_QMParameterTypeTbl] (ParameterTypeID,[name],[possibleValues],[ParameterDisplayType])
SELECT ParameterTypeID, CONVERT(nvarchar(256), name), possibleValues,ParameterDisplayType FROM dbo.[QMParameterTypeTbl] WITH (HOLDLOCK TABLOCKX)')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QMParameterTypeTbl]') AND type in (N'U'))
DROP TABLE [dbo].[QMParameterTypeTbl]
GO
EXECUTE sp_rename N'dbo.tmp_QMParameterTypeTbl', N'QMParameterTypeTbl', 'OBJECT'
ALTER TABLE QMParameterTypeTbl ADD CONSTRAINT [PK_QMQuotaParameterTypeTbl] PRIMARY KEY CLUSTERED
(
[ParameterTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE [dbo].[QMquotaParametersTbl] WITH CHECK ADD CONSTRAINT [FK_QMquotaParametersTbl_QMParameterTypeTbl] FOREIGN KEY([parameterType])
REFERENCES [dbo].[QMParameterTypeTbl] ([ParameterTypeID])
GO
ALTER TABLE [dbo].[QMquotaParametersTbl] CHECK CONSTRAINT [FK_QMquotaParametersTbl_QMParameterTypeTbl]
GO
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_QMQueuesTbl_searchTbl]') AND parent_object_id = OBJECT_ID(N'[dbo].[QMQueuesTbl]'))
ALTER TABLE [dbo].[QMQueuesTbl] DROP CONSTRAINT [FK_QMQueuesTbl_searchTbl]
GO
/****** Object: Table [dbo].[QMQueuesTbl] Script Date: 09/22/2014 09:14:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp_QMQueuesTbl](
[queueID] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](200) NOT NULL,
[description] [nvarchar](1000) NULL,
[quotaType] [int] NOT NULL,
[searchID] [int] NOT NULL,
[isActive] [bit] NOT NULL,
[creator] [nvarchar](256) NOT NULL,
[binaryData] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET IDENTITY_INSERT dbo.[tmp_QMQueuesTbl] ON
GO
ALTER TABLE dbo.[tmp_QMQueuesTbl] SET (LOCK_ESCALATION = TABLE)
GO
GRANT SELECT ON dbo.[tmp_QMQueuesTbl] TO Web,utopy AS dbo
GO
GRANT INSERT ON dbo.[tmp_QMQueuesTbl] TO utopy AS dbo
GO
GRANT DELETE ON dbo.[tmp_QMQueuesTbl] TO utopy AS dbo
GO
GRANT UPDATE ON dbo.[tmp_QMQueuesTbl] TO utopy AS dbo
GO
GRANT VIEW DEFINITION ON dbo.[tmp_QMQueuesTbl] TO utopy AS dbo
GO
IF EXISTS(SELECT * FROM [dbo].QMQueuesTbl)
EXEC('INSERT INTO dbo.[tmp_QMQueuesTbl] (queueID,[name],[description],[quotaType],[searchID],[isActive],[creator],[binaryData])
SELECT queueID, CONVERT(nvarchar(200), name), CONVERT(nvarchar(1000), description),[quotaType],[searchID],[isActive],CONVERT(nvarchar(1000), creator),[binaryData] FROM dbo.[QMQueuesTbl] WITH (HOLDLOCK TABLOCKX)')
GO
GO
SET IDENTITY_INSERT dbo.[tmp_QMQueuesTbl] OFF
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QMQueuesTbl]') AND type in (N'U'))
DROP TABLE [dbo].[QMQueuesTbl]
GO
EXECUTE sp_rename N'dbo.tmp_QMQueuesTbl', N'QMQueuesTbl', 'OBJECT'
GO
ALTER TABLE [dbo].[QMQueuesTbl] ADD CONSTRAINT [PK_QMQueuesTbl] PRIMARY KEY CLUSTERED
(
[queueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE [dbo].[QMQueuesTbl] WITH CHECK ADD CONSTRAINT [FK_QMQueuesTbl_searchTbl] FOREIGN KEY([searchID])
REFERENCES [dbo].[searchTbl] ([searchId])
GO
ALTER TABLE [dbo].[QMQueuesTbl] CHECK CONSTRAINT [FK_QMQueuesTbl_searchTbl]
GO
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_QMquotaParametersTbl_QMQuotaTypeTbl]') AND parent_object_id = OBJECT_ID(N'[dbo].[QMquotaParametersTbl]'))
ALTER TABLE [dbo].[QMquotaParametersTbl] DROP CONSTRAINT [FK_QMquotaParametersTbl_QMQuotaTypeTbl]
GO
/****** Object: Table [dbo].[QMQuotaTypeTbl] Script Date: 09/21/2014 11:05:41 ******/
/****** Object: Table [dbo].[QMQuotaTypeTbl] Script Date: 09/21/2014 11:05:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp_QMQuotaTypeTbl](
[quotaTypeID] [int] NOT NULL,
[name] [nvarchar](256) NOT NULL,
[quotaPhrase] [nvarchar](1000) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE dbo.[tmp_QMQuotaTypeTbl] SET (LOCK_ESCALATION = TABLE)
GO
GRANT SELECT ON dbo.[tmp_QMQuotaTypeTbl] TO utopy AS dbo
GO
IF EXISTS(SELECT * FROM [dbo].[QMQuotaTypeTbl])
EXEC('INSERT INTO dbo.[tmp_QMQuotaTypeTbl] (quotaTypeID,[name],quotaPhrase)
SELECT quotaTypeID, CONVERT(nvarchar(256), name), CONVERT(nvarchar(256), quotaPhrase) FROM dbo.[QMQuotaTypeTbl] WITH (HOLDLOCK TABLOCKX)')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QMQuotaTypeTbl]') AND type in (N'U'))
DROP TABLE [dbo].[QMQuotaTypeTbl]
GO
EXECUTE sp_rename N'dbo.tmp_QMQuotaTypeTbl', N'QMQuotaTypeTbl', 'OBJECT'
ALTER TABLE [dbo].QMQuotaTypeTbl add CONSTRAINT [PK_QMQuotaType] PRIMARY KEY CLUSTERED
(
[quotaTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Go
ALTER TABLE [dbo].[QMquotaParametersTbl] WITH CHECK ADD CONSTRAINT [FK_QMquotaParametersTbl_QMQuotaTypeTbl] FOREIGN KEY([quotaType])
REFERENCES [dbo].[QMQuotaTypeTbl] ([quotaTypeID])
GO
ALTER TABLE [dbo].[QMquotaParametersTbl] CHECK CONSTRAINT [FK_QMquotaParametersTbl_QMQuotaTypeTbl]
GO
GO
update webserviceparams set luceneMaxClauseCount=10000
GO
GO
ALTER TABLE webServiceParams ADD resetPasswordMailSubject varchar(max) NOT NULL DEFAULT('Reset SpeechMiner password')
ALTER TABLE webServiceParams ADD resetPasswordMailBody varchar(max) NOT NULL DEFAULT('Reset your password at <resetLink>')
ALTER TABLE webServiceParams ADD resetPasswordTokenExpirationTime int NOT NULL DEFAULT(4)
ALTER TABLE webServiceParams ADD PasswordRecovery bit NOT NULL DEFAULT(0)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[userResetToken](
[UserName] varchar(256) NOT NULL,
[ResetToken] varchar(256) NOT NULL,
[expirationTime] int NOT NULL)
GO
SET ANSI_PADDING ON
GO
GRANT UPDATE ON [dbo].[userResetToken] TO Web AS dbo
GRANT INSERT ON [dbo].[userResetToken] TO Web AS dbo
GRANT DELETE ON [dbo].[userResetToken] TO Web AS dbo
GRANT SELECT ON [dbo].[userResetToken] TO Web AS dbo
GO
update wildcardGrammars set regularExpression='(?<![\w*])((\d|0\d|1[0-2]|\*?\*):([0-5]\d|\*\*)(:[0-5]\d|\*\*)?\s?(a|p)m|((\d|0\d|1\d|2[0-3]|\*?\*):([0-5]\d|\*\*)(:[0-5]\d|\*\*)?(?!(\s?(a|p)m))))(?![\w*])' where regularExpression is not null and token='[time]'
update wildcardGrammars set regularExpression='(?<![\w*])((0?[1-9]|1[012]|\*?\*)([- /.](2[89]|3[01])|\*\*)?[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(januari|februari|march|april|may|june|juli|august|septemb|octob|novemb|decemb)\s(of\s)?(19|20|21|\*\*)(\d\d|\*\*))(?![\w*])' where languageId=0 and token='[ccexpdate]'
update wildcardGrammars set regularExpression='(?<![\w*])(-?(\*|\d){1,3}(,?((\*|\d){3})){0,2}(\.(\*|\d){1,2})?(\smillion)?\s(dollar|cent|buck))(\sand\s(\*|\d){1,2}\scent)?(?![\w*])' where languageId=0 and token='[currency]'
update wildcardGrammars set regularExpression='(?<![\w*])((januari|februari|march|april|may|june|juli|august|septemb|octob|novemb|decemb)\s(0?[1-9]|[12][0-9]|3[01]|\*?\*),?\s(19|20|21|\*\*)(\d\d|\*\*)|(19|20|21|\*\*)?(\d\d|\*\*)([- /.])(0?[1-9]|1[012]|\*?\*)([- /.])(0?[1-9]|[12][0-9]|3[01]|\*?\*)|(0?[1-9]|1[012]|\*?\*)[- /.](0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](0?[1-9]|1[012]|\*?|\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|today|tomorrow|yesterday)(?![\w*])' where languageId=0 and token='[date]'
update wildcardGrammars set regularExpression='\b(si|no|correct)\b' where languageId=1 and token='[boolean]'
update wildcardGrammars set regularExpression='(?<![\w*])((0?[1-9]|1[012]|\*?\*)([- /.](2[89]|3[01]|\*\*))?[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(ener|febrer|marz|abril|may|juni|juli|agost|septiembr|octubr|noviembr|diciembr)\s(de\s)?(19|20|21|\*\*)(\d\d|\*\*))(?![\w*])' where languageId=1 and token='[ccexpdate]'
update wildcardGrammars set regularExpression='(?<![\w*])(-?(\*|\d){1,3}(,?((\*|\d){3})){0,2}(\.(\*|\d){1,2})?(\smillon)?\s(dolar|pes|centav))(\s(y|con)\s(\*|\d){1,2}\scentav)?(?![\w*])' where languageId=1 and token='[currency]'
update wildcardGrammars set regularExpression='(?<![\w*])((ener|febrer|marz|abril|may|juni|juli|agost|septiembr|octubr|noviembr|diciembr)\s(0?[1-9]|[12][0-9]|3[01]|\*?\*),?\s(19|20|21|\*\*)(\d\d|\*\*)|(19|20|21|\*\*)?(\d\d|\*\*)([- /.])(0?[1-9]|1[012]|\*?\*)([- /.])(0?[1-9]|[12][0-9]|3[01]|\*?\*)|(0?[1-9]|1[012]|\*?\*)[- /.](0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](0?[1-9]|1[012]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|hoy|mañan|ayer)(?![\w*])' where languageId=1 and token='[date]'
update wildcardGrammars set regularExpression='\b(si|no|correct)\b' where languageId=2 and token='[boolean]'
update wildcardGrammars set regularExpression='(?<![\w*])((0?[1-9]|1[012]|\*?\*)([- /.](2[89]|3[01]|\*\*))?[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(ener|febrer|marz|abril|may|juni|juli|agost|septiembr|octubr|noviembr|diciembr)\s(de\s)?(19|20|21|\*\*)(\d\d|\*\*))(?![\w*])' where languageId=2 and token='[ccexpdate]'
update wildcardGrammars set regularExpression='(?<![\w*])(-?(\*|\d){1,3}(,?((\*|\d){3})){0,2}(\.(\*|\d){1,2})?(\smillon)?\s(eur|peset|cent))(\s(y|con)\s(\*|\d){1,2}\scent)?(?![\w*])' where languageId=2 and token='[currency]'
update wildcardGrammars set regularExpression='(?<![\w*])((ener|febrer|marz|abril|may|juni|juli|agost|septiembr|octubr|noviembr|diciembr)\s(0?[1-9]|[12][0-9]|3[01]|\*?\*),?\s(19|20|21|\*\*)(\d\d|\*\*)|(19|20|21|\*\*)?(\d\d|\*\*)([- /.])(0?[1-9]|1[012]|\*?\*)([- /.])(0?[1-9]|[12][0-9]|3[01]|\*?\*)|(0?[1-9]|1[012]|\*?\*)[- /.](0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](0?[1-9]|1[012]|\*?\*)[- /.](19|20|21\*\*)?(\d\d|\*\*)|hoy|mañan|ayer)(?![\w*])' where languageId=2 and token='[date]'
update wildcardGrammars set regularExpression='(?<![\w*])((0?[1-9]|1[012]|\*?\*)([- /.](2[89]|3[01]|\*\*))?[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(januari|februari|march|april|may|june|juli|august|septemb|octob|novemb|decemb)\s(of\s)?(19|20|21|\*\*)(\d\d|\*\*))\b' where languageId=3 and token='[ccexpdate]'
update wildcardGrammars set regularExpression='(?<![\w*])(-?(\*|\d){1,3}(,?((\*|\d){3})){0,2}(\.(\*|\d){1,2})?(\smillion)?\s(pound|penc))(\sand\s(\*|\d){1,2}\spenc)?\b' where languageId=3 and token='[currency]'
update wildcardGrammars set regularExpression='(?<![\w*])((januari|februari|march|april|may|june|juli|august|septemb|octob|novemb|decemb)\s(0?[1-9]|[12][0-9]|3[01]|\*?\*),?\s(19|20|21|\*\*)(\d\d|\*\*)|(19|20|21|\*\*)?(\d\d|\*\*)([- /.])(0?[1-9]|1[012]|\*?\*)([- /.])(0?[1-9]|[12][0-9]|3[01]|\*?\*)|(0?[1-9]|1[012]|\*?\*)[- /.](0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](0?[1-9]|1[012]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|today|tomorrow|yesterday)(?![\w*])' where languageId=3 and token='[date]'
update wildcardGrammars set regularExpression='\b(oui|non|correct)\b' where languageId=5 and token='[boolean]'
update wildcardGrammars set regularExpression='(?<![\w*])((0?[1-9]|1[012]|\*?\*)([- /.](2[89]|3[01]|\*\*))?[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(janvi|févri|mar|avril|mai|juin|juillet|août|septembr|octobr|novembr|décembr)\s(de\s)?(19|20|21|\*\*)(\d\d|\*\*))(?![\w*])' where languageId=5 and token='[ccexpdate]'
update wildcardGrammars set regularExpression='(?<![\w*])(-?(\*|\d){1,3}(,?((\*|\d){3})){0,2}(\.(\*|\d){1,2})?(\smillion)?\s(euro(s?)|dollar|cent))(\set\s(\*|\d){1,2}\scent)?(?![\w*])' where languageId=5 and token='[currency]'
update wildcardGrammars set regularExpression='(?<![\w*])((janvi|févri|mar|avril|mai|juin|juillet|août|septembr|octobr|novembr|décembr)\s(0?[1-9]|[12][0-9]|3[01]|\*?\*),?\s(19|20|21|\*\*)(\d\d|\*\*)|(19|20|21|\*\*)?(\d\d|\*\*)([- /.])(0?[1-9]|1[012]|\*?\*)([- /.])(0?[1-9]|[12][0-9]|3[01]|\*?\*)|(0?[1-9]|1[012]|\*?\*)[- /.](0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](0?[1-9]|1[012]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|aujourd''hui|demain|hi)(?![\w*])' where languageId=5 and token='[date]'
update wildcardGrammars set regularExpression='(?<![\w*])(-?(\*|\d){1,3}(,?((\*|\d){3})){0,2}(\.(\*|\d){1,2})?(\smillion)?\s(euro(s?)|dollar|cent))(\sund\s(\*|\d){1,2}\scent)?(?![\w*])' where languageId=6 and token='[currency]'
update wildcardGrammars set regularExpression='(?<![\w*])((januar|februar|marz|april|mai|juno|juni|julei|juli|august|septemb|oktob|novemb|dezemb)\s(0?[1-9]|[12][0-9]|3[01]|\*?\*),?\s(19|20|21|\*\*)(\d\d|\*\*)|(19|20|21|\*\*)?(\d\d|\*\*)([- /.])(0?[1-9]|1[012]|\*?\*)([- /.])(0?[1-9]|[12][0-9]|3[01]|\*?\*)|(0?[1-9]|1[012]|\*?\*)[- /.](0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](0?[1-9]|1[012]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|heut|morg|gest)(?![\w*])' where languageId=6 and token='[date]'
update wildcardGrammars set regularExpression='\b(sim|nã)\b' where languageId=11 and token='[boolean]'
update wildcardGrammars set regularExpression='(?<![\w*])((0?[1-9]|1[012]|\*?\*)([- /.](2[89]|3[01]|\*\*))?[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(janeir|fevereir|marc|abril|mai|junh|julh|agost|setembr|outubr|novembr|dezembr)\s(de\s)?(19|20|21|\*\*)(\d\d|\*\*))(?![\w*])' where languageId=11 and token='[ccexpdate]'
update wildcardGrammars set regularExpression='(?<![\w*])(-?(\*|\d){1,3}(,?((\*|\d){3})){0,2}(\.(\*|\d){1,2})?(\smilhã)?\s(escud|dól|centav|cént|céntim))(\se\s(\*|\d){1,2}\s(centav|cént|céntim))?(?![\w*])' where languageId=11 and token='[currency]'
update wildcardGrammars set regularExpression='(?<![\w*])((janeir|fevereir|marc|abril|mai|junh|julh|agost|setembr|outubr|novembr|dezembr)\s(0?[1-9]|[12][0-9]|3[01]|\*?\*),?\s(19|20|21|\*\*)(\d\d|\*\*)|(19|20|21|\*\*)?(\d\d|\*\*)([- /.])(0?[1-9]|1[012]|\*?\*)([- /.])(0?[1-9]|[12][0-9]|3[01]|\*?\*)|(0?[1-9]|1[012]|\*?\*)[- /.](0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](0?[1-9]|1[012]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|hoj|amanhã|ontem)(?![\w*])' where languageId=11 and token='[date]'
update wildcardGrammars set regularExpression='(?<![\w*])(-?(\*|\d){1,3}(,?((\*|\d){3})){0,2}(\.(\*|\d){1,2})?(\smilhã)?)(?![\w*])' where languageId=11 and token='[number]'
update wildcardGrammars set regularExpression='\b(si|no)\b' where languageId=13 and token='[boolean]'
update wildcardGrammars set regularExpression='(?<![\w*])((0?[1-9]|1[012]|\*?\*)([- /.](2[89]|3[01]|\*\*))?[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(januari|februari|march|april|may|june|juli|august|septemb|octob|novemb|decemb)\s(of\s)?(19|20|21|\*\*)(\d\d|\*\*))(?![\w*])' where languageId=14 and token='[ccexpdate]'
update wildcardGrammars set regularExpression='(?<![\w*])(\*|\d){1,9}(\.(\*|\d){1,2})?(\smillion)?\s(dollar|cent|buck)(\sand\s((\*|\d){1,2})\scent)?(?![\w*])' where languageId=14 and token='[currency]'
update wildcardGrammars set regularExpression='(?<![\w*])((januari|februari|march|april|may|june|juli|august|septemb|octob|novemb|decemb)\s(0?[1-9]|[12][0-9]|3[01]|\*?\*),?\s(19|20|21|\*\*)(\d\d|\*\*)|(19|20|21|\*\*)?(\d\d|\*\*)([- /.])(0?[1-9]|1[012]|\*?\*)([- /.])(0?[1-9]|[12][0-9]|3[01]|\*?\*)|(0?[1-9]|1[012]|\*?\*)[- /.](0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](0?[1-9]|1[012]|\*?)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|today|tomorrow|yesterday)(?![\w*])' where languageId=14 and token='[date]'
update wildcardGrammars set regularExpression='(?<![\w*])(-?(\*|\d){1,3}(,?((\*|\d){3})){0,2}(\.(\*|\d){1,2})?(\smillion)?\s(dollar|cent|buck))(\sand\s(\*|\d){1,2}\scent)?)(?![\w*])' where languageId=16 and token='[currency]'
update wildcardGrammars set regularExpression='(?<![\w*])((januari|februari|march|april|may|june|juli|august|septemb|octob|novemb|decemb)\s(0?[1-9]|[12][0-9]|3[01]|\*?\*),?\s(19|20|21|\*\*)(\d\d|\*\*)|(19|20|21|\*\*)?(\d\d|\*\*)([- /.])(0?[1-9]|1[012]|\*?\*)([- /.])(0?[1-9]|[12][0-9]|3[01]|\*?\*)|(0?[1-9]|1[012]|\*?\*)[- /.](0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|(0?[1-9]|[12][0-9]|3[01]|\*?\*)[- /.](0?[1-9]|1[012]|\*?|\*)[- /.](19|20|21|\*\*)?(\d\d|\*\*)|today|tomorrow|yesterday)(?![\w*])' where languageId=16 and token='[date]'
GO
update wildcardGrammars set regularExpression='(?<![\w*])(-?(\*|\d){1,3}(,?((\*|\d){3})){0,2}(\.(\*|\d){1,2})?(\smillion)?\s(dollar|cent|buck))(\sand\s(\*|\d){1,2}\scent)?(?![\w*])' where languageId=16 and token='[currency]'
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_coachingStaticCallLists_coachingSessions]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingStaticCallLists]'))
ALTER TABLE [dbo].[coachingStaticCallLists] DROP CONSTRAINT [FK_coachingStaticCallLists_coachingSessions]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_coachingStaticCallLists_timeCreated]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[coachingStaticCallLists] DROP CONSTRAINT [DF_coachingStaticCallLists_timeCreated]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_coachingStaticCallLists_timeUpdated]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[coachingStaticCallLists] DROP CONSTRAINT [DF_coachingStaticCallLists_timeUpdated]
END
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_coachingStaticCallLists_coachingStaticCallListCalls]') AND parent_object_id = OBJECT_ID(N'[dbo].[coachingStaticCallListCalls]'))
ALTER TABLE [dbo].[coachingStaticCallListCalls] DROP CONSTRAINT [FK_coachingStaticCallLists_coachingStaticCallListCalls]
GO
CREATE TABLE [dbo].[tmp_coachingStaticCallLists](
[listId] [int] IDENTITY(1000,1) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[creator] [nvarchar](256) NOT NULL,
[timeCreated] [datetime] NOT NULL,
[timeUpdated] [datetime] NOT NULL,
[sessionId] [int] NULL,
[BinaryData] [image] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.tmp_coachingStaticCallLists SET (LOCK_ESCALATION = TABLE)
GO
GO
GRANT SELECT ON dbo.tmp_coachingStaticCallLists TO Web,reports,utopy AS dbo
GO
GRANT INSERT ON dbo.tmp_coachingStaticCallLists TO utopy AS dbo
GO
GRANT DELETE ON dbo.tmp_coachingStaticCallLists TO utopy,web AS dbo
GO
GRANT UPDATE ON dbo.tmp_coachingStaticCallLists TO utopy AS dbo
go
GRANT UPDATE ON dbo.tmp_coachingStaticCallLists(name) TO web AS dbo
GO
SET IDENTITY_INSERT dbo.tmp_coachingStaticCallLists ON
GO
IF EXISTS(SELECT * FROM dbo.coachingStaticCallLists)
EXEC('INSERT INTO dbo.tmp_coachingStaticCallLists (listId,name,creator,timeCreated,timeUpdated,sessionId,BinaryData)
SELECT listId, CONVERT(nvarchar(50), name), CONVERT(nvarchar(50), creator),timeCreated,timeUpdated,sessionId,BinaryData FROM dbo.coachingStaticCallLists WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.tmp_coachingStaticCallLists OFF
GO
/****** Object: Table [dbo].[coachingStaticCallLists] Script Date: 09/18/2014 10:13:42 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[coachingStaticCallLists]') AND type in (N'U'))
DROP TABLE [dbo].[coachingStaticCallLists]
GO
EXECUTE sp_rename N'dbo.tmp_coachingStaticCallLists', N'coachingStaticCallLists', 'OBJECT'
ALTER TABLE coachingStaticCallLists ADD CONSTRAINT [PK_coachingStaticCallLists] PRIMARY KEY CLUSTERED
(
[listId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE [dbo].[coachingStaticCallLists] WITH CHECK ADD CONSTRAINT [FK_coachingStaticCallLists_coachingSessions] FOREIGN KEY([sessionId])
REFERENCES [dbo].[coachingSessions] ([sessionId])
GO
ALTER TABLE [dbo].[coachingStaticCallLists] CHECK CONSTRAINT [FK_coachingStaticCallLists_coachingSessions]
GO
ALTER TABLE [dbo].[coachingStaticCallLists] ADD CONSTRAINT [DF_coachingStaticCallLists_timeCreated] DEFAULT (getutcdate()) FOR [timeCreated]
GO
ALTER TABLE [dbo].[coachingStaticCallLists] ADD CONSTRAINT [DF_coachingStaticCallLists_timeUpdated] DEFAULT (getutcdate()) FOR [timeUpdated]
GO
ALTER TABLE [dbo].[coachingStaticCallListCalls] WITH CHECK ADD CONSTRAINT [FK_coachingStaticCallLists_coachingStaticCallListCalls] FOREIGN KEY([listId])
REFERENCES [dbo].[coachingStaticCallLists] ([listId])
GO
ALTER TABLE [dbo].[coachingStaticCallListCalls] CHECK CONSTRAINT [FK_coachingStaticCallLists_coachingStaticCallListCalls]
GO
ALTER TABLE dbo.callMetaExTypes ADD displayName varchar(256) NULL
GO
-- add permission for the page
INSERT INTO [dbo].[objectPermissionsTbl]
([objectId]
,[groupId]
,[description]
,[values]
,[configurable]
,[explanation])
VALUES
(72
,13
,'Meta Data Manager'
,'<Values xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Value><display>Show page</display><value>2</value></Value><Value><display>Hide page</display><value>0</value></Value></Values>'
,1
,'')
GO
-- add the permission by default to administrator role
insert into dbo.rolePermissionsTbl values(72,5,2)
GO
GRANT select, insert, update on callMetaExTypes to web
GO
GO
Update RecognitionLanguages
set MinIndexConfidence=40 where index1=17
GO
GO
CREATE QUEUE PurgeTaskQueue
WITH STATUS=ON,
ACTIVATION (
PROCEDURE_NAME = sp_runPurgeTaskService,
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER ) ;
GO
CREATE SERVICE PurgeTaskService ON QUEUE PurgeTaskQueue ([DEFAULT]);
GO
GO
update recognitionparams set paramvalue=3200 where paramName='swirec_max_arcs' and phaseNum=5 and language=3
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 nvarchar(256) NOT NULL,
customerGroupId nvarchar(256) NOT NULL,
subject nvarchar(MAX) NULL,
body nvarchar(MAX) NULL,
externalTextId nvarchar(256) NULL,
sender nvarchar(MAX) NULL,
receiver nvarchar(MAX) NULL,
cc nvarchar(MAX) NULL,
bcc nvarchar(MAX) NULL,
programId int NOT NULL,
originalBody nvarchar(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 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)
BEGIN
DECLARE @min_text_id INT
DECLARE @max_text_id INT
DECLARE @total_max INT
SET @min_text_id = 0
SET @max_text_id = 10000
select @total_max = max(textId) from dbo.TextData (nolock)
WHILE @min_text_id <= @total_max
BEGIN
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, subject, body, externalTextId, CONVERT(nvarchar(MAX), sender), CONVERT(nvarchar(MAX), receiver), CONVERT(nvarchar(MAX), cc), CONVERT(nvarchar(MAX), bcc), programId, originalBody FROM dbo.TextData WITH (HOLDLOCK TABLOCKX) where textId >= ' + @min_text_id + ' and textId < ' + @max_text_id)
PRINT 'inserting into dbo.Tmp_TextData from textId ' + cast(@min_text_id as varchar(250)) + ' to ' + cast(@max_text_id as varchar(250))
SET @min_text_id = @min_text_id + 10000
SET @max_text_id = @max_text_id + 10000
END
END
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
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.configServer ADD
ExternalRcsUri varchar(512) NULL
GO
ALTER TABLE dbo.configServer SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GO
update dbo.versionTbl set version= '8.5.201.257' where resource in ('SM', 'SMART')
go
This page was last edited on January 26, 2015, at 09:42.
Comments or questions about this documentation? Contact us for support!