Workitem_detail_view fails to rebuild because of a high number of lookups in the view_def table. The build process appears to fail after adding more than ten custom fields to the Workitem table in Helpdesk.
Failure appears to be a break-down of the building the workitem_detail_view from the sp_createHDDview stored procedure. Workitem_detail_view uses the e view_def table as a reference.
The sp_createHDDview stored procedure can only build a 4,000-character SQL command to run the "create view" SQL command. This limitation only allowed about nine or ten additional fields to be added into the Workitem table before the SQL command to build the view was over this 4,000-character limit.
The best resolution is to upgrade to Helpdesk 6.0 SP5. The resolution below has been implemented into the SP5 release.
If you cannot upgrade to SP5, below is a SQL command to alter the sp_createHDDview stored procedure used for creating views. Changes to the stored procedure will allow a larger number of lookups to be added.
Using a SQL query tool like SQL Server Management Studio, connect to your SQL server for Helpdesk and execute the following SQL command against the database used for Helpdesk (usually Altiris_Incidents).
/*Start SQL code */ ALTER PROCEDURE [dbo].sp_createHDDview @viewname nvarchar(255), @tablename nvarchar(255) As begin /* this stored procedure will build a CREATE VIEW SQL statement and execute it using sp_executesql We use a tricky double string replacement to allow us to save a bunch of repetitive code and get around T-SQL restrictions on where @ variables can appear */
/* drop the view if it exists */ if exists (select * from sysobjects where id = object_id(@viewname) and OBJECTPROPERTY(id, N'IsView') = 1) begin set @cvsql = N'drop view ' + @viewname exec sp_executesql @cvsql end
/* drop the view if it exists - drop [dbo].@viewname. */ if exists (select * from sysobjects where id = object_id('[dbo].'+@viewname) and OBJECTPROPERTY(id, N'IsView') = 1) begin set @cvsql = N'drop view [dbo].' + @viewname exec sp_executesql @cvsql end /* this template for the sql to execute calls sp_executesql itself to actually create the view */
set @createviewsql_template = N' declare @selclause nvarchar(4000) declare @joinclause nvarchar(4000) declare @col nvarchar(4000) declare @join nvarchar (4000) declare @colsql cursor
set @selclause = N'''' SET @colsql = CURSOR FAST_FORWARD FOR SELECT DISTINCT column_sql FROM view_def WHERE table_name = N''~VIEW_NAME''
FETCH NEXT FROM @colsql into @col WHILE @@FETCH_STATUS = 0 BEGIN set @selclause = @selclause + N'', '' + @col FETCH NEXT FROM @colsql into @col END CLOSE @colsql
set @joinclause=N'''' SET @colsql = CURSOR FAST_FORWARD FOR SELECT DISTINCT join_sql FROM view_def WHERE join_sql != N'''' AND table_name = N''~VIEW_NAME'' OPEN @colsql
FETCH NEXT FROM @colsql into @join WHILE @@FETCH_STATUS = 0 BEGIN set @joinclause = @joinclause + N'' '' + @join FETCH NEXT FROM @colsql into @join END CLOSE @colsql
set @selclause = substring(@selclause,2,len(@selclause)-1)
execute (N''CREATE VIEW [dbo].~VIEW_NAME AS SELECT '' + @selclause + N'' FROM ~TABLE_NAME '' + @joinclause) ' set @cvsql = replace(@createviewsql_template, N'~VIEW_NAME', @viewname) set @cvsql = replace(@cvsql, N'~TABLE_NAME', @tablename)
execute sp_executesql @cvsql end GO /*End SQL code */
Applies To Helpdesk 6.0 SP4
Logged in abqdc01 (Altiris - Albuquerque) database
Imported Document Id
This is machine translated content
Login to Subscribe
Please login to set up your subscription.
Didn't find the article you were looking for? Try these resources.