Copy the following commands and run them in the SQL query window for the SpeechMiner 8.5.2 to 8.5.201.07 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.[[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



/* 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.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



/* 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.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