Upgrade to Sage CRM 7.2!

Sage CRM 7.2, has been released for almost a year now. If you’re a business owner or an IT manager who rely on the earlier versions of Sage CRM to facilitate your day-to-day operations, then it’s about time you look into what Sage CRM 7.2 has to offer. This latest offering from Sage gives your organisation the edge to be able to communicate with your customers through mobility, social media and business collaboration.

In today’s blog, I’ll be walking you through a detailed procedure on how you can upgrade to the latest version of Sage CRM, version 7.2, and enjoy its new features. So, let’s get going!

Preparing for a Test Upgrade

Sage has released three hot-fixes since the release of Sage CRM 7.2 (b, c, d). Sage CRM 7.2d can be upgraded from 7.1 / SP1 / SP2. For any earlier version, it has to be first upgraded to 7.1. It is recommended to upgrade from 7.1 to SP1, and then to SP2 before upgrading to 7.2.

Before performing the upgrade, it is very essential that you build a test environment to mirror your production environment. This will enable you to test the upgrade in a controlled manner and also inform you about the possible issues that you may encounter during the live upgrade, in advance.

Prior to building the test environment, make sure you’ve got the following items in place:

System Requirements: You can refer to Sage CRM 7.2’s software support matrix to understand the server, client and integration platform which Sage CRM 7.2 will support.

License Key: The license key for version 7.2 can be acquired from Sage.

Hot-Fixes: The hot-fixes (b, c, d) can be downloaded from the Sage portal.

Server: If the existing production server does not match with the server requirements of version 7.2, then it’s recommended to prepare a new server before the upgrade.

Once you have the above things ready, it’s time to build a test server on which you will be performing a test upgrade. So let me brief you about one of the ways you can build one.

What is Hyper-V?

Hyper-V is Windows Server virtualization platform that is included as a role of Windows Server 2008 and 2012. It’s a cool technology, in which you can create multiple snapshots of your software installations (virtual machines), thus enabling you to build your test environment incrementally.

You can begin with installing the server operating system, database, and Sage CRM similar to the versions you have on your production environment. Following this, you can upgrade the Sage CRM to the desired version by running the correct service packs and patches.

You can divide the above installation in the form of ‘snapshots’ that allows you to revert back to the previous installation in case something goes wrong. Following is a screen shot of a Hyper-V interface. Creating virtual machines and snapshots in Hyper-V demands a separate space of its own, and I plan to write a small blog-tutorial on that very soon!


With the above things ready, you can take the following steps to perform a test upgrade on the virtual machine you’ve created in Hyper-V:

  • Backup the live database, registry, program files of the existing CRM system, and copy them in an accessible location on your virtual machine.
  • Install the existing version of Sage CRM on the test server.
  • Restore the backup of the live database in Sage CRM’s database created in the database server on your test server. You can run the following script for a hassle free database restore:

Use Master




FROM DISK =’C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak’


  • Copy the backup program files (custom page folder etc.) to the relevant folder in the test environment.
  • Test the data, functions, reports and customisations in the test CRM system.
  • Change some fields in the Custom_SysParams table of the CRM database as required.
  • Upgrade to 7.1 / SP1 / SP2 if the current version is earlier than 7.1 and test the upgrade.
  • Upgrade to 7.2 from 7.1 SP2 and test the upgrade.
  • Outlook plugin may be applied to the client workstations as required.

Testing the exchange integration

It will be easier to test an exchange integration if the exchange server is on a local domain network. If it’s hosted outside, then you will have to liaise with your system administrator for SMTP access.

Live Upgrade

The procedure for performing a live upgrade is very similar to the test upgrade. Performing a test upgrade helps you to know the possible errors and corresponding solutions you might encounter during the live upgrade.

Hence, I will highly recommend you to document the whole test upgrade process and use it as a guide during your live upgrade.

You must be also very mindful of the CRM down-time during the live upgrade, as it is needed by your staff for daily operations.  The test upgrade should guide you well in this case.

Well, with that, I’ll conclude my blog. As discussed earlier, there are many ways you can create a test environment for performing a test Sage CRM upgrade. How about sharing your thoughts about the same? Let the good ideas flow!

You can also refer to this Sage article on the CRM community for further information:


Escalation Rules – The Basics

Escalation Rules

I.T. based solutions and I.T. software in general came about as a need to automate tasks. It allows businesses to focus more on their business objectives and not worry about the system that supports it. Escalation Rules allows the creation of automated tasks from notifications, setting values on screen, sending emails given a particular criteria – tasks that users shouldn’t have to worry about in doing their job.

Services on CRM Server


Escalation Rules is a windows service that gets installed by default with Sage CRM. This service is responsible for running any enabled escalation rules that have been set up in CRM. But before we delve into how to create them we need to look at the logical components of an Escalation Rule to avoid issues later down the track. These components are as follows:

  • Criteria
  • Control
  • Task

Criteria – defines the conditions of the Escalation Rule being triggered.

Control – this component ensures that the escalation rule doesn’t run forever (or if you want you can also make it run till the end of time!). This is especially crucial when setting up Escalation Rules that notify other users via email. Without the control component users can and will continue to receive emails until someone turns off the Escalation Rules Service.

Task – this component does the bulk of the work in an Escalation Rule whether it be sending emails, on-screen notifications or just changing a value on the screen.

Both the Criteria and the Control aspects of an Escalation Rule is used in tandem to create a SQL ‘WHERE’ clause


The task aspect is executed through a workflow action but without the use of a workflow. It follows the exact the same steps as to how a workflow action is created.



Let’s say as part of the business process a manager needs to be notified if a lead has a high chance of turning to an opportunity, better yet a client. In this situation we could automate this task by creating an Escalation Rule

  1. Ensure that the escalation service is running.
  2. Go to Administration > Advanced Customisations > Escalation and click on New on the top left side of the screen.
  3. Enter the table or entity that we want this rule to be applied for – in this case ‘Lead’
  4. Enter in  Rule Name ‘Lead Notification’
  5. Enter in the Trigger SQL Clause Lead_Rating = ‘High’ and Lead_deleted is not null and Lead_notified is null.


This will ensure that it will only perform our task (sending an email) when

  • The lead rating is set to High
  • The lead hasn’t been deleted
  • And the lead notified hasn’t been flagged

In this SQL clause the lead_rating and the lead_deleted acts as our criteria and the lead_notified (which is a custom field) acts as our control field.

The control works by ensuring not to send emails once it has been filled in with a value.

When you’re done entering the fields hit save.

Now that we have set up the rule itself we now need to add actions to this rule in our case we want an email and to modify our control field so as to not send infinite emails to the manager, which could have dear consequences.


Choose the new rule that you have created ‘Lead Notification’

  1. Scroll down at the bottom of the screen and hit ‘New’
  2. You will be presented to a screen with the possible workflow actions that we can do; for now choose ‘Send E-mail’
  3. Fill in the Details of the email by adding in the manager that needs to be notified and adding a message to the email. Make sure to fill in the ‘from’ field otherwise Exchange will not send the email out.
  4. Hit Save when you are finished.
  5. Now we have to create the second part of our control component for this escalation rule; again scroll down at the bottom of the screen and choose ‘New’
  6. Select the set Column Value action and choose the column ‘Lead_notified’
  7. Enter in the value ‘Yes’ in the value field
  8. Choose the Attribute as ‘Read-Only’
  9. Hit Save when you are satisfied.

Now we have our escalation rule! Make sure that the escalation rule is enabled otherwise it won’t run at all. Additionally I would advise to test this out so that your poor manager doesn’t get a billion emails about Leads with a High rating, we wouldn’t want him to give him false hope!


IT Performance Monitoring

In the current competitive environment organisations rely heavily on information technology to automate business processes, streamline operations, manage resources, and even store yottabytes of data. If you’re an IT or an infrastructure manager or a small or medium size business owner looking to grow then researching about keeping your systems healthy in the long run can be a good starting point and something worthwhile consideration.

Well with performance comes cost, and if you’re concerned about burning a hole in your client’s or your own pocket, then let me break the good news to you. Performance monitoring of your infrastructure doesn’t have to break bank account, and it can be achieved by some really cool tools out there available for free.

In today’s blog, I will give you a brief outline on a similar tool that can mind your servers while you’re peacefully sleeping (isn’t that so relieving?) along with some tidbits on what will be covered in future blog posts to keep you tuned. So, let’s get going!

What is Nagios XI?

Nagios XI is a comprehensive IT infrastructure monitoring tool that constantly checks on all mission-critical infrastructure components – including applications, services, operating systems, network protocols, system metrics and network infrastructure; thus providing you with a complete view of your entire IT operation network and business processes.

And guess what?! Nagios XI isn’t rocket science. Its integrated web-based configuration interface allows users to easily manage monitoring configurations through configuration wizards.

The other cool thing I like about Nagios XI is its notification feature, in which a user can set up alerts in form of emails being sent out to IT staff providing them with outage details so that they can start resolving issues immediately. Moreover, it also provides performance graphs which can be referred to in case of advance infrastructure planning.

What next?

In this series of blog posts, I’ll be taking you through a small step-by-step tutorial on how you can set up a Nagios XI for your business, followed by some advance configuration settings. In the end, we will sum up with an automation task that uses ‘Bash-Shell’ scripting to rotate server logs, download them and format them into a performance report.

Well the fun has just began! I would like to hear from you about how you are planning to monitor your IT infrastructure performance? Drop in a line or two. Meanwhile, I’ll get back to writing the second blog.

SQL Server: Copy database from recent to older versions

Recently, I needed to copy a database from SQL Server 2012 to SQL Server 2008 R2.  Unfortunately, database backups from one version of SQL Server cannot be read by a lower version.  To work around this, I had to use the “Generate Scripts” function to first create the structure and then populate the database.

Whilst none of this is particularly complex, I thought it might be useful to collate the steps in one document…

Step 1: Generate structure scripts

  1. In the SQL Server instance that contains the database to copy, right-click on the Database in the Object Explorer, select Tasks, then select Generate Scripts.
  2. In the Wizard, progress to Choose Objects and make sure that “Script entire databases and all database objects” is selected.

    Screenshot: Select "entire database" option
    Select the “entire database” option.
  3. Progress to the “Set Scripting Options” stage.  Click on “Advanced” and make sure that the “Type of data to script” option is set to “Schema only”.  Also set the “Script for Server Version” option to the other SQL Server’s version (I still ran into incompatibility problems with the generated script, however).

    Screenshot: Advanced options in Generate Scripts
    Advanced options in Generate Scripts
  4. Finish off the Wizard, copy the generated scripts to a location that the other SQL Server instance can access and run them in that Management Studio.  Here are the main problems and fixes I ran into during this part of the process:
    1. Error message that “CONTAINMENT is marked as incorrect syntax”:  Just comment out the “CONTAINMENT = NONE” line (source: http://stackoverflow.com/a/15410854)
    2. Error message that CREATE Database commands can’t be run in a multi-transaction statement:  Just run the create statement separately.
    3. Error when trying to alter user to add role:  This command is not supported in older versions; change those commands to use “sp_addrolemember” (e.g. EXEC sp_addrolemember ‘db_datareader’, ‘Administrator’) (source: http://technet.microsoft.com/en-us/library/ms187750.aspx).
  5. If that all goes well, you should now have the database skeleton ready.  The next step is to import the data.
  6. Back on the originating SQL Server, go to Generate Scripts again, but this time in the Advanced options, set “Type of data to script” to “Data only”.  Because this will probably require a lot of space, in the “Set Scripting Options” screen, set “Files to generate” to “Single file per object”, to split up all of the scripts by table.  Finish the Wizard.
  7. You should now have a bunch of script files, ready to be run.  Copy the containing directory to a location the older SQL Server can access.  Because I had 219 generated files, to save on running them manually, I used the following batch script (create and run it from the containing folder):
    for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"

    (source: http://stackoverflow.com/a/6504317)

  8. The above script may take a while to run, but after that, you should be done!


Detailed Change Tracking in Sage CRM

A requirement that we’ve come across a few times now is to track when a particular field value changed and what the previous value was (e.g. when did the Assigned User change for Company record X?).  A co-worker of mine came up with a method of achieving this, that involved adding only two custom tables and triggers on triggers; I still remember my feeling of admiration at such an elegant solution!

Detailed setup steps are included below, but the gist of it is:
  1. Create a configuration table
    This defines what fields should be tracked.
  2. Create a tracking table
    This will contain each recorded change.  In the screenshot below, you can see that on 30/08/2013 the Admin user (trac_CreatedBy = 1) changed the Salutation on Person 1791, from “Mr.” to “Dr.”.
  3. Create a master trigger on the configuration table
    This trigger runs whenever the configuration table is updated, has an insert or a delete action performed on it.  This master trigger will loop through the configuration rows and create update/insert triggers on the required entities (e.g. the Company table), that will add insert/update changes to the tracking table.

Detailed Setup

I’ve bundled all the SQL commands to set this up, in this script (it’s a 7z file, so you can use something like 7-Zip to extract it):  crm-detailed-tracking-creation-sql
To start tracking fields, just edit the configuration table in SQL Server and specify:
  • Tbl_Name:  The entity table name to track
  • Action: If insert, update or both (insert,update) actions should be tracked
  • Updated_Collist:  The comma-separated list of fields that should be tracked
  • Is_Enable: Set to ‘1’ to turn it on
Once you commit your changes to the table, the master trigger will pick them up and create or re-create the defined triggers.

Next Steps

  • The configuration table could be exposed in CRM via a regular custom page, allowing CRM admins to maintain the tracking definitions
  • Create CRM reports allowing managers to see what changes have been made, to records managed by their team



02/01/2014: This setup assumes that your CRM database has defined primary key constraints on the table that you want to track.  This assumption is correct for new installs of Sage CRM 7.2, but if you have upgraded from an older instance, the database will not have those keys defined, and you will need to set them up manually.



7 Tips for Running a Requirements Gathering Workshop

Gathering user requirements is generally acknowledged to be one of the most important stages of a CRM project.  However, unearthing what users need from such a system can be difficult, if there is little guidance in the process (either from experienced, internal stakeholders or external consultants).

The following tips come from my personal experiences running requirements gathering workshops:

1.  People will not contribute right away

Attendees may be happy to be present, uninterested or even hostile to the project itself.  Regardless of the mood in the room, people will be reluctant to participate and you will need to invest some time in outlining the situation, what the project is looking to achieve and how they can contribute.

2.  Surveys can be a useful starting point

Distributing a survey a couple of weeks out from the workshop can often provide examples and use cases that will help you personalize your discussion points.  The survey should be brief (<5 questions) and provide an anonymous opportunity for users to highlight the issues important to them.

3.  Demoing functionality is useful

Having a working prototype can be of great assistance in helping people think  about the processes required.  You shouldn’t “build” the solution before you’ve talked to users, but if you can customise the demo with one or two use cases that are relevant to them, it will provide a gentle jumping off point for everyone to think about what you missed, what could be improved and what worked well.

4.  Don’t stress about preparing slides

Whilst a working demo is useful, slides generally are not.  I always make sure I have some, but without fail, the discussion quickly moves to the whiteboard and stays there.

5.  Keep a parking lot for issues

Once you’ve moved onto whiteboarding discussions, immediately cordon off an area as a “parking lot”.  Ideas and feedback will start flowing quickly and important points will need to be put aside frequently, although hopefully not forgotten.

6.  Try to get senior management in the room

On the one hand, attendees who are sceptical of the project will often provide important insights into potential pitfalls for the system (they may have good reason for their scepticism).  On the other, having senior management represented will reinforce their commitment to the success of the project and prevent a negative mood from taking hold.

7.  Ask what reports are needed

Finally, a very useful question during the workshop and follow-up meetings, is to ask what reports each user needs.  Without a framework to build on, users will find it difficult to express what their common tasks are, what hampers those tasks, what information they need and so on.  Placing it in the context of reports allows people to clearly define discrete pieces of information (that can be translated into fields) and to articulate the data sources.

How do I output a link to a CRM record, in an email?

Jeff Richards describes how to do it in this post:

Basically, your Email template needs to have the following text:

#recordanchor# <Anchor text goes here> #recordanchorend#

If you’re manually constructing the link (e.g. via a script), you can use the same “SessionFind” URL structure e.g.:


The above URL will redirect the user to the Company Summary page (Act=200) for the company (key0=1) with a comp_companyid of ‘300123’ (key1=300123).

NOTE: The user will always have to login when clicking these links (even if they already have an open session).

How do I set my account to login to a custom screen?

Sage CRM allows users to customise which screen should be displayed by default, when they login.

image2013-8-7 16-35-27

But what if you want to login to a custom screen (e.g. an ASP file)?  In that case, you need to:

  1. Open the following file for editing
  2. Put in a Javascript redirect for the page you would like to redirect to e.g.:
    <script type="text/javascript">
        var strURL = window.location.href;
        strURL = strURL.replace(/\/InteractiveDashboard\/InteractiveDashboard\.asp/, '/CustomPages/CA/Dashboard/index.asp');
        strURL = strURL.replace(/\?.*(SID=\d+).*/, '?' + "$1");
        window.location = strURL;
  3. Because the Dashboard is hardcoded to display a set of tabs at the top of the iframe, you will also need to remove those – you could do this by hiding the tabs. For example, in ‘Dashboard/index.asp’, include the following:
    // Hide the InteractiveDashboards tabs, that are included in the parent frameset.
    $("#landingpage_top_frame", parent.document).hide();
  4. Now, any user with “Dashboard” set as their “Log Me In To” option should be redirected to your custom page.

How do I summarize, in days, when each Opportunity was last modified?

More specifically, this information is usually intended to be outputted as an additional column, in the Opportunity pipeline screen.

How the "Days in Stage" column appears in the Opportunity pipeline.
How the “Days in Stage” column appears in the Opportunity pipeline.

To do this, you’ll need to:

  1. Modify the database view used by the Opportunity pipeline.
  2. Create a translation.
  3. Add the new column to the pipeline screen.


Step 1: Modify vListOpportunities

The Pipeline screen uses the vListOpportunities view, to provide the columns for it to display.  In Sage CRM, go to the Administration section, select the Opportunties entity and under the Views tab, update the view definition to include our new field:

CAST((DATEDIFF(hh, wi.WkIn_UpdatedDate, GETDATE()) / CAST(24 AS DECIMAL(18,1))) AS DECIMAL(18,1)) AS 'oppo_daysinstage',

and an inner join to the Workflow Instance table (we’re using the Updated Date on the workflow instance as an indicator of when the Opportunity was last modified; you could just use the Opportunity’s Updated Date, but then that wouldn’t include workflow progression).

INNER JOIN WorkflowInstance AS wi 
		ON wi.Wkin_CurrentEntityId = 10 
			AND wi.WkIn_CurrentRecordId = oppo_opportunityid

Your view definition should end up looking like the following:

View definition for vListOpportunities.
View definition for vListOpportunities.

Step 2: Add a translation for the new field

To display a user-friendly name for the oppo_daysinstage field, go to Administration -> Customisation -> Translations and create a new translation using the following details:

Parameters for a new oppo_daysinstage translation.
Parameters for a new oppo_daysinstage translation.


Step 3: Add “Days in Stage” to the Pipeline screen

For the last step, go to Administration -> Customisation -> Opportunities -> Lists and select the Opportunities List item.  Select the “Days in Stage” field in the Field drop-down, click Add and then save your changes.

Adding the "Days in Stage" field as a new column.
Adding the “Days in Stage” field as a new column.

When you go back to the Dashboard, your Opportunities Pipeline should now display this brand new field!

My CRM buttons are no longer appearing in Outlook?

If the Sage CRM plugin for Outlook is working correctly, you should see  a “File e-mail” button in your Outlook toolbar.

Sage CRM plugin buttons in Outlook
Sage CRM plugin buttons in Outlook

If not, try the following steps to get it working:


Check that your PC has the required libraries installed.  In the Add/Remove Programs window, you should see:

  • Visual Studio Tools for Office 2010 (VSTO)
  • Microsoft Office 2007 Primary Interop Assemblies
  • Microsoft .Net Framework 4 Client Profile

We’ve also encountered issues if the user account is setup as a roaming profile.  If so, try changing the account to a local profile.

Check Trust Centre

In the Outlook client, open the Trust Centre and check if the Sage CRM plugin is listed in the Add-ins section.  If it is, make sure that it is enabled and trusted by Outlook.

Re-install the plugin

You can find the installer for the plugin in the <Sage CRM Install Directory>\CRM\WWWRoot\Plugin directory (e.g. C:\Program Files (x86)\Sage\CRM\CRM\WWWRoot\Plugin).  Run the plugin appropriate for your setup (e.g. for Exchange integration, use CRMClientExSetup.exe; for just Outlook-client integration, use CRMClientSetup.exe).

Keep in mind that the Outlook user must have local administrator permissions on their PC, the first time that they use the plugin (e.g. in Windows 7, go to the Control Panel, select Users, click on Give other users access to this computer and then add the Outlook user account as an Administrator).

If this still does not work, try logging into the PC as Administrator and re-installing the plugin.

Cleaning up the registry

If the plugin is still not working, you might need to search the Windows registry to see if there are any old or corrupt entries that are confusing the plugin.  As always, make sure to backup your registry before making any changes.

Ideally, the plugin’s registry entries should be under HKEY_CURRENT_USER\SOFTWARE\Sage\Exchange Outlook Plugin.

Outlook itself will also keep registry entries for its plugins (e.g. HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\Outlook\Addins\OutlookAddin2003).

Delete any CRM Outlook entries you find in these folders.

After that, just try searching the registry for any matches on ‘CRM’ and manually checking each.

Remove downloaded files

Check C:\Windows\Downloaded Program Files for any CRM Outlook related files (e.g. crmoutlookplugin1.dll).  Delete all of these files.

Restart the CRM server

Sometimes the plugin will be unable to connect to the CRM server (either during the install or after), due to corrupted metadata on the CRM server.  In these cases, refreshing the metadata on the CRM server (or even restarting the CRM server) will fix the issue.