Tasks overview is not displayed

Modified on Thu, 22 Aug at 11:28 PM

Symptom

When logging in as a Service Desk Agent, you might see nothing displayed in Tasks screen:

Cause

In RC 3.8, a SQL schema have been updated (stored procedures and tables) in comparison with RC 3.7 and 3.7.10. This make SQL server take long time to re-compile those changes (maybe a few minutes). After the re-compiling is completed, the issue is gone.

Resolution

When the RC 3.8 is installed and you have configured the application and upgraded the database (using RC Configuration Tool), run the following script in SQL server:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spEXECsp_RECOMPILE]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[spEXECsp_RECOMPILE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spEXECsp_RECOMPILE] AS
SET NOCOUNT ON
-- 1 - Declaration statements for all variables
DECLARE @TableName varchar(128)
DECLARE @OwnerName varchar(128)
DECLARE @CMD1 varchar(8000)
DECLARE @TableListLoop int
DECLARE @TableListTable table (UIDTableList int IDENTITY (1,1), OwnerName varchar(128), TableName varchar(128), ObjType varchar(128))
-- 2 - Outer loop for populating the database names
INSERT INTO @TableListTable(OwnerName, TableName, ObjType)
SELECT u.[Name], o.[Name], o.[type] FROM sys.objects o INNER JOIN sys.schemas u ON o.schema_id = u.schema_id WHERE o.Type in ('U','P') ORDER BY o.[type]
-- 3 - Determine the highest UIDDatabaseList to loop through the records
SELECT @TableListLoop = MAX(UIDTableList) FROM @TableListTable
-- 4 - While condition for looping through the database records
WHILE @TableListLoop > 0
BEGIN
-- 5 - Set the @DatabaseName parameter
SELECT @TableName = TableName, @OwnerName = OwnerName FROM @TableListTable WHERE UIDTableList = @TableListLoop
-- 6 - String together the final backup command
SELECT @CMD1 = 'EXEC sp_recompile ' + '[' + @OwnerName + '.' + @TableName + ']' + char(13)
-- 7 - Execute the final string to complete the backups
-- SELECT @CMD1
EXEC (@CMD1)
-- 8 - Descend through the database list
SELECT @TableListLoop = @TableListLoop - 1
END
SET NOCOUNT OFF
GO

This script is to create [spEXECsp_RECOMPILE] stored procedure, which is used to recompile all changes in RC database (tables, stored procedures, etc.). This recompiling can improve access speed to RC database when user runs the system for the first time after upgrading.

After the [spEXECsp_RECOMPILE] is created in RC database, you need to run it by executing the following statement:

exec [dbo].[spEXECsp_RECOMPILE]

Properties

Applies to: All revisions of RC 3.8

Reference: TFS #64965

Knowledge base ID: 0189

Last updated: Mar 14, 2016

Attachments (1)

L0FMGnbbhkEz....jpeg
37 KB