KNOWN ISSUE: Package Summary reports fail to run

Article:TECH35594  |  Created: 2007-10-02  |  Updated: 2007-10-15  |  Article URL http://www.symantec.com/docs/TECH35594
Article Type
Technical Solution


Issue



After properly configuring a Package Server with the monitor agent and rolling out the WMI metric provider, metric data is properly uploaded to the database.

When attempting to run any of the reports located in the \Reports\Monitoring\Monitor Solution\Altiris\Package Server\Package Summary, an Invalid Column Name error will occur. Here is an example of such an error after attempting to run the "Total Disk Space Used" report:

Sql error in query. Error: System.Data.SqlClient.SqlError: Invalid column name '{bd0190a0-b471-49d8-85ca-cde2f2a30e6b}'.System.Data.SqlClient.SqlError: Invalid column name '{bd0190a0-b471-49d8-85ca-cde2f2a30e6b}'.Sql CommandText: -- MAX ROWCOUNT SET ROWCOUNT 50000 -- DECLARE @strsql nvarchar(4000) DECLARE @ColID1 nvarchar(45) DECLARE @ColName1 nvarchar(45) DECLARE @table nvarchar(100) DECLARE @days nvarchar(10) SET @ColID1 = '{bd0190a0-b471-49d8-85ca-cde2f2a30e6b}' SET @ColName1 = 'Total Disk Used' SET @table='SMRptTempe4d43a251b9d4e7484de98d9fbc255c9' SET @days=1 SET @strsql = N' IF EXISTS (Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''' + @table + N''' AND COLUMN_NAME=''' + @ColID1 + N''') BEGIN Select ''CM-BEAVIS'' as ''Name'', CONVERT(VARCHAR, tt.[TimeStamp], 101) + '' '' + CONVERT(VARCHAR, tt.[TimeStamp],108) as ''Start Time'', CASE DataLength([' + @ColID1 + N']) WHEN 0 then CAST(0 as decimal) ELSE CAST([' + @ColID1 + N'] as decimal) END as ''' + @ColName1 + N''' From ' + @table + N' tt Where DATEDIFF(dd, [TimeStamp], GETDATE()) <= ' + @days + N' Order BY 1 ASC END ELSE BEGIN /* Causes the ''Data not in database message'' to be generated */ Select * from NonExistentTable END ' EXEC sp_executesql @strsql


Environment



Monitor Solution Core 6.0.1337
Altiris Infrastructure Monitor Pack 6.0.1057
Altiris Infrastructure Report Pack  6.0.1037

Cause



Using the "Total Disk Spaced Use"  report as an example, here is the report query:

Here is the query for this Report:

DECLARE @strsql      nvarchar(4000)
DECLARE @ColID1      nvarchar(45)
DECLARE @ColName1 nvarchar(45)
DECLARE @table    nvarchar(100)
DECLARE @days      nvarchar(10)

SET @ColID1    = '{bd0190a0-b471-49d8-85ca-cde2f2a30e6b}'
SET @ColName1    = 'Total Disk Used'
SET @table='%Table Name%'
SET @days=%Days%

SET @strsql = N'
IF EXISTS (Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ''' + @table + N'''
AND COLUMN_NAME=''' + @ColID1 + N''')
BEGIN
    Select  ''%Machine Name%'' as ''Name'',
        CONVERT(VARCHAR, tt.[TimeStamp], 101) + '' '' + CONVERT(VARCHAR, tt.[TimeStamp],108) as ''Start Time'',
        CASE DataLength([' + @ColID1 + N'])
        WHEN 0 then CAST(0 as decimal)
        ELSE CAST([' + @ColID1 + N'] as decimal)
        END as ''' + @ColName1 + N'''
    From
        ' + @table + N' tt
    Where
        DATEDIFF(dd, [TimeStamp], GETDATE()) <= ' + @days + N'
    Order BY 1 ASC
END
ELSE
BEGIN
    /* Causes the ''Data not in database message'' to be generated */
    Select * from NonExistentTable
END
'
EXEC sp_executesql @strsql

It appears that the table being queried is 'SMRptTempe......'  and the query is looking for the ColID1 value which is a GUID that doesn't exist. Attempting to check for the existence of this table (no DROP function in this query) will not be succesful. As mentioned, for any report in the Package Summary Folder will generate the error as pasted above. The 'SMRptTempe'  table will contain a different numeric value with each attempt to run any of these report. 


Solution



This issue is currently being reviewed for the appropriate query modification.

Legacy ID



38517


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


Terms of use for this information are found in Legal Notices