A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Thursday, April 21, 2011

ShelbyQUERY Text Limit

I apologize for originally posting this as an empty message. For some reason the message was lost when I clicked to publish it, and I just didn't have time to re-write the whole thing.

What I intended was to post the maximum number of characters that the ShelbyQUERY text editor can handle. I had to determine this maximum because a customer contacted me about an overflow error when she was using ShelbyQUERY, and it turned out she was exceeding the maximum character limit of the editor. Oddly, the editor can hold more text than the "maximum" technically allowed, and it can execute the query as well. But if you try to move the insertion cursor anywhere past the maximum limit, you will receive an overflow error. So it is just good to avoid hitting that limit if you can.

Through some experimentation, I discovered that the maximum number of characters is 32,766. However, carriage returns count as two characters. And spaces count as one each also.

You should not hit this limit unless you are doing some extremely complex or multi-layered query analysis. But if you start getting overflow errors in ShelbyQUERY, this is the first thing to check.

As a troubleshooting tip, I recommend copying the text of your query over into MS Word and using the Review > Word Count tool. Use the character count (with spaces) value and add double the number of lines. That is equal to the number of the "characters" in your query. This will give you an idea for how much text needs to be cut out of the query.

If you are in a real pinch to squeeze out every extraneous space, you can remove carriage returns and use a space instead. You can even remove the space character if there is a punctuation mark (such as a comma or a single quotation mark) to separate key words in the statement. The result will not be readable, but it may be enough to fit the limit of the ShelbyQUERY text editor.

A better "workaround" might be to just use MS SQL Server Management Studio. That tool has no arbitrary limit to the amount of text, and so you can execute extremely long query statements without any trouble at all.