Filters in "Raw SQL" fail to load. Error: An item with the same key has already been added.

Products

• IT Management Suite

Issue/Introduction

Any filter in "Raw SQL" mode or changing a filter to be in "Raw SQL" mode fails to load with a standard console error screen.

Error during data bind for the RawSqlDataSourceEditControl.

An item with the same key has already been added.
   [System.ArgumentException @ mscorlib]
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at Altiris.Reporting.DataSource.RawSqlDataSourceEditControl.DataBindSources()
   at Altiris.Reporting.DataSource.RawSqlDataSourceEditControl.DataBind()

Exception logged from:
   at Altiris.Reporting.DataSource.RawSqlDataSourceEditControl.DataBind()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean, Boolean)
   at System.Web.UI.Page.ProcessRequest(Boolean, Boolean)
   at System.Web.UI.Page.ProcessRequest()
   at System.Web.UI.Page.ProcessRequest(System.Web.HttpContext)
   at Altiris.NS.UI.Controls.PageCachePage.ProcessRequest(System.Web.HttpContext)
   at Altiris.NS.UI.AltirisPage.ProcessRequest(System.Web.HttpContext)
   at System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(System.Web.HttpApplication+IExecutionStep, Boolean&)
   at System.Web.HttpApplication+PipelineStepManager.ResumeSteps(Exception)
   at System.Web.HttpApplication.BeginProcessRequestNotification(System.Web.HttpContext, AsyncCallback)
   at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(System.Web.Hosting.IIS7WorkerRequest, System.Web.HttpContext)
   at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, IntPtr, Int32)
   at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr, IntPtr, IntPtr, Int32)
   at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr, System.Web.RequestNotificationStatus&)
   at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr, System.Web.RequestNotificationStatus&)
   at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, IntPtr, Int32)
   at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr, IntPtr, IntPtr, Int32)

User [AD\altadm], Auth [AD\yl80], AppDomain [/LM/W3SVC/1/ROOT/Altiris/NS-2-131553601515066779]

HTTP [GET]: http://localhost/Altiris/NS/Admin/Collections/NSResourceCollection.aspx?Guid=668fad61-be28-48d0-b848-0d74c4154e17&TreeGuid=1ce604a6-c897-493f-a090-0039563990be&ItemGuid=668fad61-be28-48d0-b848-0d74c4154e17&ParentGuid=00000000-0000-0000-0000-000000000000&Url=http://[NSserver]/altiris/console/tree.aspx?TreeGuid=1ce604a6-c897-493f-a090-0039563990be&&ConsoleGuid=1b22db4e-a898-443f-9b99-855b1653d3f5&ConsoleGuid=1b22db4e-a898-443f-9b99-855b1653d3f5&CallbackPageId=9fa09788dc1846a7b773f91c1cfa4bda
 ip: [172.28.95.113]; languages: [en-US];
 response: [200 OK]; x-smp-nsversion: [8.1.4528.0];

-----------------------------------------------------------------------------------------------------
Date: 11/17/2017 10:03:07 AM, Tick Count: 152551193 (1.18:22:31.1930000), Size: 3.22 KB
Process: w3wp (10112), Thread ID: 290, Module: Altiris.Reporting.UI.dll
Priority: 1, Source: Altiris.Reporting.DataSource.RawSqlDataSourceEditControl.DataBind

Environment

ITMS 8.1, 8.5, 8.6

Cause

Duplicated view or table name in database. (with different identifier eg. dbo.view_name and domain\user.view_name  OR  dbo.CollectionMembership and ams.CollectionMembership)

In the example below, you can see that the customer has a custom table called "Inv_Warranty_Information". However, while trying to test this table, he duplicated the table and called them "backup.Inv_Warranty_Information" and "temp.Inv_Warranty_Information". The original table was called "dbo.Inv_Warranty_Information", which uses the default dbo schema. 

SQL Server doesn't like tables with the same name, even when the schema may be called differently.

Resolution

1. Define the duplicated view or table name using the below SQL query:
   
   select s.name + '.' + o.name 'Schema.Name', o.*
   from sys.sysobjects o
   join sys.schemas s on o.uid = s.schema_id
   where o.name in
   (
   SELECT     name
   FROM sysobjects WHERE type='U' OR type='V'
   GROUP BY name
   HAVING     (COUNT(name) > 1)
   )
   order by o.name, 1
   
   
2. Rename the duplicates. Find it under SQL Management Studio for the database in use, select the duplicated table and rename it (like "backup.Inv_Warranty_Information_1" and "temp.Inv_Warranty_Information_2")