How can I insert over 3400 bytes of text into a field in the Tridion broker?

Question

I have a schema with a lot of metadata fields. We want to be able to search for components based on that schema from the broker via the Tridion API like:

using Tridion.ContentDelivery.DynamicContent.Query;
private static Criteria getSearchCriteria(
        string searchText,
        params BrokerConstants.MetadataField[] fields)
    var searchCriteria = new List<Criteria>();
    foreach (var f in fields)
    {
        var mkcText = new CustomMetaKeyCriteria(f.Name);
        var mvcText = new CustomMetaValueCriteria(
                mkcText, 
                "%" + searchText + "%",
                Criteria.Like);
        searchCriteria.Add(mvcText);
    }
    return new OrCriteria(searchCriteria.ToArray());
}

This works fine so far: the user can type in some search text, we pass the search text through to Tridion via the broker API, and Tridion gives us back the components that match that search text.

But! If I add a lot of text to the content field for any component, the Tridion publishing process fails at the "deploying" phase:

Phase: Deployment Processing Phase failed, Could not deploy component [Component id=tcm:9-2617-16 title=xyz schema=tcm:9-2325-8], CustomMeta field, StringValue, is bigger than the supported size of 3400 bytes!

I tried changing the KEY_STRING_VALUE column in the broker databases's CUSTOM_META table from nvarchar(3400) to nvarchar(MAX), but that doesn't seem to have fixed the problem.

I'm not exceeding the limit by very much at all: "wc" tells me there are 4037 bytes in my text. Around 6000 or so sounds like a comfortable-ish upper limit for my needs.

Is there an easy way to increase the amount of bytes of text I'm allowed in this field?

Austine

Just an FYI as one Tridion developer to another. Using the Broker database (or any database for that matter) for text searches, especially the open %text% searches, is really not recommended. This will most surely give you huge performance issues. – Click

@Hendrik: yeah, I'm aware that it's scanning every row and not hitting the index for that part of the query: in benchmarking so far it hasn't been a problem (well below 100 millis) - we're really only talking like 20 rows here. I agree that it's certainly not ideal. – Austine

Answer 1

There is no supported way of doing what you wish to accomplish. Also, keep in mind what is the actual purpose of the custom meta. Seems to me you're misusing it by adding huge content in the column.

Anyways, if you really want to go down that road, then you need to contact Tridion Support or visit sdltridionworld (login required) and download CD_2011.1.1.83467 (or any hotfix containing CD_2011.1.1.81125 except for CD_2011.1.1.83475!!!). What the hotfix changes is it removes a hard check on the size of the metadata done at deployment time. In other words, it allows you to shoot yourself in the leg by letting the database server decide if you are allowed to store that much content in that column. Please do know that changing the database columns is not supported by Tridion.

Hope this helps.

Gull

Thanks for the answer: we're looking at seeing if the hotfix fixes the issue for us and reevaluating how we're doing search for the components in question. We probably can't rewrite how that search is working in the short term (as mentioned, it works and it's more than fast enough), but probably in the next release. – Austine

Answer 2

Changing the definition of the column in the broker database is not supported, and may cause support and upgrade issues for your implementation in the future.

It sounds like you're taking the text content of your components and placing it into a text based metadata field in order to provide some kind of full text search functionality on your front end (Forgive me if I'm wrong). This does not necessarily fit well with the intended purpose of the Content Delivery API.

I refer you to a previous question on the subject of implementing free text search, and my answer

Richman