Original Article by Johan Veldhuis here
Warning: the procedure described in this blog are used as a workaround for an issue. Incorrect changes in the database may cause malfunctioning of the Response Groups.
In Lync 2010 it’s possible to add users to a group which can be used by the Response Groups. In some scenarios it might be necessary to remove and directly add an agent. This can be the case if a SIP address of a user needs to be changed.
Removing the agent is normally not an issue but adding it directly is. In almost every case the following error will be displayed:
Windows Powershell returned the following errors:
Cannot insert duplicate key row in object dbo.Agents with unique index IX_Agents_UserSid.
The statement has been terminated.
This error will tell you that it can’t add the agent because there is an existing entry in the agent table which is used by the Response Groups.
Normally you will have to wait a few minutes before you can add the user again. But this week I experienced that after 3 hours the agent still can’t be added to the group. In the event log nothing strange was being logged. On TechNet you will not find many information how the Response Groups really perform this action. But in some cases it looks like the Response Group will be resetted after making a change and it only resets the Response Group if it is idle.
Because I couldn’t add the user after 3 hours it became quiet irritating. As you may know Lync is using SQL databases. The agents of a Response Group are added to the rgsconfigdatabase. This database can be found on the SQl server which also hosts the CMS.
Within this database you will find a table called dbo.Agents. When you will open this table you will see an overview of the agents which are member of the groups.
Perform the following steps to remove an agent from this table:
Right click on the dbo.Agents table and select the option Edit Top 200 rows
Search for the user and remove the specific record
By removing the record it will be possible to add the agent to the group again.
The issue is described in this KB article of Microsoft: