Differences between Sage CRM Editions

I found an article regarding the features differences between Sage CRM Editions from Greytrix.com. The differences are interesting, especially Sage CRM 100 that only can make very basic customizations. Following is the content of the article.

Sage CRM is available in three editions i.e. Sage CRM 100, Sage CRM 200 and Sage CRM.com. Understanding difference between these editions has always been mystifying to clients. Therefore, we thought it would be great to differentiate it and present it in a blog, which would help users to understand the difference in better manner.

Following are some basic differences between On Premise versions of Sage CRM 100 & Sage CRM 200 and Cloud version Sage CRM.com

Sage CRM 100:

>> Allows maximum of 30 users; Solo offline clients are available at an additional cost.
>> It is an on-premise version of Sage CRM and includes Enterprise Integration Server (EIS).
>> Has minimal scope for customizability and may cost additional fee.
>> Sage CRM 100 represents a one-time software purchase with a 25% annual software maintenance and technical support fee.
>> Web self-service option not available.
>> Wireless Mobile/PDA Access not available.
>> Admin -> system-> Self Service tab is not available.
>> ASP Pages (EIS) is available at an additional fee
>> List\New Button, Screens\New Button, table Scripts in EIS at an additional fee.

Sage CRM 200:

>> Allows unlimited users; Solo offline users are available at an additional per named user cost.
>> Includes Enterprise Integration Server (EIS), Wireless Mobile Users (Pocket PC) and CTI in the base server price.
>> It is a rich edition of Sage CRM and has vast functionalities compared to Sage CRM 100
>> Allows creation of Custom CRM entities with much broader scope as compared to Sage CRM 100
>> Web self-service option may be available for an additional cost.
>> Admin -> system-> Self Service tab is available.
>> ASP Pages  is available in EIS
>> List\New Button, Screens\New Button, table Scripts in EIS is in-built.

Sage CRM.com:

>> Allows unlimited user as a multi-tenant, hosted solution; it is paid as per user/per month basis
>> Provides multiple security layers to protect your data through 128-bit SSL encryption technology
>> Web self-service option is not available
>> Allows access though wireless Mobile and PDA access
>> The SQL Server database can be moved to an in-house instance of Sage CRM at any time and 50% of the trailing 12 months of hosting payments will be applied to the purchase price

 Apart from some of the above differences, the core functionality, customizability, configurability and flexibility exists in all the editions of Sage CRM.



Office Automation Problem in Task Scheduler

This blog is created based on my colleague experience.

He created an application to automate Excel process to read and write an Excel file. The application was able to run the process successfully if it was run manually. But, the application was failed to run if it was run from Task Scheduler. Even though he forced the application to run from the Task Scheduler, the application was still unable to run.

He found the solution from a thread in Microsoft forum. The solution is:

In Windows 2008 Server x64

Please make this folder.


In Windows 2008 Server x86

Please make this folder.


This operation took away office automation problems in my system.

A Desktop folder seems to be necessary in the systemprofile folder to open file by Excel.

This solution is a bit ridiculous, but may be the purpose of this folder is for a dummy or temporary folder of the Excel automation process. Somehow this solution solve the automation problem.



Create Pivot in SQL Server

Recently I have to create a report that is using pivoting in SQL Server. At first I had difficulties in using pivot in SQL Server, because it was the first time to use it. After a week with the report development I get a grip how to use pivot, at least the basic usage that can cater for my report.

Below is the syntax of how to use pivot:

SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
[last pivoted column] AS <column name>
(<SELECT query that produces the data>)
AS <alias for the source query>
    <aggregation function>(<column being aggregated>)
    [<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

I’m sure by seeing the syntax above only we already confused, so let’s take an example. I have created a query that contains PO number, PO date, Item number, and quantity ordered.


With the list of records are as below:

PO123      10-Jun-2013   DESK01   2
PO123      10-Jun-2013   CHAIR01  3
PO456      25-Jun-2013   DESK02   4
PO456      25-Jun-2013   CHAIR01  5
PO789      09-Jul-2013   DESK01   6

From the records above we want to get what is the qty for each month, regardless the year.

    FOR MONTH(TRANSMTH) IN ("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12")

The result of this pivot query is:

ITEMNO     1     2     3     4     5     6     7     8     9     10     11     12
DESK01                                   2     6
DESK02                                   4
CHAIR01                                  8

From the result above the quantity is summed up according to the month.

As you notice the pivot is to get the sum of item’s quantity which based on month of the PO. We also can use PO Number against the item as well, like the query below:

    FOR PONUMBER IN ("PO123", "PO456", "PO789")

The result of this pivot query is:

ITEMNO     PO123     PO456     PO789
DESK01     2                   6
DESK02               4         
CHAIR01    3         5         

The disadvantage of this query is the list of PO number ("PO123", "PO456", "PO789") must be determined before hand. And, we cannot use a selection query to show the list. So, in order to solve this, I use another query to create the PO number list and put it one-by-one in the column bracket. It’s quite troublesome I would say. But, pivoting is a good function nevertheless.

Update A Table From Another Table

Sometimes we need to update some values in one table by other values that reside in another table.

For example I have 2 tables:

Sales_Import table:

LeadID  AccountNumber
147     5807811235
150     5807811326
185     7006100100007267039

RetrieveAccountNumber table:

LeadID  AccountNumber
147     7006100100007266957
150     7006100100007267039

We are going to update the value in AccountNumber field in Sales_Import table by the value in RetrieveAccountNumber table.

Method 1:

    Sales_Import.AccountNumber = RAN.AccountNumber
    Sales_Import SI
    RetrieveAccountNumber RAN
    SI.LeadID = RAN.LeadID

Method 2:

MERGE INTO Sales_Import
   USING RetrieveAccountNumber
      ON Sales_Import.LeadID = RetrieveAccountNumber.LeadID
      SET AccountNumber = RetrieveAccountNumber.AccountNumber;

Method 1 is the most common method used, but the method 2 is the most preferable method (read this article). Anyway both methods are have the same result:

LeadID  AccountNumber
147     7006100100007266957
150     7006100100007267039
185     7006100100007267039

Don’t use the below method to do this:

UPDATE Sales_Import SET AccountNumber = 
    SELECT AccountNumber FROM RetrieveAccountNumber
    WHERE RetrieveAccountNumber.LeadID = Sales_Import.LeadID

The result of this method will be:

LeadID  AccountNumber
147     7006100100007266957
150     7006100100007267039
185     NULL

The reason is the LeadID “185” in Sales_Import table cannot be found in RetrieveAccountNumber table.



Enable Debugging In DLL Development

When developing a standard application it’s easy to just debug it step-by-step. But, when developing a library (DLL file), we need to do additional steps.

In this case I’m using Visual Studio 2005 and developing a library that will be used by IIS 7 in Windows Server 2008 environment.

Step 1:

Run remote debugger wizard from “Start -> All Programs -> Microsoft Visual Studio 2005 -> Visual Studio Tools -> Visual Studio 2005 Remote Debugger Configuration Wizard”

The purpose of this step is to open ports in Windows Firewall for Visual Studio to do debugging.





Step 2:

Run the remote debugger based on the processor the library is built. In this example the library is built using x86 processor. Run the program from “Start -> All Programs -> Microsoft Visual Studio 2005 -> Visual Studio Tools -> Visual Studio 2005 Remote Debugger”.

Up to this step the remote debugger is already up.



Step 3:

Make sure the output of the library is the location where the library will be called by the IIS.


Step 4:

Attach the process to IIS process in “Tools -> Attach to Process …”, select to show “Managed code” only, and then choose IIS process (w3wp.exe) from the list.



After the process is attached the solution will run in debug-mode by itself. Create a break-point in the library code, and then run the program that trigger this library, you will see process will stop at the designated break-point.



Insert Multiple Records into Single Table

There are several ways to insert multiple records into a single table:

Method 1: Traditional method (very simple method)

The purpose of this method is to execute multiple statements at one-time and each statement insert one record.

INSERT INTO Sales_Import (LeadID, AccountNumber)
VALUES (147, '5807811235');
INSERT INTO Sales_Import (LeadID, AccountNumber)
VALUES (150, '5807811326');
INSERT INTO Sales_Import (LeadID, AccountNumber)
VALUES (185, '7006100100007267039');

Method 2: Select – Union method

The purpose of this method is to create a query that contents multiple records by using “UNION ALL” and then insert them into a table.

INSERT INTO Sales_Import (LeadID, AccountNumber)
SELECT 147, '5807811235'
SELECT 150, '5807811326'
SELECT 185, '7006100100007267039'

Method 3: Multiple Set of Values method

This method is similar with the first method, but it has multiple set of values that are going to be inserted into table.

INSERT INTO Sales_Import (LeadID, AccountNumber)
VALUES (147, '5807811235'), (150, '5807811326'), (185, '7006100100007267039')



Page Size in MS SQL Server

Some times ago I tried to understand what is the meaning of “page size” in (Microsoft) SQL Server. This is what I found:

The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.

In SQL Server, the page size is 8 KB (8192 bytes). This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

The following table shows the page types used in the data files of a SQL Server database.

Page type Contents
Data Data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON.
Index Index entries.
Text/Image Large object data types:

  • text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data

Variable length columns when the data row exceeds 8 KB:

  • varchar, nvarchar, varbinary, and sql_variant
Global Allocation Map, Shared Global Allocation Map Information about whether extents are allocated.
Page Free Space Information about page allocation and free space available on pages.
Index Allocation Map Information about extents used by a table or index per allocation unit.
Bulk Changed Map Information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.
Differential Changed Map Information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.
Log files do not contain pages; they contain a series of log records.

Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.


Large Row Support

Rows cannot span pages, however portions of the row may be moved off the row’s page so that the row can actually be very large. The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8 KB). However, this does not include the data stored in the Text/Image page type. This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width. This is done whenever an insert or update operation increases the total size of the row beyond the 8060 byte limit. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page.

In another article I also found that “Surpassing the 8,060-byte row-size limit might affect performance because SQL Server still maintains a limit of 8 KB per page”. So, we must bear in mind when designing a table, make sure not to exceed this page size limit.