-------------------------------------------------------------------------------
-- This is the update template file
--
-- For every hotfix/service pack there is a unique update script that is 
-- executed by the installer. 
--
-- The first statement is a logging statement to indicate what db version
-- the database currently has.
--
-- The script ends with an update statement to increment the database version
-- and another log statement to indicate completion of the update script.
-- Wrap all blocks with begin/end keywords, even if there is only one statement.
--
-- If some sql statements only need to be executed against a hosted or licensed -- database, use the dbo.isHosted() function call. This function returns 1 if 
-- the database is a hosted database and 0 if licensed, i.e. 
--	if dbo.isHosted() = 1
--		begin
--			print 'hosted';
--		end
--
--
-- Please use isBigint() function to determine if database uses bigint for IDs:
--	if dbo.isBigint() = 1
--		begin
--			print 'IDs are of BIGINT type';
--		end
-- else 
--		begin
--			print 'IDs are of INTERGER type';
--		end
-------------------------------------------------------------------------------

IF((SELECT COMPATIBILITY_LEVEL FROM SYS.DATABASES WHERE NAME = DB_NAME()) < 110)
BEGIN
	PRINT 'Please alter your database compatibility level'
	PRINT 'Minimal compatibility level is 110 - SQL Server 2012'
	SET NOEXEC ON
END
GO
SET NOCOUNT ON
GO
EXECUTE updateLogger '$Id: //depot/main/AdobeConnect/components/dbscripts/updates/update_11_0_5_0.sql#2 $', '$Change: 1416216 $', 'Start';
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------
-- Start of updates
--------------------------------------

------------------------------------------------------------------------------------------------------
--BEGIN Jira: ACREQ-41595 - Correct PPS_TRANSCRIPTS_SCO_TICKET to make it same as referent one
------------------------------------------------------------------------------------------------------
PRINT N'Dropping index PPS_TRANSCRIPTS.PPS_TRANSCRIPTS_SCO_TICKET...';
GO
DROP INDEX PPS_TRANSCRIPTS_SCO_TICKET ON PPS_TRANSCRIPTS;
GO
PRINT N'Creating index PPS_TRANSCRIPTS.PPS_TRANSCRIPTS_SCO_TICKET...';
GO
CREATE INDEX PPS_TRANSCRIPTS_SCO_TICKET ON PPS_TRANSCRIPTS
	([SCO_ID] ASC, [TICKET] ASC)
    INCLUDE([PRINCIPAL_ID], [SESSION_ID], [STATUS], [DATE_CREATED], [DATE_CLOSED]) 
	WITH (FILLFACTOR = 80, PAD_INDEX = ON, SORT_IN_TEMPDB=ON);
GO
------------------------------------------------------------------------------------------------------
--END Jira: ACREQ-41595
------------------------------------------------------------------------------------------------------

--------------------------------------------------
-- End of updates - !!! DO NOT ADD LINES BELOW !!!
--------------------------------------------------

--------------------------------------------------------------------
-- Various data maintenance points
--------------------------------------------------------------------

--------------------------------------------------------------------

--------------------------------------------------------------------
-- BEGIN REFRESH block
--
-- Recompile SPs, views and other objects that need to be recompiled
-- Please DO NOT change following lines
--------------------------------------------------------------------
GO
IF CURSOR_STATUS('global', 'ObjectCursor') >= -1
BEGIN
	DEALLOCATE ObjectCursor;
END
GO

DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;

DECLARE ObjectCursor CURSOR FAST_FORWARD FOR
	SELECT SCHEMA_NAME(o.schema_id) + '.[' + OBJECT_NAME(o.object_id) + ']'
	FROM sys.objects o 
	WHERE type_desc IN ('SQL_STORED_PROCEDURE', 'SQL_TRIGGER', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION',
								'SQL_INLINE_TABLE_VALUED_FUNCTION',	'VIEW')
	AND ISNULL(OBJECTPROPERTY(o.object_id,'IsSchemaBound'), 0) = 0	 -- don't check schemabinding they don't like it

OPEN ObjectCursor;
FETCH NEXT FROM ObjectCursor INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @Name + '''';
   PRINT @Sql;

   BEGIN TRY
		EXEC @Result = sp_executesql @Sql;
		IF @Result <> 0 RAISERROR('Failed', 16, 1);
   END TRY
   BEGIN CATCH
		-- PRINT @Sql
      PRINT 'The module ''' + @Name + ''' does not compile.';
   IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    END CATCH

    FETCH NEXT FROM ObjectCursor INTO @Name;
END

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor;
GO

-------------------------------------------------------------------------------
-- Change the version number to match the file build number below
-- MINOR_RELEASE is tantamount to Service Pack
-- The REVISION field should never really be set to anything other than 0 in the DB update scripts context

UPDATE PPS_DATABASE_VERSION 
	SET MAJOR_RELEASE = 11, 
	MINOR_RELEASE = 0, 
	[UPDATE] = 5, 
	REVISION = 0
GO

EXECUTE updateLogger '$Id: //depot/main/AdobeConnect/components/dbscripts/updates/update_11_0_5_0.sql#2 $', '$Change: 1416216 $', 'Finish';
GO
SET NOCOUNT OFF
SET NOEXEC OFF
GO