Useful Out-of-the-Box SQL Functions for MSCRM

There are some very useful SQL functions that are built into the MSCRM database that are available to be used in the select queries to write reports, pull data into Excel, or just to check the data in your database. Some of the more notable functions, at least from what I think, are the following:

1. F_START_OF_WEEK (gets the first day of a week, and you can specify which day you want to be the start of your week, Monday, Tuesday, etc)
2. fn_LastXMonth (this is what Advanced Find uses, this is fairly easy to replicate with SQL but it gets even easier if there is a function to be used)
3. fn_FindUserGuid (this gets the logged in user's GUID, this is how Advanced Find figures out "Equals Current User")
4. fn_GetMaxUserPrivilege (tells you whether a user has sufficient rights to a specific privilege,)
5. fn_RptBracket (puts numbers in groups, for instance fn_RptBracket(75, 50) would return 50 - 99 . This translates to placing the number 75 into numbers 1-49, 50-99, 100-149, etc. Basically the first number is the number you want to group and the second is the interval at which you want your groupings. Play with it a bit to see how it works. It could be very useful for reporting)

To find all of the functions open up SQL Management Studio and expand the MSCRM database, then expand "Programmability", expand "Functions", and finally, expand "Scalar-valued Functions" to see the full list. Right click a function and choose "Script Function As", "Create To", "New Query Editor Window" to look at the code behind the function.

Some very cool things to find and use, hopefully this makes some report queries much more simple and other data check queries easier to write.

David Fronk
Dynamic Methods Inc.

Reassociating MSCRM Users with Active Directory Users

Back in the version 1.0 and 1.2 days fixing CRM user associations was a huge pain. Users would get deleted from Active Directory or CRM users would need to be reassociated with a different user in Active Directory. These tasks were either impossible to perform or nearly impossible to recover from. In these instances, MS Support had to get involved to reassociate records owned by that user through the back end with some of their utilities.

When version 3.0 came out finally users had the ability to use Deployment Manager to reassociate CRM users to Active Directory users. This helped a lot.

In version 4.0 the ability to reassociate CRM users with Active Directory users through Deployment Manager has gone away. When I discovered this I was very upset because I support numerous companies and inevitably, between all my clients, I will get a handful of these type of issues a year. So, having that utility was a reasonably big deal to me. After some poking around I found that reassociating users in MSCRM 4.0 is actually even easier than it was in 3.0. Since I was expecting some special command or utility I never even noticed it.

If you open up a user's card in CRM in all other versions the Username field has been locked down as soon as you save the record. In 4.0 this is not the case. All you have to do to reassociate the user record with a different Active Directory record is change the username. The autopopulate of the fields still occurs and everything gets reassociated.

Now you're ready to rock and roll and keep all of your users between MSCRM and Active Directory in sync.

David Fronk
Dynamic Methods Inc.

MSCRM 4.0 Rollup 1 is now available

This rollup fixes over some 80 issues with the server, client and email router (each a separate download and install). We've applied this in-house and had no issues as of yet, just improvement. To download click below:

http://www.microsoft.com/downloads/details.aspx?FamilyID=57c6267b-3b13-49dd-bfed-3cc83633aea7&displaylang=en

Also, please note that not all recent hotfixes made it into this rollup. I am aware of at least 2, a hotfix for the deletion service and one for issues with the Word Mail Merge connector. But each of those hotfixes are over 100MB.

I'm a fan of rollup 1 so far but if you're applying to fix specific issues just make sure that the appropriate hotfix is included so that you do actually fix the issue you are having.

David Fronk
Dynamic Methods Inc.