Performance of Custom Queries

I got this tip from a developer at a client of mine a while back and I've been meaning to share it. They had created some custom aspx pages that lived inside the CRM sitemap that helped users search across CRM and other data. The CRM queries were using FetchXML just like a regular advanced find view, but they were performing really slowly. Sometimes they were taking as long as 16 seconds to return results. No way were the users going to live with that.

After some digging around, a DBA finally was consulted (maybe we should have asked him for his opinion earlier!). It didn't take him long to give us a helpful tip that led to fixing the query and getting it to return results in under 1 second.

The tip?
It turns out that SQL will ignore indexes when a query is run against a table when the query has a LIKE clause that uses "CONTAINS" (basically where there's a % sign before and after the value), as in

SELECT * FROM contacts WHERE lastname LIKE '%Smith%'

The remedy? Form your queries so they use the equivalent of a "STARTS WITH" so SQL will use the index:

SELECT * FROM contacts WHERE lastname LIKE 'Smith%'

US States Picklists Values

How many times have you had to make a picklist for the US states on a CRM 4.0 form? Well, rather than having to manually add each of the 50 states (52 if you count DC and Puerto Rico), I've got an XML file pre-made with the values of the US States that I copy into the customizations.xml for the particular picklist attribute I'm editing. I thought I'd share it here to save you some time as well.

Download it here.

To use it, export the XML for the entity you are working with, and open it in Visual Studio or your favorite XML editor (Notepad will work). Do a find to locate the picklist attribute you want to edit, and look for the node under it. It should look something like this:

Copy the contents of the downloaded file and then select everything from the starting
tag to the ending tag in the customizations.xml and paste the contents of your clipboard into it. Verify that the XML looks correct, save your customizations.xml and import it back into CRM. Publish the entity and, voila, you have all 50 states available from your picklist.

UPDATE: Due to popular demand, I've also posted a similar text file that includes the countries of the world (265 of them). Note that this list has the United States and Canada listed first and second, with the rest of the world's countries in alphabetical order.

Agent Communications Panel for CRM - Call Center Functionality Using OCS

Microsoft recently released the "Agent Communications Panel for Microsoft Dynamics CRM 4.0" which provides an out-of-the-box integration with Office Communications Server for call center functionality within CRM.

"The Agent Communications Panel for Microsoft Dynamics
CRM 4.0 is an application add-in to Microsoft Dynamics CRM 4.0. It works with
Microsoft Dynamics CRM and Microsoft Office Communications Server 2007 R2, and
enables agents to manage their communications (make calls, receive calls,
conference, and chat) from their Microsoft Dynamics CRM system. The Agent
Communications Panel is an XAML browser application (XBAP). It is published to a
Web server and opened from a Web browser."

I haven't had a chance to set it up and play with it yet, but it looks like it's a logical extension to CRM that should be well-received. You can download it here.

Tag: You're It!

At Convergence, one of the keynote speakers demoed a new beta technology called "Tag." Similar to a barcode, tags are colorful little unique labels that, when photographed with a properly-equipped mobile device, can act as hyperlinks from physical objects to websites or contact cards out in the ether.

The idea is that people can download the little mobile app onto their phones, and if they see a "tag" on a product, or billboard, or business card, etc., they can snap a picture and their phone will automatically link to the website URL encoded in the image. There's been a similar app for the iPhone that lets users upload a barcode and get product information from the web, but in the case of tags, Microsoft is currently allowing anyone to create a tag (just go to microsoft.com/tag and sign up), and the algorithm behind the tags allows for more data to be stored in it than a typical black and white QR or datamatrix code.

What I haven't learned yet is whether there's an API so you can submit URL strings through a webservice and get back tags in a binary format that you could stuff into a database. I'm thinking about applications for CRM: For example, let's say you have field service reps who have to do service on machinery. They might have a mobile device with access to CRM data. Tags could be used to store a link to the machinery's record in CRM, and the field service rep could pull up service history and related customer information just by snapping a pic of the tag on the piece of machinery. That's just one possibility. But for this to work, you'd want to have a custom app in CRM that generates tags every time a new "machinery" record was created. Hmmm... lots of possibilities.

How To Uninstall SQL Server 2008 Reporting Services

I was setting up a CRM development server with SQL 2008 and made the quick, uninformed, and ultimately time-consuming decision to install Reporting Services in SharePoint integrated mode. I was also going to install WSS 3.0, so I thought I'd try this out.

Unfortunately, I found out later that CRM 4.0 does not support reporting services in SharePoint integrated mode. So, if I had been thinking clearly, this would have been a not too difficult problem: just uninstall reporting services and reinstall it in the right mode.

My problem: I couldn't figure out for the life of me how to uninstall Reporting Services for SQL 2008. I tried everything I could think of to remove it. It wasn't listed separately under the Programs and Features list, and I couldn't find any other way despite hours (ugh!) of searching.

I finally figured out that if I went to the Add/Remove Programs area (Programs and Features in Server 2008), and started an uninstall of SQL Server 2008, I would get the option to select which feature of SQL I wanted to remove. Duh. I feel like an idiot.

Will Your Business Survive or Thrive in '09?

This is a bit of an open-ended article - I'm really looking to spark a discussion and hear feedback from you: In thinking about the "current situation" that we've all heard so much about, and the apprehension that many people have for what 2009 might bring, it occurred to me that both of these things - the turbulence of the last quarter and the uncertainty for the future - have implications for businesses that make use of CRM systems.

It's an axiom of recessionary times that businesses that excel at keeping their existing customers and who increase their profile in the marketplace will have a better chance at survival, whereas companies that fail on either of these two fronts will have a much harder time staying afloat. Companies that make effective use of CRM - and who are improving their use of CRM with tools like social media - should have a competitive advantage in this environment.

So my questions for discussion are: Does your company see CRM as a competitive advantage? And what are your plans for improving your use of CRM in 2009 to maintain or improve your competitive standing?

Create a Link to a File or Directory from a CRM Record

If you are like many of our clients, you still retain a lot of Word and Excel documents about your customers. Wouldn't it be nice to be able to link directly to the appropriate folder from the CRM record for that customer? Here's how to use some OnLoad script to turn a regular text field into a link to a folder.

1) Add a text field attribute called 'folderlink' to your account form. Give it a maximum length of 300 characters to make sure you have enough space for long paths.
2) Paste the following code into the OnLoad event for the Account entity's form:

if (crmForm.all.new_folderlink != null)
{
crmForm.all.new_folderlink.style.color = '#0000FF';
crmForm.all.new_folderlink.style.textDecorationUnderline = true;
crmForm.all.new_folderlink.style.fontWeight = 'bold';
var folderunc = crmForm.all.new_folderlink.DataValue;
{
crmForm.all.new_folderlink.ondblclick = function()
{
window.open(folderunc);
}
}
}

3) That's it. Now save and publish your form and paste a path to a folder into that field and try it out. You'll notice that the script above also formats the text in the field to be blue, bold, and underlined so it looks like a standard link, helping your users intuitively understand that they can click on it to open the folder.

Line Breaks for nvarchar TextArea fields in SQL Reports

This one had me stumped for a while. I was writing a report for a CRM client who had a number of nvarchar textarea fields on an entity in CRM. The fields had lots of free-form text with line breaks in them. For example:

This is line 1.
This is line 2.
This is line 3.

When I was creating the report in Visual Studio/SQL Reporting Services, the fields rendered correctly, but when I deployed it to CRM and ran the report, all the lines ran together, like this:

This is line 1. This is line 2. This is line 3.

I tried everything I could think of to format the field in the report layout, but nothing worked. After some googling around, I realized I needed to do some manipulation in my query. So I changed the relevant part of my select statement to something like this:

REPLACE(CAST(CRMAF_FilteredEntity.new_CustomField AS nvarchar(MAX)), CHAR(10),
CHAR(13) + CHAR(10)) AS CustomField

What this does is renders the field and replaces the line feed ("CHAR(10)") with both a Carriage Return and a Line Feed ("CHAR(13) + CHAR(10)"). And now my reports render correctly!

More than 100,000 visitors since I started tracking!

Some time in the last few days my blog had its one hundred thousandth visitor since I started tracking the site's traffic! I started this blog back in November 2004 in order to have a place to keep my CRM-related ideas and tricks where I could reference it easily and share it with a few other people. I started tracking the site's traffic about 6 months later when I realized I was getting a lot more visitors than I anticipated. Since then I have posted 125 posts and have had many more visitors than I ever thought I would. I've also had the opportunity to meet a number of folks through the blog as well. Here are some recent statistics on the site's traffic as of 3/20/2008 at 9pm EST::

Total Visitors: 100,799
Average Per Day 136
Average Visit Length 2:01
Last Hour 4
Today 174
This Week 954

Thanks to everyone who has visited and especially to those who have commented on the blog and shared their experiences with Microsoft CRM!

Help Wanted! Hiring CRM Consultant/Developer

Over the last year, our firm, Customer Connect, has experienced a tremendous upsurge in demand for Microsoft CRM. With the recent release of 4.0, that demand is only increasing. As a result, we are actively recruiting talented, experienced CRM consultants and developers. If you (or someone you know) is interested and has the technical chops, please visit our Careers page (http://www.customer-connect.com/Careers/tabid/384/Default.aspx) and read the full job description and submit a resume.

A little background on Customer Connect: We are located in the Charlotte, North Carolina area. Our firm is a small group of talented professionals with a passion for delivering excellence and value. One of the main criteria for this role is that the individual must have an "ownership" mentality; that is, they must take ownership of the successful outcome of their projects and ownership of the relationships we build with our clients. Each of our team members is given a lot of independence to make decisions and guide our clients through the complexities of rolling out a CRM solution.

CRM Workflow Emails Not Being Sent

I hope this helps anyone who runs into a similar problem: I ran into a problem on a client's CRM server yesterday where the emails being generated from CRM's workflow rules were not being sent out, causing the workflow rule to fail. This had been working in the past, but suddenly it was not. There were a bunch of failed and paused rules in the workflow monitor. taking a look at the failure messages there revealed two different messages:
"Invalid association. the request association is not valid." and "Invalid Visibility Modification Access."

Both of these error messages, along with an error on the CRM server's event log, pointed me to look at the antivirus software. I had run into a problem once before with a CRM server that was running McAfee antivirus that was blocking outbound traffic on port 25 - the port that carries SMTP mail. This client also was running McAfee, and some googling turned up the following KB article (support.microsoft.com/kb/916925) which, among other things, suggests placing the W3wp.exe process in the antivirus software's exclusion list. (I'm not sure if this problem only occurs with McAfee, it just so happens that these are the two cases I've come across, which was helpful since it helped me narrow down the problem a little more quickly than I might have.)

The only problem was that the W3wp.exe process was already excluded from being blocked by the A/V software. In this instance, the CRM server was relaying mail to the Exchange server for outbound SMTP mail, so I thought I'd change the registry settings to make it use the SMTP service in IIS on the CRM server. Before doing this, however, the IT wiz at my client's company discovered that the AntiVirus software was also blocking the CrmWorkflowService.exe - something that's not listed in Microsoft's KB article. By adding this to the exclusion list, workflow email started flowing again.

New CRM MVP Blogger: David Jennaway

David Jennaway, a knowledgeable CRM MVP in the UK, has started a blog that is already rich with useful information. Thanks, David, and keep up the good work!

Link: http://mscrmuk.blogspot.com/

So Much To Learn: Microsoft's Neural Network Algorithm

If you work in the world of technology like I do, perhaps you can empathize with the way I felt this morning after reviewing a presentation forwarded to me about a really high-end demonstration of Microsoft CRM that uses SQL Analysis Services and the Analytics Foundation for predictive leads analysis and other cool things.

There were a lot of powerful ways that this demonstration used historical data to predict which leads would close on a purchase, recommend products associated with an opportunity, and even automagically generate a target marketing list based on - and this is what caught my attention and led to my feelings of technological inadequacy - "Microsoft’s neural network algorithm."

A description of the neural network algorithm from the MSDN website didn't help to clarify matters for me: "...the Microsoft Neural Network algorithm creates classification and regression mining models by constructing a Multilayer Perceptron network of neurons." (More about this here.)

Don't I feel stupid. I thought neurons were part of the brain. Just when I thought I was starting to master the technologies underlying CRM, this comes along. I need to take a month off so I can do some reading and research!

I'm Moving To A New Firm

I have recently accepted a position with a CRM consulting firm here in the Charlotte, North Carolina area: Customer Connect. Customer Connect's focus is on CRM consulting, and I will be spending most of my time on Microsoft Dynamics CRM. I'm looking forward to the change and being able to develop my knowledge and skills further while serving Customer Connect's clients. Check out their website here. If you need the assistance of an experienced and well-rounded CRM team, drop us a line!

Get a Glimpse of CRM Live - Microsoft's Hosted CRM

Brad Wilson, Microsoft's general manager for CRM has posted a video where he demonstrates some of the features of the next version of CRM, which will be available as a hosted, subscription-based service, in addition to the on-premise or partner-hosted platforms. The video can be seen here.

New CRM Developer Center on MSDN

Microsoft's recently re-designed MSDN website now includes a new Microsoft Dynamics CRM Developer Center at http://msdn2.microsoft.com/en-us/dynamics/crm/default.aspx. This page includes links to the CRM SDK, development resources, and a very interesting new page they are calling a roadmap for CRM technologies -- a list of links to all of the different technologies that make CRM work, such as XML, SOAP, Exchange Server, the .NET framework and more. If you are a developer new to CRM, this is definitely the place to start.

KB Article About CRM License Issues When Using MSDN License

Microsoft has published a KB article which addresses issues faced by organizations that have deployed CRM using license keys from their MSDN subscriptions. I see this scenario pretty frequently, and the article presents concise info about what you need to do if you want to move CRM to a production environment.

My Guest Post on the Microsoft CRM Team Blog On MSDN

Check it out! I was invited to post on the CRM development team's blog on MSDN.

I posted on how to show context-relevant files and folders within a CRM record. Hope you find it useful!

At the Global MVP Summit

I am at the Global MVP Summit (http://mvp.support.microsoft.com/MVPsummit) this week in Seattle, Washington, USA. After traveling much of the day from Charlotte, North Carolina, I am wiped out, but excited to catch up with other CRM MVPs and Microsofties. Tomorrow the Summit opens with a keynote speech from Bill Gates. Everything else is under the strictest of confidence and I'm not supposed to blab about it on my blog, but rumor has it that Microsoft will be using the Summit to unveil their planetary death ray and introducing the developer responsible for programming Dick Cheney.

Seriously, though, I need to thank my loyal blog readership and the CRM community -- without them I wouldn't have hung in long enough to make it here. Thanks!

Daylight Savings Time Changes Updates For Microsoft CRM 3.0

The federal government of the United States changed the start and end dates for Daylight Savings Time, effective in 2007. These changes impact every software application that uses the date and time in any way, and that includes not only Microsoft CRM, but the entire technology stack that it sits on (Windows Server, IIS, SQL Server, Exchange Server). Microsoft has released updates for all of these programs to adjust them for the new Daylight Savings Time, which goes into effect on March 11, 2007.

The place to get started is here: Microsoft Dynamics CRM 3.0 Time Zone Data Updates (KB 925874) and Microsoft Dynamics CRM 3.0 Update Time Zones Wizard (KB 932984). On this page you can download a file called CRM_DST_Remediation_details_Rev1.0.exe which is a 28-page (yikes!) Word document that is in a self-extracting file. This Word document details the steps you should follow to update the entire infrastructure and then the steps required to update Microsoft CRM. The juicy part starts on page 9 of the document, with a step-by-step how-to on updating CRM.