Tuesday, April 09, 2013

Switching on and off appointment synchronization in CRM for Outlook

When using the mobile solutions, it is quite common to use a server-based CRM-Exchange synchronization software. There are several different products out there and the one I have the best experiences from is Riva Exchange integrator (http://www.rivacrmintegration.com) although a bit more expensive than other on the market, a very stable and high quality product.

If the Outlook addon for CRM is used at the same time, there is a substantial risk that appointments created in CRM are integrated twice to the Exchange server, once through the server based integration and once using the Outlook integration. As the server based integration is to be the main method of integration the Outlook integration needs to be modified to stop appointments from being integrated from CRM to Outlook.
Setting which entities are to be synchronized from CRM to Outlook

This is a bit problematic however, as there is no way of doing this for a group of users, but is done in the personal settings once the outlook addon has been installed. This means doing it manually is practically impossible.

The data is actually stored in CRM in the UserQuery entity. This is a rather special entity however, for instance, you need to impersonate each user to be able to edit each users UserQuery’s. This will take some extra consideration in coding. Thanks to David Jennaway for pointing out some of the details regarding this.

If you are running a CRM On-Premise, writing to the SQL database in not supported, but in certain cases, and I would generally advise against it as it can damage you CRM system, it might be ok. In this case, what is needed is to change the statecode and status code of some UserQuery:s. As this is unsupported I give no guarantees but I have used it myself, so I thought I’d share it with you if you’d like to use it.

The following SQL Update script, will deactivate all appointment synchronization in the CRM Outlook addon for all users. Run it in the context of the CRM database.
Again, USE AT OWN RISK, it is unsupported!

UPDATE [UserQueryBase]   SET [StatusCode] = 2      ,[StateCode] = 1 WHERE QueryType = 256 AND (ReturnedTypeCode = 4201 OR ReturnedTypeCode = 4214 OR ReturnedTypeCode = 4251)

The following script will reactivate them.

UPDATE [UserQueryBase]   SET [StatusCode] = null      ,[StateCode] = 0 WHERE QueryType = 256 AND (ReturnedTypeCode = 4201 OR ReturnedTypeCode = 4214 OR ReturnedTypeCode = 4251)
Another interesting fact that you might not be aware of is that if you switch off the synchronization of appointments between CRM and Outlook, the next time you run the synchronization, the CRM Outlook Addon will remove all the corresponding appointments from Outlook, and hence from Exchange (if Outlook is connected to Exchange).

Gustaf Westerlund
MVP, CEO and owner at CRM-konsulterna AB


  1. Hi Gustaf, I was really impressed by your article on Switching on and off appointment synchronization in CRM for Outlook. This was really informative me. 
    CRM Development

  2. This is really very helpful information which is provided online.The person which are interested and want to more about CRM,then they can use this blog.
    CRM Solutions