CRM 4.0 to CRM 2011 Queues Upgrades

There are lots of improvements and enhancement with regards queue in CRM 2011 and they are mentioned in this article

Now if you migrated your organization from CRM 4.0 system the since for each user CRM 4.0 has two queues as mentioned below
1. User’s Private Queue (i.e. Assign Queue)
2. User’s WIP Queue (i.e. In Progress Queue folder)

While upgrading User’s Private Queue is converted as default queue and renamed user’s full name so if user is name mark smith, in CRM 2011 this user will have only one queue named with full user name

All WIP queue is migrated as it is but on them worker Id set to owning user, so since these were the items in user’s In progress queue in CRM 4.0 they are migrated with working user set as owning user.

If you match your queue details with standard CRM 2011 organization (which was not upgrade from CRM 4.0) then you will know that they have got only one named queue and do not have this additional WIP queue.

If users want to match up this queue experience for CRM 2011 environment (migrated from CRM 4.0 system) to new install of CRM 2011 environment then following ate recommended steps for the sae which are mentioned in following article. this article provides the details but does not provide tool or script to do the job, this post will provide tool and script code to achieve this.

http://msdn.microsoft.com/en-us/library/gg327885.aspx

1. Move all items from WIP queue to user’s default queue and set worker id as well
2. Delete or Deactivate the WIP queue

Now each user can manually do above task but if you got large user base then I guess it is better if system admin can do above task for the same.

Following two ways to achieve this,

Option 1: using CRM API to update relevant queue records (Supported)

I have created application which will move all current WIP queue item to user’s named queue and then disables the WIP queue.

This application is shared below.
CRM2011_WIPQueues

Following is few more details regarding this application for the same.

Step 1: after downloading, Rename CRM2011_WIPQueues_Upgrades_EXE.doc to CRM2011_WIPQueues_Upgrades_EXE.zip and extract the file.

Step 2: after extracting run the CRM2011_WIPQueues_Upgrades.exe

Step 3: provide CRM server details and then click on “Move Items“ button. Following are example screen print for the same.

One advantage of this application is it is supported but disadvantage is that it could take more then 2-3 hours if your CRM system got really large records in WIP queues but since we have run this only once I prefer this approach.

However there is another faster (but unsupported) method is mentioned below to achieve the same result.

Option 2: using SQL script **UNSUPPORTED**

System administrator can build SQL script to update all relevant queue item records to change queue id from WIP queue to user’s named queue.

Following example screen print for the script for the same for one user, this script can be downloaded from here and change the USER DOMAIN NAME for the relevant user in the following script. Of course this needs to be run for all users in the system.

-- find out all items present in relevant user's WIP queue
-- update user's domain name in query mentioned below
select * from filteredqueueitem
inner join filteredqueue on
filteredqueueitem.queueid = filteredqueue.queueid
inner join filteredsystemuser on
filteredsystemuser.systemuserid = filteredqueue.ownerid
where queuetypecode = 3
and filteredsystemuser.domainname = 'USER DOMAIN NAME'

--Update items in WIP queue to named queue
-- update user's domain name in query mentioned below
Update queueitembase
set queueid = (select filteredqueue.queueid from filteredqueue
inner join filteredsystemuser on
filteredsystemuser.systemuserid = filteredqueue.ownerid
where queuetypecode = 2
and filteredsystemuser.domainname = 'USER DOMAIN NAME')
where queueid = (
select filteredqueue.queueid from filteredqueue
inner join filteredsystemuser on
filteredsystemuser.systemuserid = filteredqueue.ownerid
where queuetypecode = 3
and filteredsystemuser.domainname = 'USER DOMAIN NAME')

One advantage of this approach is that it will complete the moving with few minutes even if million records needs moving but big disadvantage is that we are making change to SQL directly which is considered as *UNSUPPORTED CHANGE * so if you are going for this route make sure you test this script fully on your development/UAT environment thoroughly before running them in to Live environment.

Hope this helps..

Cheers,
MayankP:)

SQL Transaction: Small Tip.

While using CRM 4.0 many times we have to do unsupported changes directly to the database and there is risk involved in running the queries directly to change CRM database record or field.

I have small tip for this using SQL Transaction for this. Before running your Query run begin transaction Command then run your query and check the results.

If results are ok and as expected then run commit transaction Command.

Otherwise if result is not as expected then run rollback transaction Command and that will undo the changes.

see below image for explnation on this..

Hope this helps!!!

Cheers,
MayankP:)

CRM 4.0 Security Privileges

Recently we started getting following error on one of our CRM environment.

Server was unable to process request.
0×80040220
SecLib::CrmCheckPrivilege failed. Returned hr = -2147220960 on UserId: 6e7d3c02-b2c2-df11-a8e6-78e7d1e8d0ae and PrivilegeId: cb4b339f-2b45-447e-bdd3-0bf4bbebc294
Platform

Well this does say it security issue but still does not say which entity it is failing for..Well you can easily track this down using following query. (I.e. replace relevant privilege id as per your requirement)

select name from FilteredPrivilege
where privilegeid = 'cb4b339f-2b45-447e-bdd3-0bf4bbebc294'

As in example it shows user does not have permission to update relevant leads, we given lead update permission to relevant users and that fixed the issue!!

While looking for solution on this problem I also found few CRM Security Reports displaying all role privileges, including hidden privileges.

Cheers,
MayankP:)

Append string value in SQL using XML Path

One of my colleagues has showed me this cool trick…

So many times you need to write code where you needed to append strings values from a field in SQL Table and all the times people uses different approach…cursors, CTE, WHILE loop, doing it from the front end…etc. in SQL

Following is one very cool approach to achieve this in SQL using XML Path.

Hope this helps…