Discover Simple SQL Server Solutions to Common Sage CRM Problems

While most problems you come across can easily be corrected directly in Sage CRM, there are a few you may need to fix in SQL Management Studios. These issues range from simply changing the size of a field to save in a view that is keeping a screen from working to adjusting a field that has the incorrect Entry Type in Sage CRM. In this article I will also tell you how to shrink the SQL log file to prevent it from becoming too big.

Before working directly in SQL make sure to back up your Sage CRM database.

Shrinking the SQL database log file should be done on a regular basis using a SQL maintenance place or it can be done manually, if you choose. If you allow your SQL database log file to grow too large you will be unable to continue using Sage CRM. Any action that you try to perform will give you a “log file is too big” error message in your Sage CRM SQL Logs. Luckily, shrinking the log file is easy.

In this example, I assume your database name is CRM and that you are using SQL 2008.
[sourcecode language=”csharp”]
USE CRM

GO

ALTER DATABASE CRM SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(CRM_log, 1)

ALTER DATABASE CRM SET RECOVERY FULL WITH NO_WAIT

If you are using SQL 2005, you will need to do the following instead:

USE CRM

GO

DBCC SHRINKFILE(‘CRM_Log’, 1)

BACKUP LOG CRM WITH TRUNCATE_ONLY

DBCC SHRINKFILE(‘CRM_Log’, 1)
[/sourcecode]

To run either query:

• Open SQL Management Studios
• Select new query from the top left and copy and paste the correct query for your SQL database

Remember, you will also need to change ‘CRM’ and ‘CRM_Log’ to match the name of your database and log file.

There may come a time when you want to change a field’s type in Sage CRM. If you want to change a field type from something that has the same SQL field type you simply need to select the field you want to change and change the Entry Type of field from the selection list in Sage CRM.

For example, if you want to change a field in Sage CRM from a Numeric field to a Currency field. Both are stored as Numeric fields in SQL, so Sage CRM lets you easily change it in the interface.

If you want to change a numeric field to an integer you need to make a few updates in SQL. First you need to change the field type in the table:

• Open SQL Management Studios
• Expand Databases, expand your CRM database, expand Tables, expand the table of the field you want to modify, and then expand Columns.
• Right click on the column/field name you want to adjust and click on Modify
• Change the Data Type to Int and click on Save

If SQL is blocking you from making the change, go to Tools, Options, select designer and then un-check Prevent saving changes that require table re-creation. Click Ok and then click Save again.

Now that your field has been changed, you need to modify its Custom_Edits record so Sage CRM will see it as an integer in the interface. Each Sage CRM Entry Type for a field has an integer value that is stored in the Custom_Edits table. Numeric, for example, is 32, Currency is 51 and integer is 31. We will need to run the following query to adjust the field type.
[sourcecode language=”csharp”]
UPDATE Custom_Edits

SET Colp_EntryType = 31

WHERE Colp_ColName = ‘FieldName’
[/sourcecode]

Now that the field has been adjusted in SQL and the Custom_Edits record has been changed you need to run a metadata refresh in Sage CRM:

• Login to Sage CRM and go to Administration, System, Metadata
• Select Refresh All and click Save.

Once the refresh is complete your field has been changed.

The last common problem you may encounter is an error when viewing certain lists after adding a field to a table. If your Sage CRM error log is reporting that the size of the view is too large, you will need to either take a few fields out of the view or shrink down the size of a few fields in the view.

Taking a few unneeded fields out of the view is the recommended approach and can be handled through the Sage CRM interface. If you are unable to adjust the view because this is a core view, or you are not sure what fields are required to be in the view, you will need to adjust the size of a one more fields directly through SQL Management Studios. By default Sage CRM will create fields with a type that contains an “n” in front of the data type in SQL. This is done because Sage CRM allows for several languages to be used and this requires a larger set of characters to be saved. The downside to using the “n” in front of the types is that it takes up more space in a view.

If you are not going to be using any special characters in certain fields (or in any fields) you can save a lot of space in your view by going into SQL Management Studios and changing fields types from nvarchar(), nchar(), and ntext to varchar(), char(), and text. To do this:

• Open SQL Management Studios
• Expand Databases
• Expand your CRM database
• Expand Tables
• Expand the table of the field or fields you want to modify and then expand Columns
• Right click on the column/field name you want to adjust and click on Modify
• You will notice that your data type will have the extra “n” on the front. Simply remove the “n” off the front of the data type and click on save.

Once you have adjusted a few of the larger fields and you have refreshed the metadata you will no longer have a problem with the view being too large. The fields that take up more space in the views are fields with a higher numbers on the end or fields that have the data type of ntext.  Those should be modified first and will have a bigger impact on the size of your view.

Now that you are able to adjust a field type, shrink the SQL log file and shrink field types to correct views that are too large. You are able to avoid the frustrating issues these things can cause. Because these changes are not easily undone, as changing a field’s data type or size can truncate values saved in the field to fit into your new data type, make sure you take a backup of the database so you can always restore and start over.