Management Reporter - The operation could not be completed due to failure on the server

What would be the New Year without a little drama?

I don't often write about Management Reporter, however, we just went live at a client site this past Tuesday, with everything working just as expected - by us and the client. However, just a few days working with their new environment, the client decided to run some of the financial reports we'd created with Management Reporter. Upon attempting to generate the report, the client received the following error:

Attempting to outrule any permissions issue, I ran the report with the SQL Server system admin (sa) account and got the same error.

Since the error was not descriptive enough, I turned to the Windows Applications Event Viewer and immediately spotted 3 entries. The first entry clearly stated the problem:

"Length of LOB data (678233) to be replicated exceeds configured maximum 65536. The statement has been terminated"

Because the exception was generated by a SQL client component, I could definitely rule out an issue with Management Reporter.

The second entry shows the service model fault exception messaging component that trapped the error and relied it to the Management Reporter component.

Application Event Viewer - 2nd Entry

The third entry showed the Management Reporter component that was directly affected by the cascading errors.

The original error message in the Event Viewer pointed to the length of the LOB data used for replication. This prompted me to think back for a bit and I realized that the hosting provider for my clients infrastructure and data had configured transactional replication in the past days based on the instructions provided in Microsoft Support article 926490.

Hence, it was not a surprise to me the error was referencing an issue with replication. In doing some extra search, I came across a SQL Server setting in the Advanced tab that allows you to set the Max Text Replication Size. By changing this value to -1, SQL Server would acknowledge no limit to the size of any character, XML, or image data type that could be replicated with a single insert or update operation.

The good thing also was, after setting the value, I did not have to restart the SQL Server service which made the client pretty happy as well. We re-ran the MR report and problem was gone!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/