Unfortunately the Audit entity does not not have a Filtered view in the database. Also, there is no support for advanced find and the like.
Therefore there is no supported way to create a report that runs on the audit entity.
If you are working with Dynamics CRM 2011 On Premise you could go for the unsupported solution provided by daemon lin in this thread:
Query the table AuditBase (or the Audit view) in combination with the [MetadataSchema].[Attribute] table and the [MetadataSchema].[Entity] table to translate the AttributeMask (separated with ',') in AuditBase using Attribute.ColumnNumber = (the number in AuditBase.ChangeData), Attribute.EntityId = Entity.EntityId and Entity.ObjectTypeCode = AuditBase.ObjectTypeCode into the attribute names (You can get the display name using [MetadataSchema].[LocalizedLabel]). The corresponding changed values are in AuditBase.ChangeData seperated by ~. Operation is an optionset (1=Create, 2=Update, 3=Delete)
Note that security will NOT be applied on the data. Security Roles are not taken into account in this query. (In contrast, Filtered views always apply security for the current user)
Remember that in this case you are working in an unsupported mode and that a CRM update may break your report. Make sure to document that you did this.
If you are working with CRM 2011 Online: there is no way to create a complete report using SSRS. SSRS in CRM Online cannot directly access the database and the Audit Entity is not entirely exposed through web services which is required if you want to create a FetchXml based Report. (e.g. the ChangeData column that contains the changed data is not valid for read; you would have no easy option to translate the attributemask into corresponding field names.)
In All Cases: you can always work with filtering on the Audit Summary View (Settings > Auditing > Audit Summary View)
http://social.microsoft.com/Forums/en/crmdevelopment/thread/b3bcc68b-88e4-4c1e-af96-dc645b34bbe7
http://marcuscrast.wordpress.com/2012/01/14/dynamics-crm-2011-audit-report-in-ssrs/
http://www.avanadeblog.com/xrm/2010/09/crm-2011-feature-of-the-week-9132010-auditing.html
http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/a11cf7b2-3f5a-4f2b-b97d-9ca56a0c5aab
Sunday, 26 February 2012
Saturday, 25 February 2012
Crm 2011 Developer Toolkit - How to exclude a resource file from a crm deployment package?
Please note the properties window of the web resource file. If you set the Build Action to None, the resource will NOT be deployed to Crm
Friday, 24 February 2012
VirtualBox 4: how to save the State of the Machine
To close virtual machine, users can simply click the x in the top right corner, or press the host key + Q.
After closing the virtual machine window, VirtualBox will ask if you want to save, send the shutdown signal, or power off the VM.
http://grok.lsu.edu/article.aspx?articleid=13463
After closing the virtual machine window, VirtualBox will ask if you want to save, send the shutdown signal, or power off the VM.
http://grok.lsu.edu/article.aspx?articleid=13463
Wednesday, 22 February 2012
How to do AllColumns for ColumnSet for Crm 2011
new Microsoft.Xrm.Sdk.Query.ColumnSet(true) is equal to new AllColumns() in CRM 4.0.
Thursday, 16 February 2012
How does Crm smart matching work? smart matching rules and logic
How does smart matching work:
Smart matching relies completely on the existence of similarity between emails. The subject and recipients (from, to, cc and bcc) list are the two important components that are considered with checking for similarity.
When an email is sent from CRM, there are two sets of hashes generated for it and stored in the database.
a. Subject hashes:
To generate subject hashes, the subject of the email, which may include the CRM token if its usage is enabled in system settings, is first checked for noise words like RE: FW: etc. The noise words are stripped off the subject and then tokenized. All the non empty tokens (words) are then hashed to generate subject hashes.
b. Recipient hashes:
To generate the recipient hashes the recipient (from, to, cc, bcc) list is analyzed for unique email addresses. For each unique email address an address hash is generated.
Next when an incoming email is tracked (arrived) in CRM, the same method is followed to create the subject and recipient hashes.
To find the correlation between the incoming email and the outgoing email the stored subject and recipient hashes are searched for matching values. Two emails are correlated if they have the same count of subject hashes and at least two matching recipient hashes.
http://community.dynamics.com/roletailored/customerservice/b/cscrmblog/archive/2008/11/11/microsoft-dynamics-crm-email-correlation-and-smart-matching.aspx
http://blogs.msdn.com/b/crm/archive/2008/01/29/what-s-new-in-microsoft-dynamics-crm-4-0-e-mail-integration.aspx
Smart matching relies completely on the existence of similarity between emails. The subject and recipients (from, to, cc and bcc) list are the two important components that are considered with checking for similarity.
When an email is sent from CRM, there are two sets of hashes generated for it and stored in the database.
a. Subject hashes:
To generate subject hashes, the subject of the email, which may include the CRM token if its usage is enabled in system settings, is first checked for noise words like RE: FW: etc. The noise words are stripped off the subject and then tokenized. All the non empty tokens (words) are then hashed to generate subject hashes.
b. Recipient hashes:
To generate the recipient hashes the recipient (from, to, cc, bcc) list is analyzed for unique email addresses. For each unique email address an address hash is generated.
Next when an incoming email is tracked (arrived) in CRM, the same method is followed to create the subject and recipient hashes.
To find the correlation between the incoming email and the outgoing email the stored subject and recipient hashes are searched for matching values. Two emails are correlated if they have the same count of subject hashes and at least two matching recipient hashes.
http://community.dynamics.com/roletailored/customerservice/b/cscrmblog/archive/2008/11/11/microsoft-dynamics-crm-email-correlation-and-smart-matching.aspx
http://blogs.msdn.com/b/crm/archive/2008/01/29/what-s-new-in-microsoft-dynamics-crm-4-0-e-mail-integration.aspx
Tuesday, 14 February 2012
CRM 2011 How to change ownership of an entity after creation. Clone an entity
Issue:
Has anyone tried changing the ownership type of an entity from organisation to user in CRM 2011 before?
As there are hundreds of fields on the entities’ form already, I’d like to save time on recreating entity if possible.
Solution:
You could export all the entity fields and re-import them to the new user-owned entity, creating them during import. Far quicker than doing it manually.
Step1: Create the entity first without adding any fields and select the entity type to be either org or user.
Step2: Using data import wizard (NOT solution import) to create the rest of the fields. Be careful with the Guid in the lookup fields. They have to exist in the target crm org.
Step3: Tidy up the forms
http://www.youtube.com/watch?v=hZVFXFZ5tUU
Has anyone tried changing the ownership type of an entity from organisation to user in CRM 2011 before?
As there are hundreds of fields on the entities’ form already, I’d like to save time on recreating entity if possible.
Solution:
You could export all the entity fields and re-import them to the new user-owned entity, creating them during import. Far quicker than doing it manually.
Step1: Create the entity first without adding any fields and select the entity type to be either org or user.
Step2: Using data import wizard (NOT solution import) to create the rest of the fields. Be careful with the Guid in the lookup fields. They have to exist in the target crm org.
Step3: Tidy up the forms
http://www.youtube.com/watch?v=hZVFXFZ5tUU
Monday, 13 February 2012
Html5 vs Silverlight
Small outward facing apps, go HTML, for complex LOB go silverlight.
http://www.hanselman.com/blog/ShouldIUseHTML5OrSilverlightOneMansOpinion.aspx
http://www.hanselman.com/blog/ShouldIUseHTML5OrSilverlightOneMansOpinion.aspx
Sunday, 12 February 2012
Out of the mouths of children
A 1st grade school teacher had twenty-six students in her class. She presented e...ach child in her classroom the 1st half of a well-known proverb and asked them to come up with the remainder of the proverb. It's hard to believe these were actually done by first graders
Friday, 10 February 2012
Where is my app.config for SSIS?
Where is my app.config for SSIS?
By Darren Green 3 Aug 2009 09:20
Sometimes when working with SSIS you need to add or change settings in the .NET application configuration file, which can be a bit confusing when you are building a SSIS package not an application. First of all lets review a couple of examples where you may need to do this.
You are using referencing an assembly in a Script Task that uses Enterprise Library (aka EntLib), so you need to add the relevant configuration sections and settings, perhaps for the logging application block.
You are using using Enterprise Library in a custom task or component, and again you need to add the relevant configuration sections and settings.
You are using a web service with Microsoft Web Services Enhancements (WSE) 3.0 and hosting the proxy in SSIS, in an assembly used by your package, and need to add the configuration sections and settings.
You need to change behaviours of the .NET framework which can be influenced by a configuration file, such as the System.Net.Mail default SMTP settings. Perhaps you wish to configure System.Net and the httpWebRequest header for parsing unsafe header (useUnsafeHeaderParsing), which will change the way the HTTP Connection manager behaves.
You are consuming a WCF service and wish to specify the endpoint in configuration.
There are no doubt plenty more examples but each of these requires us to identify the correct configuration file and and make the relevant changes. There are actually several configuration files, each used by a different execution host depending on how you are working with the SSIS package.
The folders we need to look in will actually vary depending on the version of SQL Server as well as the processor architecture, but most are all what we can call the Binn folder. The SQL Server 2005 Binn folder is at C:\Program Files\Microsoft SQL Server\90\DTS\Binn\, compared to C:\Program Files\Microsoft SQL Server\100\DTS\Binn\ for SQL Server 2008. If you are on a 64-bit machine then you will see C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\ for the 32-bit executables and C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ for 64-bit, so be sure to check all relevant locations. Of course SQL Server 2008 may have a C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\ on a 64-bit machine too.
To recap, the version of SQL Server determines if you look in the 90 or 100 sub-folder under SQL Server in Program Files (C:\Program Files\Microsoft SQL Server\nn\) . If you are running a 64-bit operating system then you will have two instances program files, C:\Program Files (x86)\ for 32-bit and C:\Program Files\ for 64-bit. You may wish to check both depending on what you are doing, but this is covered more under each section below.
There are a total of five specific configuration files that you may need to change, each one is detailed below:
DTExec.exe.config
DTExec.exe is the standalone command line tool used for executing SSIS packages, and therefore it is an execution host with an app.config file.
e.g. C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe.config
The file can be found in both the 32-bit and 64-bit Binn folders.
DtsDebugHost.exe.config
DtsDebugHost.exe is the execution host used by Business Intelligence Development Studio (BIDS) / Visual Studio when executing a package from the designer in debug mode, which is the default behaviour.
e.g. C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DtsDebugHost.exe.config
The file can be found in both the 32-bit and 64-bit Binn folders. This may surprise some people as Visual Studio is only 32-bit, but thankfully the debugger supports both. This can be set in the project properties, see the Run64BitRuntime property (true or false) in the Debugging pane of the Project Properties.
dtshost.exe.config
dtshost.exe is the execution host used by what I think of as the built-in features of SQL Server such as SQL Server Agent
e.g. C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtshost.exe.config
This file can be found in both the 32-bit and 64-bit Binn folders
devenv.exe.config
Something slightly different is devenv.exe which is Visual Studio. This configuration file may also need changing if you need a feature at design-time such as in a Task Editor or Connection Manager editor.
Visual Studio 2005 for SQL Server 2005 - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe.config
Visual Studio 2008 for SQL Server 2008 - C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe.config
Visual Studio is only available for 32-bit so on a 64-bit machine you will have to look in C:\Program Files (x86)\ only.
DTExecUI.exe.config
The DTExec UI tool can also have a configuration file and these cab be found under the Tools folders for SQL Sever as shown below.
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
A configuration file may not exist, but if you can find the matching executable you know you are in the right place so can go ahead and add a new file yourself.
In summary we have covered the assembly configuration files for all of the standard methods of building and running a SSIS package, but obviously if you are working programmatically you will need to make the relevant modifications to your program’s app.config as well.
Ref: http://www.sqlis.com/post/Where-is-my-appconfig-for-SSIS.aspx
By Darren Green 3 Aug 2009 09:20
Sometimes when working with SSIS you need to add or change settings in the .NET application configuration file, which can be a bit confusing when you are building a SSIS package not an application. First of all lets review a couple of examples where you may need to do this.
You are using referencing an assembly in a Script Task that uses Enterprise Library (aka EntLib), so you need to add the relevant configuration sections and settings, perhaps for the logging application block.
You are using using Enterprise Library in a custom task or component, and again you need to add the relevant configuration sections and settings.
You are using a web service with Microsoft Web Services Enhancements (WSE) 3.0 and hosting the proxy in SSIS, in an assembly used by your package, and need to add the configuration sections and settings.
You need to change behaviours of the .NET framework which can be influenced by a configuration file, such as the System.Net.Mail default SMTP settings. Perhaps you wish to configure System.Net and the httpWebRequest header for parsing unsafe header (useUnsafeHeaderParsing), which will change the way the HTTP Connection manager behaves.
You are consuming a WCF service and wish to specify the endpoint in configuration.
There are no doubt plenty more examples but each of these requires us to identify the correct configuration file and and make the relevant changes. There are actually several configuration files, each used by a different execution host depending on how you are working with the SSIS package.
The folders we need to look in will actually vary depending on the version of SQL Server as well as the processor architecture, but most are all what we can call the Binn folder. The SQL Server 2005 Binn folder is at C:\Program Files\Microsoft SQL Server\90\DTS\Binn\, compared to C:\Program Files\Microsoft SQL Server\100\DTS\Binn\ for SQL Server 2008. If you are on a 64-bit machine then you will see C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\ for the 32-bit executables and C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ for 64-bit, so be sure to check all relevant locations. Of course SQL Server 2008 may have a C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\ on a 64-bit machine too.
To recap, the version of SQL Server determines if you look in the 90 or 100 sub-folder under SQL Server in Program Files (C:\Program Files\Microsoft SQL Server\nn\) . If you are running a 64-bit operating system then you will have two instances program files, C:\Program Files (x86)\ for 32-bit and C:\Program Files\ for 64-bit. You may wish to check both depending on what you are doing, but this is covered more under each section below.
There are a total of five specific configuration files that you may need to change, each one is detailed below:
DTExec.exe.config
DTExec.exe is the standalone command line tool used for executing SSIS packages, and therefore it is an execution host with an app.config file.
e.g. C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe.config
The file can be found in both the 32-bit and 64-bit Binn folders.
DtsDebugHost.exe.config
DtsDebugHost.exe is the execution host used by Business Intelligence Development Studio (BIDS) / Visual Studio when executing a package from the designer in debug mode, which is the default behaviour.
e.g. C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DtsDebugHost.exe.config
The file can be found in both the 32-bit and 64-bit Binn folders. This may surprise some people as Visual Studio is only 32-bit, but thankfully the debugger supports both. This can be set in the project properties, see the Run64BitRuntime property (true or false) in the Debugging pane of the Project Properties.
dtshost.exe.config
dtshost.exe is the execution host used by what I think of as the built-in features of SQL Server such as SQL Server Agent
e.g. C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtshost.exe.config
This file can be found in both the 32-bit and 64-bit Binn folders
devenv.exe.config
Something slightly different is devenv.exe which is Visual Studio. This configuration file may also need changing if you need a feature at design-time such as in a Task Editor or Connection Manager editor.
Visual Studio 2005 for SQL Server 2005 - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe.config
Visual Studio 2008 for SQL Server 2008 - C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe.config
Visual Studio is only available for 32-bit so on a 64-bit machine you will have to look in C:\Program Files (x86)\ only.
DTExecUI.exe.config
The DTExec UI tool can also have a configuration file and these cab be found under the Tools folders for SQL Sever as shown below.
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
A configuration file may not exist, but if you can find the matching executable you know you are in the right place so can go ahead and add a new file yourself.
In summary we have covered the assembly configuration files for all of the standard methods of building and running a SSIS package, but obviously if you are working programmatically you will need to make the relevant modifications to your program’s app.config as well.
Ref: http://www.sqlis.com/post/Where-is-my-appconfig-for-SSIS.aspx
Thursday, 9 February 2012
Dates Fields and Time Zones in CRM
As we pick up larger projects, we are finding more that cross international boundaries. Due to the way SQL handles date fields and how the CRM import process works, this presents some problems. Here is the summary of my findings on these issues.
Server time – this is the timezone of the server. So if you server is installed in NZ, you will be in GMT+12
User time – this is set in the user’s CRM options
Daylight saving – note that to make this email simpler, I have ignored daylight saving. When you are working out time adjustments, you will need add time on appropriately. Daylight saving varies by country and even within countries e.g. Queensland does not have it but NSW in the same timezone does.
1. Populating a date and time field through the UI
Date and time is entered as user’s local time and stored as UTC in the database. When viewed by a user, this is converted to the user’s time zone. This means that if you are an NZ user (GMT+12) and you add an appointment for 1100 NZ time, it will appear as 0900 NZ time to a user in the Sydney timezone (GMT+10). So far, so good.
Resolution: No required, working as expected
2. Populating a date only field through the UI
SQL does not have the concept of a date only and has to store date and time for date only record entered in CRM. It does this by storing the date at midnight local time. This means that if a NZ user (GMT+12) adds a date of 09/02/2011, it will be stored in SQL as 2011-02-08 12:00:00 (midday the day before) so that when you add on the 12 hours, it becomes 2011-02-09 00:00:00. Now, if this date is viewed by someone in the Sydney timezone (GMT+10), it will be converted to their local time by adding on 10 hours to give 2011-02-08 22:00:00 which when you display as a date only field shows as 08/02/2011 – the day before the date entered. In summary:
Date Entered by First User (dd/mm/yy) Time Zone of First User (GMT+no.of hours) Date and Time Stored in SQL Time Zone of Second User (GMT+no.of hours) Date and Time as Converted from SQL time Date Displayed in CRM for Second User
09/02/2011 12 08/02/2011 12:00:00 10 08/02/2011 22:00:00 08/02/2011
Therefore, whenever your timezone is ahead of someone else’s, the date only field when viewed by the other user will always display the previous day (as any number of hours subtracted off of midnight will always be the previous day). This will only be a problem where dates entered in one time zone need to be viewed in another. For example, if an admin in NZ adds Christmas Day to all calendars, it will be on 25 December in NZ and 24 December for everyone else. Note that anyone whose time zone is ahead of yours will always have the correct day (as you will never have to add on more than 24 hours).
Resolution: If the data can be entered locally then you will not experience this issue. If the data must be entered from another time zone, then you will need a plugin on the record to convert the date and time to equivalent of 11pm NZ time (see 4 below for why we use 11pm). Note that the user will never see the time component as this field is displayed as date only in CRM.
3. Populating a data and time field using data import
When importing data, CRM uses the zone of the user importing the data.
Resolution: The simplest fix is to change the timezone of the user importing the records and limiting the records to a single timezone. If you need to add multiple records across time zones in one load (e.g. when loading appointment data for all of Australia), you will need to convert the time to as it would appear in the loading user’s local time. For example, if you are a NZ user (GMT+10) and want to load an appointment for a Sydney user (GMT+10) to show in their calendar from 1000 to 1100, you will need to import it as 1300 to 1400 (the GMT+12 equivalent time). See 4 below if you want to know how to add or subtract hours from a field in Excel.
4. Populating a date only field using data import
If your user is in NZ and you are loading data for any other timezone than it will display the previous day (same logic as 2 above). If, for example, you are loading sales records for Australia which use a date-only transaction date to your NZ based server, then when these records are viewed by an Australian user, they will show a day earlier. The same problem will exist when you run reports – transactions listed on the 1st of a month will get moved to the previous month – which will mean all your figures are slightly out.
Resolution: If a field is configured as date only in CRM, you can still load a time component to this field. For New Zealand, you should add 23 hours so that no matter how many hours you are behind NZ, a date only field will always show the same date (even when adjusted for Daylight Saving although this is not included below).
Hawaii US West Coast (e.g. Redmond) US East Coast (e.g. New York) London Hong Kong Sydney NZ
GMT-9 GMT-8 GMT-5 GMT+0 GMT+8 GMT+10 GMT+12
2:00 3:00 6:00 11:00 19:00 21:00 23:00
Incidentally, If anyone needs me to fly out to Hawaii to rectify time zone issues, I’ll make myself available
To convert a date field in Excel, first format it to a custom formatting of ”dd/mm/yyyy hh:mm:ss”, then use the formula “=[Date Field] + Time (23,0,0)” where [Date Field] is the reference to the field to which you want to add time. A date that started out as 09/02/2011 will then end up as 09/02/2011 11:00:00 p.m. If you are saving your data as a CSV file, you must ensure the formatting is
Note that if you are in any other timezone, the no of hours to add is the number of hours you are ahead of the international date line minus 1
===============
Hi B,
Thanks for a great summary of the date headaches and possible solutions.
Given the problems with them I’m struggling to think of a reason why you would want to design a globalised system to record a date only and not the date and time.
Say that I am a resource NZ and I’m celebrating Christmas then someone in UK will want to know that I’m not available (a.k.a drunk) between midday of the 24rd until midday 25th UK time, not that it is Christmas day on that day. Also if someone in the UK wanted to send me a birthday wishes email they would need to be reminded to send it the day before to reach me in NZ on my Birthday.
Similarly for a deadline, if a task needs to be completed by Wednesday and the task creator is in NZ, then from their perspective the task should be completed by COB Wednesday NZ time regardless of where the task is being carried out. So while is tempting to make the due date Wednesday the task should have a due date-time of Wednesday at 5.30pm in NZ and Wednesday 3.30pm in the Oz. It is tempting to
Also if I understand you solution of adding 11 hours correctly, it will only work for countries that are further east on the globe than GMT + 2, fine for Oz but not ok for the west of this line UK, USA etc.
Cheers
A
=================
Hi A,
I agree that specific tasks which need completed at a specific time should always be date and time.
With the Christmas Day example, to mark this in everyone’s calendar correctly, you would need to create one entry for every timezone called NZ Christmas, NSW Christmas, Queensland Christmas, SA/NT Christmas, WA Christmas etc and you would either need to convert all times to NZ equivalent time or change your timezone before creating each of these if adding them as date only.
The problems occur when you have a global system which is administered and maintained out of one country (and NZ is the worst case scenario). For example, we have to load sales data with a date only field (customer does not have sales time, only date and has no interest in seeing a time field) for all global markets from NZ. These have to be manipulated to show on the correct date for the timezone.
You don’t add on 11 hours, you add on 23 hours, to make it 11pm as per my example below.
Kind regards,
B
Source - Intergen
Server time – this is the timezone of the server. So if you server is installed in NZ, you will be in GMT+12
User time – this is set in the user’s CRM options
Daylight saving – note that to make this email simpler, I have ignored daylight saving. When you are working out time adjustments, you will need add time on appropriately. Daylight saving varies by country and even within countries e.g. Queensland does not have it but NSW in the same timezone does.
1. Populating a date and time field through the UI
Date and time is entered as user’s local time and stored as UTC in the database. When viewed by a user, this is converted to the user’s time zone. This means that if you are an NZ user (GMT+12) and you add an appointment for 1100 NZ time, it will appear as 0900 NZ time to a user in the Sydney timezone (GMT+10). So far, so good.
Resolution: No required, working as expected
2. Populating a date only field through the UI
SQL does not have the concept of a date only and has to store date and time for date only record entered in CRM. It does this by storing the date at midnight local time. This means that if a NZ user (GMT+12) adds a date of 09/02/2011, it will be stored in SQL as 2011-02-08 12:00:00 (midday the day before) so that when you add on the 12 hours, it becomes 2011-02-09 00:00:00. Now, if this date is viewed by someone in the Sydney timezone (GMT+10), it will be converted to their local time by adding on 10 hours to give 2011-02-08 22:00:00 which when you display as a date only field shows as 08/02/2011 – the day before the date entered. In summary:
Date Entered by First User (dd/mm/yy) Time Zone of First User (GMT+no.of hours) Date and Time Stored in SQL Time Zone of Second User (GMT+no.of hours) Date and Time as Converted from SQL time Date Displayed in CRM for Second User
09/02/2011 12 08/02/2011 12:00:00 10 08/02/2011 22:00:00 08/02/2011
Therefore, whenever your timezone is ahead of someone else’s, the date only field when viewed by the other user will always display the previous day (as any number of hours subtracted off of midnight will always be the previous day). This will only be a problem where dates entered in one time zone need to be viewed in another. For example, if an admin in NZ adds Christmas Day to all calendars, it will be on 25 December in NZ and 24 December for everyone else. Note that anyone whose time zone is ahead of yours will always have the correct day (as you will never have to add on more than 24 hours).
Resolution: If the data can be entered locally then you will not experience this issue. If the data must be entered from another time zone, then you will need a plugin on the record to convert the date and time to equivalent of 11pm NZ time (see 4 below for why we use 11pm). Note that the user will never see the time component as this field is displayed as date only in CRM.
3. Populating a data and time field using data import
When importing data, CRM uses the zone of the user importing the data.
Resolution: The simplest fix is to change the timezone of the user importing the records and limiting the records to a single timezone. If you need to add multiple records across time zones in one load (e.g. when loading appointment data for all of Australia), you will need to convert the time to as it would appear in the loading user’s local time. For example, if you are a NZ user (GMT+10) and want to load an appointment for a Sydney user (GMT+10) to show in their calendar from 1000 to 1100, you will need to import it as 1300 to 1400 (the GMT+12 equivalent time). See 4 below if you want to know how to add or subtract hours from a field in Excel.
4. Populating a date only field using data import
If your user is in NZ and you are loading data for any other timezone than it will display the previous day (same logic as 2 above). If, for example, you are loading sales records for Australia which use a date-only transaction date to your NZ based server, then when these records are viewed by an Australian user, they will show a day earlier. The same problem will exist when you run reports – transactions listed on the 1st of a month will get moved to the previous month – which will mean all your figures are slightly out.
Resolution: If a field is configured as date only in CRM, you can still load a time component to this field. For New Zealand, you should add 23 hours so that no matter how many hours you are behind NZ, a date only field will always show the same date (even when adjusted for Daylight Saving although this is not included below).
Hawaii US West Coast (e.g. Redmond) US East Coast (e.g. New York) London Hong Kong Sydney NZ
GMT-9 GMT-8 GMT-5 GMT+0 GMT+8 GMT+10 GMT+12
2:00 3:00 6:00 11:00 19:00 21:00 23:00
Incidentally, If anyone needs me to fly out to Hawaii to rectify time zone issues, I’ll make myself available
To convert a date field in Excel, first format it to a custom formatting of ”dd/mm/yyyy hh:mm:ss”, then use the formula “=[Date Field] + Time (23,0,0)” where [Date Field] is the reference to the field to which you want to add time. A date that started out as 09/02/2011 will then end up as 09/02/2011 11:00:00 p.m. If you are saving your data as a CSV file, you must ensure the formatting is
Note that if you are in any other timezone, the no of hours to add is the number of hours you are ahead of the international date line minus 1
===============
Hi B,
Thanks for a great summary of the date headaches and possible solutions.
Given the problems with them I’m struggling to think of a reason why you would want to design a globalised system to record a date only and not the date and time.
Say that I am a resource NZ and I’m celebrating Christmas then someone in UK will want to know that I’m not available (a.k.a drunk) between midday of the 24rd until midday 25th UK time, not that it is Christmas day on that day. Also if someone in the UK wanted to send me a birthday wishes email they would need to be reminded to send it the day before to reach me in NZ on my Birthday.
Similarly for a deadline, if a task needs to be completed by Wednesday and the task creator is in NZ, then from their perspective the task should be completed by COB Wednesday NZ time regardless of where the task is being carried out. So while is tempting to make the due date Wednesday the task should have a due date-time of Wednesday at 5.30pm in NZ and Wednesday 3.30pm in the Oz. It is tempting to
Also if I understand you solution of adding 11 hours correctly, it will only work for countries that are further east on the globe than GMT + 2, fine for Oz but not ok for the west of this line UK, USA etc.
Cheers
A
=================
Hi A,
I agree that specific tasks which need completed at a specific time should always be date and time.
With the Christmas Day example, to mark this in everyone’s calendar correctly, you would need to create one entry for every timezone called NZ Christmas, NSW Christmas, Queensland Christmas, SA/NT Christmas, WA Christmas etc and you would either need to convert all times to NZ equivalent time or change your timezone before creating each of these if adding them as date only.
The problems occur when you have a global system which is administered and maintained out of one country (and NZ is the worst case scenario). For example, we have to load sales data with a date only field (customer does not have sales time, only date and has no interest in seeing a time field) for all global markets from NZ. These have to be manipulated to show on the correct date for the timezone.
You don’t add on 11 hours, you add on 23 hours, to make it 11pm as per my example below.
Kind regards,
B
Source - Intergen
Wednesday, 8 February 2012
programming scenarios
Since I really bad at creating scenarios, I'm going to make a collection here.
Crm scenarios:
* Update Next Birthday Using a Custom Workflow Activity.
* Create URL link using a custom WF step.
* Validate SSN (social security number) using javascript
* When an account's business phone number changed, all its child contact record need to be updated on that field too.
Crm scenarios:
* Update Next Birthday Using a Custom Workflow Activity.
* Create URL link using a custom WF step.
* Validate SSN (social security number) using javascript
* When an account's business phone number changed, all its child contact record need to be updated on that field too.
When to use workflow and when to use plugin
Use workflow when the business logic must be performed by people who are not developers.
Use workflow when you need the ability to allow user to start a business logic manually.
Use Plugin when the delay between when the event occurs and when the business logic runs is NOT acceptable.
Use workflow when you need the ability to allow user to start a business logic manually.
Use Plugin when the delay between when the event occurs and when the business logic runs is NOT acceptable.
Tuesday, 7 February 2012
Saturday, 4 February 2012
Crm 2011 Sdk helper code summary
ServerConnection class: Frequently used. This class can held information needed to help you connect to and authenticate with Dynamics CRM 2011 using the OrganizationServiceProxy and DiscoveryServiceProxy classes.
DeviceIdManager class: Sometimes used. This class registers a computing device with Windows Live ID. This class is only needed when authenticating with Dynamics Crm Online
SystemUserProvider class: Rarely used. The purpose of this class is to create users in AD and Dynamics Crm 2011
Enumeratoins for Options Sets: Often used. These enumerations can be generated from the CrmSvcUtil code generation tool.
--------The SDK description---------
ServerConnection class:
The ServerConnection class provides methods to obtain server, organization, and user logon information for any Microsoft Dynamics CRM deployment. This information is needed to connect to and authenticate with Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online using the DiscoveryServiceProxy and OrganizationServiceProxy classes. The ServerConnection class is used by most samples that ship with the Microsoft Dynamics CRM 2011 SDK.
DeviceIdManager class:
The DeviceIdManager class registers a computing device with Windows Live ID, through the generation of a device ID and password, and optionally stores that information in an encrypted format on the local disk for later reuse. This functionality is required when authenticating with Microsoft Dynamics CRM Online. The device registration is stored in the %USERPROFILE%\LiveDeviceID folder on the computing device.
The primary class method to call from your code is LoadOrRegisterDevice. For an example of how the DeviceIdManager class is used, see the GetDeviceCredentials method in the Helper Code: ServerConnection Class topic.
A Windows Azure hosted application that must authenticate with Microsoft Dynamics CRM Online should either set the PersistToFile property of DeviceIdManager to false or call the two parameter LoadOrRegisterDevice method passing in the required device ID and password. In both cases, the DeviceIdManager will not attempt to persist the device ID and password to a file.
SystemUserProvider class:
The SystemUserProvider class demonstrates how to programmatically create additional users in Active Directory and Microsoft Dynamics CRM 2011.
Several SDK samples require additional (fictitious) system users to exist in Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online, other than the logged on user, for the sample to perform its intended task. For Microsoft Dynamics CRM 2011, these samples use the SystemUserProvider class methods to create these additional required users. Since it currently is not possible to programmatically create a new Windows Live ID user, when running against a Microsoft Dynamics CRM Online server, the code just prints the required user information to the console. You can then manually add and invite those users if desired.
Enumeratoins for Options Sets:
The SDK download package includes an extension to the CrmSvcUtil code generation tool that you can use to generate enumerations for all option set values including global option sets, picklist, state, and status values. For more information, see Sample Extension to Generate Enumerations for Option Sets.
DeviceIdManager class: Sometimes used. This class registers a computing device with Windows Live ID. This class is only needed when authenticating with Dynamics Crm Online
SystemUserProvider class: Rarely used. The purpose of this class is to create users in AD and Dynamics Crm 2011
Enumeratoins for Options Sets: Often used. These enumerations can be generated from the CrmSvcUtil code generation tool.
--------The SDK description---------
ServerConnection class:
The ServerConnection class provides methods to obtain server, organization, and user logon information for any Microsoft Dynamics CRM deployment. This information is needed to connect to and authenticate with Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online using the DiscoveryServiceProxy and OrganizationServiceProxy classes. The ServerConnection class is used by most samples that ship with the Microsoft Dynamics CRM 2011 SDK.
DeviceIdManager class:
The DeviceIdManager class registers a computing device with Windows Live ID, through the generation of a device ID and password, and optionally stores that information in an encrypted format on the local disk for later reuse. This functionality is required when authenticating with Microsoft Dynamics CRM Online. The device registration is stored in the %USERPROFILE%\LiveDeviceID folder on the computing device.
The primary class method to call from your code is LoadOrRegisterDevice. For an example of how the DeviceIdManager class is used, see the GetDeviceCredentials method in the Helper Code: ServerConnection Class topic.
A Windows Azure hosted application that must authenticate with Microsoft Dynamics CRM Online should either set the PersistToFile property of DeviceIdManager to false or call the two parameter LoadOrRegisterDevice method passing in the required device ID and password. In both cases, the DeviceIdManager will not attempt to persist the device ID and password to a file.
SystemUserProvider class:
The SystemUserProvider class demonstrates how to programmatically create additional users in Active Directory and Microsoft Dynamics CRM 2011.
Several SDK samples require additional (fictitious) system users to exist in Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online, other than the logged on user, for the sample to perform its intended task. For Microsoft Dynamics CRM 2011, these samples use the SystemUserProvider class methods to create these additional required users. Since it currently is not possible to programmatically create a new Windows Live ID user, when running against a Microsoft Dynamics CRM Online server, the code just prints the required user information to the console. You can then manually add and invite those users if desired.
Enumeratoins for Options Sets:
The SDK download package includes an extension to the CrmSvcUtil code generation tool that you can use to generate enumerations for all option set values including global option sets, picklist, state, and status values. For more information, see Sample Extension to Generate Enumerations for Option Sets.
Create a Crm console app from scratch 2. A quick startup program
When writing an application that uses the Microsoft Dynamics CRM SDK, you typically need to perform the following steps to configure your application’s project.
In the project’s properties, set the target framework to .NET Framework 4.
Add the following .NET references to your project:
System.Data.Linq
System.Runtime.Serialization
System.Security
System.ServiceModel
System.DirectoryServices.AccountManagement
Add the required Microsoft Dynamics CRM SDK assembly references. At a minimum, add Microsoft.Crm.Sdk.Proxy and Microsoft.Xrm.Sdk. For a complete list of the assemblies included in the Microsoft Dynamics CRM SDK, see Assemblies Included in the Microsoft Dynamics CRM SDK.
In most cases, you need to install Windows Identity Foundation and add a reference to Microsoft.IdentityModel to your project.
Including the following namespaces
using System;
//using System.ServiceModel; // Maybe needed for more complex crm programs
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Client;
//using Microsoft.Crm.Sdk; // Maybe needed for more complex crm programs
using Microsoft.Crm.Sdk.Messages;
#Use the Sample and Helper Code
In the project’s properties, set the target framework to .NET Framework 4.
Add the following .NET references to your project:
System.Data.Linq
System.Runtime.Serialization
System.Security
System.ServiceModel
System.DirectoryServices.AccountManagement
Add the required Microsoft Dynamics CRM SDK assembly references. At a minimum, add Microsoft.Crm.Sdk.Proxy and Microsoft.Xrm.Sdk. For a complete list of the assemblies included in the Microsoft Dynamics CRM SDK, see Assemblies Included in the Microsoft Dynamics CRM SDK.
In most cases, you need to install Windows Identity Foundation and add a reference to Microsoft.IdentityModel to your project.
Including the following namespaces
using System;
//using System.ServiceModel; // Maybe needed for more complex crm programs
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Client;
//using Microsoft.Crm.Sdk; // Maybe needed for more complex crm programs
using Microsoft.Crm.Sdk.Messages;
using System;
//using System.Collections.Generic;
//using System.Text;
//using System.ServiceModel;
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Client;
//using Microsoft.Crm.Sdk;
using Microsoft.Crm.Sdk.Messages;
namespace CrmConsoleApplication2
{
public class Program
{
private static ClientCredentials _clientCreds;
private static OrganizationServiceProxy _serviceProxy;
public static void Main(string[] args)
{
_clientCreds = new ClientCredentials();
_clientCreds.Windows.ClientCredential.UserName = "Administrator";
_clientCreds.Windows.ClientCredential.Password = "NotTheRealPassword";
_clientCreds.Windows.ClientCredential.Domain = "PLAYGROUND";
using (_serviceProxy = new OrganizationServiceProxy(new Uri("http://localhost:5555/CRM2011RTM/XRMServices/2011/Organization.svc")
, null
, _clientCreds
, null))
{
WhoAmIRequest request = new WhoAmIRequest();
WhoAmIResponse response = (WhoAmIResponse)_serviceProxy.Execute(request);
Console.WriteLine("User {0} has just connected", response.UserId);
}
Console.ReadLine();
}
}
}
#Use the Sample and Helper Code
Thursday, 2 February 2012
Microsoft Dynamics CRM 2011 – Error When Browsing Discovery.svc Service
Reference
http://yellowduckguy.wordpress.com/2012/02/03/microsoft-dynamics-crm-2011-error-when-browsing-discovery-svc-service/
Today I had to resolve a customer issue where the Dynamics CRM 2011 for Outlook Configuration Wizard could not find the CRM 2011 server and the CRM server did exist on the domain.
I was connecting Microsoft Outlook 2007 to CRM 2011 via the installed add-in Microsoft Dynamics CRM 2011 for Outlook.
My list of areas to troubleshoot this issue was as follows:
•Check CRM 2011 server is running ok. Can I browse it from the CRM Server?
•Check I can ping the CRM server from the Outlook client PC
•Check I can browse to CRM from the web browser on the Outlook client PC
•Check I can resolve the Discovery.svc service. CRM for Outlook Configuration tool uses this.
•Check the registry settings for the Configuration settings of CRM for Outlook
The Issue
Going through this list I found I could not resolve the Discovery.svc service from the Outlook client PC. You should be able to resolve the Discovery.svc service using the following URL. http://[yourcrmserver]/XRMServices/2011/Discovery.svc You should see a page similar to below when you can successfully render the Discovery Service.
So I thought I had a possible IIS issue as I couldn’t render the service on the CRM server as well. I decided to check the Event Viewer first for any clues. Below is the error message I received.
WebHost failed to process a request.
Sender Information: System.ServiceModel.ServiceHostingEnvironment+HostingManager/63835064
Exception: System.ServiceModel.ServiceActivationException: The service ‘/XRMServices/2011/Discovery.svc’ cannot be activated due to an exception during compilation. The exception message is: This collection already contains an address with scheme http. There can be at most one address per scheme in this collection. If your service is being hosted in IIS you can fix the problem by setting ‘system.serviceModel/serviceHostingEnvironment/multipleSiteBindingsEnabled’ to true or specifying ‘system.serviceModel/serviceHostingEnvironment/baseAddressPrefixFilters’.
Parameter name: item. —> System.ArgumentException: This collection already contains an address with scheme http. There can be at most one address per scheme in this collection. If your service is being hosted in IIS you can fix the problem by setting ‘system.serviceModel/serviceHostingEnvironment/multipleSiteBindingsEnabled’ to true or specifying ‘system.serviceModel/serviceHostingEnvironment/baseAddressPrefixFilters’.
Parameter name: item
at System.ServiceModel.UriSchemeKeyedCollection.InsertItem(Int32 index, Uri item)
at System.Collections.Generic.SynchronizedCollection`1.Add(T item)
at System.ServiceModel.UriSchemeKeyedCollection..ctor(Uri[] addresses)
at System.ServiceModel.ServiceHost..ctor(Type serviceType, Uri[] baseAddresses)
at Microsoft.Crm.Sdk.V5.DiscoveryServiceHostFactory.CreateServiceHost(String constructorString, Uri[] baseAddresses)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.CreateService(String normalizedVirtualPath)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.ActivateService(String normalizedVirtualPath)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)
— End of inner exception stack trace —
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)
at System.ServiceModel.ServiceHostingEnvironment.EnsureServiceAvailableFast(String relativeVirtualPath)
Process Name: w3wp
Process ID: 4116
From this error message it seems their is a http bindings issue within IIS. Then it came to me! One of our infrastructure guys had created another port binding for CRM to be on port 80 for some other unrelated testing.
Resolution
Therefore to resolve this I had to remove the extra http binding in the Microsoft Dynamics CRM website within IIS. To change or investigate your site bindings, click on
Bindings…
under
Edit Site
in IIS as shown below.
You should only have one http binding in the list as shown below. Note: I have removed the binding which was causing the problem in the image below.
Its possible to have multiple http bindings, but you will need to change some web.config values which contain
multipleSiteBindingsEnabled
and set it to
true
. See the error message earlier for more information. Hope that helps! Greg Olsen
http://yellowduckguy.wordpress.com/2012/02/03/microsoft-dynamics-crm-2011-error-when-browsing-discovery-svc-service/
Today I had to resolve a customer issue where the Dynamics CRM 2011 for Outlook Configuration Wizard could not find the CRM 2011 server and the CRM server did exist on the domain.
I was connecting Microsoft Outlook 2007 to CRM 2011 via the installed add-in Microsoft Dynamics CRM 2011 for Outlook.
My list of areas to troubleshoot this issue was as follows:
•Check CRM 2011 server is running ok. Can I browse it from the CRM Server?
•Check I can ping the CRM server from the Outlook client PC
•Check I can browse to CRM from the web browser on the Outlook client PC
•Check I can resolve the Discovery.svc service. CRM for Outlook Configuration tool uses this.
•Check the registry settings for the Configuration settings of CRM for Outlook
The Issue
Going through this list I found I could not resolve the Discovery.svc service from the Outlook client PC. You should be able to resolve the Discovery.svc service using the following URL. http://[yourcrmserver]/XRMServices/2011/Discovery.svc You should see a page similar to below when you can successfully render the Discovery Service.
So I thought I had a possible IIS issue as I couldn’t render the service on the CRM server as well. I decided to check the Event Viewer first for any clues. Below is the error message I received.
WebHost failed to process a request.
Sender Information: System.ServiceModel.ServiceHostingEnvironment+HostingManager/63835064
Exception: System.ServiceModel.ServiceActivationException: The service ‘/XRMServices/2011/Discovery.svc’ cannot be activated due to an exception during compilation. The exception message is: This collection already contains an address with scheme http. There can be at most one address per scheme in this collection. If your service is being hosted in IIS you can fix the problem by setting ‘system.serviceModel/serviceHostingEnvironment/multipleSiteBindingsEnabled’ to true or specifying ‘system.serviceModel/serviceHostingEnvironment/baseAddressPrefixFilters’.
Parameter name: item. —> System.ArgumentException: This collection already contains an address with scheme http. There can be at most one address per scheme in this collection. If your service is being hosted in IIS you can fix the problem by setting ‘system.serviceModel/serviceHostingEnvironment/multipleSiteBindingsEnabled’ to true or specifying ‘system.serviceModel/serviceHostingEnvironment/baseAddressPrefixFilters’.
Parameter name: item
at System.ServiceModel.UriSchemeKeyedCollection.InsertItem(Int32 index, Uri item)
at System.Collections.Generic.SynchronizedCollection`1.Add(T item)
at System.ServiceModel.UriSchemeKeyedCollection..ctor(Uri[] addresses)
at System.ServiceModel.ServiceHost..ctor(Type serviceType, Uri[] baseAddresses)
at Microsoft.Crm.Sdk.V5.DiscoveryServiceHostFactory.CreateServiceHost(String constructorString, Uri[] baseAddresses)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.CreateService(String normalizedVirtualPath)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.ActivateService(String normalizedVirtualPath)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)
— End of inner exception stack trace —
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)
at System.ServiceModel.ServiceHostingEnvironment.EnsureServiceAvailableFast(String relativeVirtualPath)
Process Name: w3wp
Process ID: 4116
From this error message it seems their is a http bindings issue within IIS. Then it came to me! One of our infrastructure guys had created another port binding for CRM to be on port 80 for some other unrelated testing.
Resolution
Therefore to resolve this I had to remove the extra http binding in the Microsoft Dynamics CRM website within IIS. To change or investigate your site bindings, click on
Bindings…
under
Edit Site
in IIS as shown below.
You should only have one http binding in the list as shown below. Note: I have removed the binding which was causing the problem in the image below.
Its possible to have multiple http bindings, but you will need to change some web.config values which contain
multipleSiteBindingsEnabled
and set it to
true
. See the error message earlier for more information. Hope that helps! Greg Olsen
Subscribe to:
Posts (Atom)