Synchronizing an NTEXT field of more than 4,000 characters offline

Today we welcome guest blogger and CRM MVP Jim Steger who is a developer, blogger, and writer for Sonoma Partners.

When Microsoft Dynamics CRM 4.0 provided an offline API, we now implement the Microsoft Dynamics CRM client for Outlook with Offline Access much more frequently. Recently, we came across an issue where our users were not able to go offline. The error was the following:

When looking at the trace, the following additional details are provided:

An error occurred during Offline Synchronization.  Try going offline again, or restart Microsoft Outlook. Insert failed for entity ‘sonoma_qbeconfiguration’, batchRows=0 with exception System.Data.SqlClient.SqlException: The bulk load failed. The column is too long in the data file for row 1, column 2. Verify that the field terminator and row terminator are specified correctly.
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Apparently, SQL Server Express couldn’t handle an attribute with a field of more than 4,000 characters. Fortunately, this was a known issue and described in the following KB938065 knowledge base article.

As the KB article describes, we simply needed to apply an SQL Server Express hotfix to each client and then all would be fine. Well, that depends on whether you install the right hotfix! For some reason SQL Server Express SP2 didn’t rollup this particular hotfix. As such, a different hotfix is needed depending on which you need to determine which version of SQL Server Express (and service pack) you have installed, in order to properly install the correct hotfix. Please note, that currently a new install of the Microsoft Dynamics CRM client for Outlook with Offline Access will install SQL Server Express with Service Pack 2.

However, good news! The answer is now simpler than the existing KB article describes. Just use Windows Server Update Service (WSUS) to download the latest service pack for SQL Server Express (currently SP3). Thankfully, SP3 does contain the hotfix for this problem and you should be set!

If for some reason, you can’t update directly to SP3, then you can use the following command from a command prompt on the client machine to determine your current SQL Server Express version and just install the appropriate hotfix manually:

osql –E –S.\CRM –q"select @@version"

This command assumes a single instance of SQL Server Express called CRM. The current version numbers for SQL Server Express are:

Now you can take those gigantic CRM NTEXT fields safely offline!

Cheers,

Jim Steger

365blog

See more articles from this author