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

book

Article ID: 158515

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

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://pmnams1428/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

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.

Environment

ITMS 7.1 SP2 mp1 and later
ITMS 8.1, 8.5
ITMS 8.6

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")
 

 

 

Attachments