“Cannot insert duplicate key row” -error in Transform.Common

February 5, 2020 Posted by Alexander Axberg

I recently ran into the following error in the Transform-job in Data Warehouse:

An error countered while attempting to execute ETL Module:
 ETL process type: Transform
 Batch ID: 5601
 Module name: TransformIncidentDim
 Message: ErrorNumber=”2601″ Message=”Cannot insert duplicate key row in object ‘dbo.IncidentDim’ with unique index ‘UniqueIndex’.” Severity=”14″ State=”1″ ProcedureName=”TransformIncidentDimProc” LineNumber=”163″ Task=”Inserting into Dimension”

The error occures when the transform job was trying to instart a specific Incident into the dbo.IncidentDim table in the DWRepository database.
The source of the error was that the specific category in this incident, existed 2 times in the IncidentClassification table, but I guess the same thing could happen to the support groups or any other enum in the incident class.


  1. Open the IncidentClassification table in the DWRepository database, and look for enums with duplicate rows (but with different classificationID) with this query:
    SELECT count(enumTypeId) as ‘Count’ ,enumTypeId FROM IncidentClassification GROUP BY enumTypeId
  2. Look for a row where the count number is greater than 1. When found, copy the enumTypeId value and run this query:
    SELECT * FROM IncidentClassification WHERE enumTypeID = ‘<enumtypeID value>’
  3. To decide which duplicate row to remove, run a query against DWDataMart to see which one of the rows are used.
    Run this query against DWDataMart for each IncidentClassificationID above:
    SELECT * FROM IncidentDimvw WHERE Incidentclassification_IncidentClassificationID = ‘<IncidentclassificationID value>’
  4. For the row/rows in #2 that are not used in DWDataMart, delete them from DWRepository by using each the of IncidentClassificationIDs so only one row remains:
    DELETE FROM IncidentClassification WHERE IncidentClassificationID = ‘<IncidentclassificationID value>’
  5. Rerun the Transform.Common job, and it should complete successfully.

System Center 2019 released

March 14, 2019 Posted by Anders Asp

System Center 2019 has just been released and is available for download on both MVLS and MSDN / Visual Studio. In general, it’s a small release, and from a SCSM perspective the official news in this version are:

  • Support to SQL 2017
  • Improvement in Active Directory Connector
  • Improved UI Responsiveness
  • Enable Service Logon

Read more here:

Also, be sure to read all the Known Issues in this release here:

I’d hold back from upgrading my environments for a while until we’ve seen how stable the release is.

Also, from now on the semi-annual release cycle have been scrapped. So for those of you who went down that route you will now have to go back to the long term release cycle (in other words, SCSM 2019).

Read more here (under “Changes to release cadence” near the end):

Crashing tasks in Service Manager 2016

October 3, 2017 Posted by Anders Asp

So, I helped a customer upgrade to Service Manager 2016 the other day and the upgrade itself went fine. However, when trying to use some of their custom tasked after the upgrade, the console crashed.

The task itself isn’t anything special – it’s just a task to open a specific URL in their browser and has been working for a long time. We tried some different things to try to figure out what caused this crash. Running the console elevated, wiping our local SCSM user cache, trying from another client with another user and so on, but nothing seemed to help… When looking a little closer into the event log (Operations Manager log on the client), we discovered these events though.

And this part of the error message gave us a clue on what the issue might be:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Xml.XmlException: ‘=’ is an unexpected token. The expected token is ‘;’. Line 1, position 399.

For some reason, it seems like Service Manager 2016 doesn’t like the syntax we’re using for our tasks anymore. So let’s take a look at the task itself:

After some testing, we figured out that the &-character is causing the crash. Apparently, it is not converted into correct XML syntax when stored into the management pack as it used to in previous version of Service Manager. To avoid the crashing tasks, we simply had to do this conversion ourselves. So instead of writing & in our tasks, we had to replace it with &amp; After this change, the tasks were working fine again without crashing the console.

Note that this also apply to text written in other places in the custom task, such as the Description field.

Using a standalone SSAS or SSRS when installing SCSM 2016

September 24, 2017 Posted by Anders Asp

I ran into some issues when trying to install a brand new Service Manager 2016 environment at a customer the other day. They have been using Service Manager for a while and instead of doing an in-place upgrade of their 2012 R2 environment, they wanted to do a side-by-side upgrade/migration and created new VMs with Windows Server 2016 and SQL 2016. As this customer had experienced issues with SSAS (SQL Server Analysis Services) and the Service Manager cube processing in the past, they decided to put SSAS on a separate server in an effort to minimize any issues.

This is all fine and supported, but for some reason the installer didn’t actually discover SSAS on this server when trying to install the Data Warehouse. Instead the wizard just showed us something in line with “SQL server is not available”. Looking in the setup log (SCSMSetupWizard01.log in the Temp folder for the user running the setup), we found this error message:

01:41:29:Failed while trying to check remote AS: Object reference not set to an instance of an object. –    at Microsoft.SystemCenter.Essentials.SetupFramework.HelperClasses.SetupValidationHelpers.GetHighestMajorVersion(IEnumerable`1 versionList)
at Microsoft.SystemCenter.Essentials.SetupFramework.HelperClasses.SetupValidationHelpers.GetManagementScope(String hostName, String sqlInstance, ConnectionOptions connectionOption, String wmiNamespace, String managementScopeDesc)
at Microsoft.SystemCenter.Essentials.SetupFramework.HelperClasses.SetupValidationHelpers.PreReqRemoteMachineforAS(String hostName)
at Microsoft.SystemCenter.Essentials.SetupFramework.HelperClasses.SetupValidationHelpers.ValidateAS(String hostName).

After some digging, we discovered that in order for the installer to actually identify SQL on the SSAS machine, we had to install the SQL Engine. After adding that to the SSAS machine the installation went fine, and you can actually go ahead and remove the SQL Engine when the installation is completed. The same fix is needed if you’re running SSRS on a stand-alone machine.

Service Manager 2016 UR2 released

February 27, 2017 Posted by Anders Asp

As in the title – Update Rollup 2 for SCSM 2016 was released a couple of days ago. It’s a small UR that fixes the known issue with SM crashing when trying to create a new Exchange Connector through the wizard. Other than that there are some fixes to the HTML5 portal:

  • You as an administrator create a new Exchange Connector through the System Center 2016 Service Manager console. When you click the Test Connection button in the Server Connection pane of the Create Exchange Connector wizard, you receive an exception.
  • Requests that are created through the Self-Service Portal always have the Source field of the work item set as Portal and the Status field set as New. These values are not overwritten when a template or Request Offering input mapping is applied.
  • In the Self-Service Portal, the Request Offering forms display validation errors in English, even when a non-English language is selected.
  • The Self-Service Portal does not display the full name of users (such as the reviewers list for Review Activity and implementer for Manual Activity) but instead only displays the domain\user-name information for the user.
  • The My Requests and the My Activities pages do not load in the Self-Service Portal if the object associated to a work item class has duplicate properties that have the same name because of class inheritance.

Download the UR here:

Note that there’s no need to install anything unless you are running the HTML5 portal. The Exchange Connector fix is just an updated DLL file.

Service Manager 2016 related links

October 18, 2016 Posted by Anders Asp

A short post containing some useful Service Manager 2016 related links.

Official Service Manager 2016 documentation:

Read the System Center 2016 Release Notes here (Known Issues – do read this if you’re planning to use any SC 2016 product!):

Download the Service Manager 2016 Authoring Tool here:

Download a fix for the HTML5 Portal if you’re doing an upgrade here:

Download the Operations Manager Management Pack for Service Manager 2016 here:

Also note that Update Rollup 1 for System Center 2016 has been release. However, UR1 does not contain any fixes for Service Manager 2016. Download and read more here:

Service Manager 2016 now available

October 17, 2016 Posted by Anders Asp


Since a couple of days back the whole System Center 2016 suite, including Service Manager, is now available on MSDN and MVLS. The new version of Service Manager includes:

  • Performance improvements
  • Faster SCCM and Active Directory connector sync with disabling ECL logging
  • Grooming improvements for ECL logs
  • Reduction in incident workflow lag
  • Integrated ADGroupExpansion Workflow into ADConnector
  • New Date dimensions in Data Warehouse cubes
  • New HTML based Self Service portal
  • Spell check in SM console
  • Open activity in progress
  • Support for Lync 2013 and Skype for Business
  • Setup changes to support SQL AlwaysOn installation
  • Support for .NET Framework 4.5.1
  • Support for Windows Server 2016
  • Support for SQL Server 2016

Read more here.

The list is certainly not as long as we wish it would have been, but some fixes, especially the Performance Improvements, are really important. Here’s some numbers from Microsoft to display the performance improvements:

Improved work-item creation and update commit performance
Time to create and update work items was greatly reduced using this improvement.

Action SM2012R2 SM2016 Improvement
Incident creation time 2 – 6 seconds 0.5 seconds 4 times
Incident creation time during connector sync 8 – 10 seconds Less than 1 second 8 times

Improved workflow processing
Workflows in Service Manager should have less latency and should catch up faster when you do experience latency.

Here are the times for workflows to catch up after 2 hours of latency at a 45 work item per minute rate:

Action SM2012R2 SM2016 Improvement
Workflow catch-up time 2 hours 50 mins 1 hour 46 mins 1.5 times

Higher work-item per second processing capacity
Service Manger can more easily handle a large inflow of 45 work items per minute.

Action SM2012R2 SM2016 Improvement
Average incident creation time 2.2 seconds 0.5 seconds 4 times

As you can see by looking at the numbers, the improvements made has really made an impact, and I can honestly say that I was blown away by speed of the console while trying SCSM 2016 in my lab. Hopefully the improvements will be this noticeable in all production environments as well, once people manage to upgrade.

Also, a couple of things has been deprecated and is no longer supported:

  • The Silverlight based Self Service Portal
  • The IT GRC Process Pack
  • The CSSP, Cloud Service Process Pack

Read more here.

AD-Connector to external, untrusted domain stops after UR7 or later

October 5, 2016 Posted by Anders Asp

If you have an untrusted AD-connector that suddenly cannot connect anymore, after applying UR7 or later it could be due to an LDP connectivity issue.
In my case, the Operations Manager log on the workflow server logged the error: The specified domain does not exist or cannot be contacted.. each time the connector started.

DNS resolution of the domain name worked fine, and I could connect manually to a DC on port 389 using ldp.exe, from the workflow server.
However, because of the updates in the AD-connector done in UR7, SCSM is now also connecting to the DC on port 389/UDP instead of only 389/TCP.

So go to you network guy and ask him to open up for both TCP and UDP on port 389.

You can troubleshoot the LDAP connectivity yourself using this tool:
PortQryUI (https://www.microsoft.com/en-us/download/details.aspx?id=24009)

UR3 patch for the HTML5 portal

May 26, 2016 Posted by Anders Asp

If anyone has missed it, the 3rd cumulative patch was released for the Microsoft HTML5 portal a couple of weeks ago. Grab it here: https://support.microsoft.com/en-us/kb/3144617

Here are new features introduced with this release
• Attachments a can be viewed and downloaded for Self Service Portal.
• The Must Vote and Has Veto information is added for reviewers in Review activities.
• By default, the portal puts custom enumerations for My Request (incident & Service Requests) states in the Closed filter category. Now the portal allows for customization to map required custom states to the Active filter category also. For more details check for “CustomActiveRequestStatusEnumList” under “Basic Customization” section on this link.
Here are issues fixed in this release
• Multiple selections across pages in the Query UI do not work.
• Enums in list don’t appear in the same order as they are shown in the console.
• Cannot scroll to last item in Internet Explorer 10.
• Selections of optional query element in a request offering is not being mapped to work item fields.
• The Resolved Date property for an incident is not being set while resolving an incident from the Self Service Portal.
• When you author the request offering, custom properties cannot be mapped to an activity which is part of another activity.
• The portal is not setting Actual End Date or Decision Date values for manual and review activities.
• The portal displays an incorrect time when the server uses the 12 Hr (AM/PM) time format.
• Request offerings and Service offerings are unsorted (now are shown alphabetically).
• Page load fails with a JavaScript error when you click the Share icon for an item which has a single quotation mark (‘) in the title.
• Marking a manual activity as failed takes it to the Completed state.
• The Date Picker in a request offering form keeps the date only in U.S. format.
• A request offering form crashes if it contains an empty Simple list form element.
• Activities are missing in MyActivites for “All” filter in Turkish Language
• Display-Only Query Results are behaving as Mandatory Field.

Update Rollup 7 released – includes the write collision fix!

July 29, 2015 Posted by Anders Asp

Today is a big day! Not only was Windows 10 released today but also Update Rollup 7 for SCSM. Why is UR7 such a big deal? Well, among other things, it contains a fix for the write collision issue that’s been in the product since day 1! (See this post for more information about the “error”: http://www.scsm.se/?p=853)


After applying UR7, you shouldn’t be getting the error above if the WI you’re working with is updated by someone or something else (such as a workflow) while you still haven’t saved your changes. Instead your update will be merged with the other update.

Example: If User A and User B works with the same Incident, let’s call it IR1234, and User A updates the Title while User B still have the Incident form opened. User A presses OK and moves along to the next case. User B has now updated the same Incident, IR1234, with a new Description. Remember, the Title change that User A did is not yet reflected on the form, since User B had the form opened when User A updated this! Previously when User B pressed OK or Apply the error above would be thrown, but now with UR7 applied the update will be commited, and the form would be updated to also reflect the update to Title that was commited by User A. To highlight that this merge of data has been done, the form will display a piece of information in the top of the form, just like this.


The only exception to this would be if User A and User B actually updates the exact same property. In that case, the old data collision error will still occur. To get more information about write collision fix, see this official blogpost:

To get UR 7 and to read about all the fixes, go here: