Getting errors after adding more than ten custom fields in Helpdesk

Article:TECH31815  |  Created: 2007-07-05  |  Updated: 2007-10-26  |  Article URL http://www.symantec.com/docs/TECH31815
NOTE: If you are experiencing this particular known issue, we recommend that you Subscribe to receive email notification each time this article is updated. Subscribers will be the first to learn about any releases, status changes, workarounds or decisions made.
Article Type
Technical Solution

Issue



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.

Environment



Helpdesk 6.0 SP4

Cause



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.


Solution



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
      */
     
declare @createviewsql_template nvarchar(4000)
declare @cvsql nvarchar(4000)

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

OPEN @colsql

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


Supplemental Materials

SourceDEFECT
ValueABQ 4105
DescriptionLogged in abqdc01 (Altiris - Albuquerque) database

Legacy ID



36043


Article URL http://www.symantec.com/docs/TECH31815


Terms of use for this information are found in Legal Notices