Kevin Hughes on Dynamics CRM

Got a SQL DB stuck in restoring?

2011-03-01T04:44:00.000-08:00

Then use the following script:

RESTORE DATABASE [DATABASE NAME] WITH RECOVERY

Taken from
http://www.execsql.com/post/bringing-a-database-out-of-restoring-state-in-sql-server-2005

Creating SSRS Reports that fit on to an A4 Sheet

2011-02-18T07:13:00.001-08:00

One requirement that always come up when a customer requests a report is that it must print (or export to PDF) onto a sheet of A4.

This is often for summary reports that rollup useful information onto one page, such as an Event's Sessions and Attendees, so that the user can take a copy with them.

The trick to this is to set the report’s page width to 8.27 inches and the height to 11.69 inches.

This tip is taken from a post on Botsikas' Blog, which I have referred to many times!
http://botsikas.blogspot.com/2008/02/sql-reporting-services-and-a4-paper.html

ExecuteNonQuery Error after redeployment

2010-05-10T02:52:00.000-07:00

Very useful article just saved me from a day of frustration.

When redeploying an organisation database from a foreign environment, the app pool will not have permission to the new database and must be configured in the SQL database's security settings.

http://www.langalaxy.de/2009/11/system-invalidoperationexception-at-crm-logon/

Rename SQL Server Instance

2010-04-14T03:10:00.000-07:00

For a renamed computer that hosts a default instance of SQL Server, run the following procedures:
sp_dropserver GO sp_addserver , local GO
Restart the instance of SQL Server.

For a renamed computer that hosts a named instance of SQL Server, run the following procedures:

sp_dropserver  GO sp_addserver , local GO

Restart the instance of SQL Server.

Then:

SELECT @@SERVERNAME AS 'Server Name'

From:

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

Field Level Security White Paper released by Microsoft

2009-11-24T05:22:00.000-08:00

Often considered an unsupported customisation to Microsoft CRM 4.0, Microsoft have released a whitepaper detailing the supported methods to secure fields, not entities.

In brief, the document provides consideration to all angles of accessing data, and with forms in mind suggests that designers secure both the field with JavaScript (for accessibility purposes) and through Pre Stage Plug Ins to eradicate non authorised entries by simply removing them from the Propertybag.

You can download the white paper here:
http://download.microsoft.com/download/3/D/9/3D9D0AD7-A6E5-49DC-8963-C0B223250EEF/CRM4%20NB%20-%20SEC%20-%20Field-Level%20Security.pdf
(The googled link appears to be down..)

Email Router logging unresolved Email Addresses

2009-04-30T08:00:00.000-07:00

If you are receiving too many errors in the Event Viewer as your Email Router is logging every email that it can't resolve, you can change the logging level by:

Opening regedit on the server where the email router is installed
Navigating to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSCRMExRouterService
Changing the LogLevel value from 1 (default) to 2
Restarting the Email Router Service

SQL Date Formats

2008-11-17T05:37:00.001-08:00

Just stumbled across this page listing Date formats and the SQL to produce them, all in a handy table.

Very useful when producing SSRS reports..
http://www.sql-server-helper.com/tips/date-formats.aspx

Plugin Registration Tool

2008-11-13T06:23:00.001-08:00

Just a quick tip.

if you need to run the plugin registration tool included with the CRM 4.0 SDK using default credientials, leave the Domain Name blank and you'll log straight in.

Useful if you get an unauthorised 401 errors trying to connect.

Adding a Custom Notification to the top of a Microsoft CRM Form

2008-11-04T08:19:00.000-08:00

I've decided to update my blog on a more regular basis to give a bit back to those that have helped me so far, as well as an excuse to play around with CRM a little more often!

So to kick things off, here is some javascript I wrote for a customer to add a custom notification to the CRM form, this provides a CRM stylised banner to the top of the form, much like those on products and service activities. Add this javascript to your form onLoad() event and add any conditions as necessary.

Hope it helps!

var warningHtml =
"<table callpadding='0' cellspacing='0'><tr valign='middle'><td><img src=''../_imgs/ico/16_alert.gif'' /></td><td><b>Warning Message</b></td><tr></table>'";

var oDiv = document.createElement("div");
oDiv.id = "new_warning";
oDiv.className = "Notifications";
oDiv.style.padding = "3px";

document.crmForm.all.Notifications.parentNode.appendChild(oDiv);

var theDiv = document.crmForm.all.new_warning;
theDiv.style.display = "";
theDiv.innerHTML = warningHtml;

P.S. This is an unsupported development, usual caveats apply.

Advanced Find in CRM 4.0 - Same entity relationships not allowed?

2008-02-20T09:24:00.000-08:00

I'm currently having some trouble upgrading some functionality used in CRM 3.0 to CRM 4.0
Basically the model is..

a contact has a group of interests
these interests exist in a seperate entity
the contact is connected to an interest through a link entity (contact-interest). (basically making a many to many)

The user can then use an advanced find to search for all contacts with a certain set of interests. For example:

Contact 1 has interest A and interest B
Contact 2 has interest A

The user wants to find contacts with interest A and interest B

An advanced find with contacts with contact-interests where interest equals and selecting A and B in the lookup serves as an OR and finds both contacts.

An AF where interest equals A and a seperate line for interest equals B finds neither (the interest lookup on contact interest can not be both).

So, the only way to get this to work is to have two contact-interest lines....

Find Contacts
Contact Interest (Contact)
Interest = A
Contact Interest (Contact)
Interest = B

This works in CRM 3.0 as it will find contact 1 who has both.

Problem is, CRM 4.0 doesn't allow you to create duplicate clauses to the same entity as above and comes up with the following error:

"The relationship you are adding already exists in the query"

Does anyone know of a way round this? It looks like the CRM team have chosen to block this and I'm not sure why!

Skype and Microsoft CRM

2007-10-29T04:54:00.000-07:00

Recently I’ve been a bit puzzled as to why when using CRM inside internet explorer on my laptop, IE would freeze for around 30 seconds when trying to view a list of accounts or contacts.
After such a freeze, IE and CRM would come back to life and work perfectly.

The problem lies in the new version of Skype which includes a very natty IE plugin that will scrap the screen for phone numbers and allow you to directly dial out.

As you can imagine, a list of 250 (my personal preference) accounts or contacts each with one or more phone numbers on the list was a little too much of a feast for the plug in and caused it to hang and eventually crash silently before IE would come back to life.

More interesting is that I disabled the plugin through the plugin manager in IE7 and it was still causing the issue. Only after a full uninstall did the issue go away.

Don't delete the System Customizer Role

2007-04-26T01:25:00.000-07:00

After a client recently cleaned their CRM system ready to bring in a new department, I noticed that I could no longer create new entities.

This it seems, is a bug in CRM. If the System Customizer role has been deleted (and it wont try to stop you), a SQL query will return null and throw an error.

The only way round it (apart from a reinstall), is to get a backup restored on to a temp db and copy the system customizer row from the rolebase table back to the live db.

You don't need to copy the rows from the roleprivilegesbase table (it wont have any privileges)

A different way of looking at this is, if anything has got the word "system" in it, don't delete it!

Restricted Access Mode and FilteredViews

2007-03-28T07:50:00.000-07:00

During a support issue with one of my clients, I noticed a unexpected result when using filtered views which allows access to data without consuming a user license.

It would appear that when a user is put in restricted access mode (access to settings but no data) in Microsoft CRM 3.0 the user is not restricted access to data made available through the Filtered Views, yet the user's security roles are still considered and data is filtered accordingly.

I'm yet to see whether this still is the case using the Web Services, but it would appear that only the application checks to see if the user is in RMA.

If this is the case, expect it to be plugged.

Fed up with constructing FetchXML / QueryExpressions?

2007-03-28T07:43:00.000-07:00

If so,
check out Stunnware's excellent fetchXML wizard including the option to convert to C# Query Expressions

http://www.stunnware.com/crm2/topic.aspx?id=FindingData6