Does anyone have experience working with reports using NTEXT fields in SQL Server 2000?

I have a table where HTML code is stored in 2 NTEXT fields that I need to write a simple report against. The longest string I've seen in one of the NTEXT fields is ~36,000 characters.

When I created the synonym, it translated these fields to TX50/TX (Usage/Actual), but that seemed to split up the long string into multiple rows when the repor was run.

What worked was to change the Master file to use A10000 fields instead of TX. But there were 2 problems. (1) That would still truncate the fields >10,000 characters in length. (2) It was still rather slow. Whereas a similar query in Query Analyzer would take 2-3 seconds, when run via WebFocus, it was 2-3 minutes.

I'm not sure what the maximum length an Alphanumeric field can be, but I tried A30000. That caused an error.

I'm curious what the fastest and most robust way to access these fields would be.

Thanks.

Chris

Hi Chris,

I ran a quick search on Information Builder's online documents and came across this topic which may be of your interest.

What is the maximun lenght for Alpha field in WebFOCUS?
http://techsupport.informationbuilders.com/sps/72051001.html

When you said you tried A30000 and it gave an error, can you please forward the content of the error message? Also, which release of iWay server and WebFOCUS are you running on?

Hope this helps.


Cheers,

Kerry

I am having major problems with a column in several Microsoft CRM tables. It is defined as ntext 7000.

When I generate the metadata, it is defined as usage TX50, actual tx.

I read several different tables, create HOLD files for each, and then merge them together with the MORE statement.

I can't have a tx field in a MORE statement, so I redefine the column in the HOLD files with column-name/A200. This creates a HOLD file with no rows.

If I change the Masters to usage A200, actual A200, I get a lot of IO errors.

I don't have a clue how to resolve this.

I tried looking for the document that Kerry kindly posted in 2005, but it appears to be no longer available.

What to do?

Thanks,

I added

SQL SQLMSS SET CONVERSION LONGCHAR ALPHA
SQL SQLMSS SET VARCHAR OFF

to the site profile and edasprof.prf and these command make no difference.

Also, I think the problem is due to carriage returns in the data!

TECHNIQUE: Unable to retrieve information from SQL Server nText field

suggests adding this to EDASPROF, didn't make any difference in v5.3.2

ENGINE SQLMSS SET CURSORS CLIENT

(and the document doesn't tell us what this means).

Over 2000 views since this topic was posted in September 2005 and no concrete solutions.

My solution: create a view to the table where the problem column is transformed using the MS SQL Server CAST function. I also use a user defined function to strip all HTML tags and carriage-returns/line-feeds.

The view has code something like this:

Cast(dbo.udfStripText(CAST(activity_pointer_phonecall.Description AS nvarchar(4000))) as varchar(2000))

udfStripText is a function I created based on two functions I found on the web. One stripped HTML tags, the other stripped CR-LF characters. The combined function seems to work in that it strips all the unwanted characters, I'm not sure it doesn't also strip out some good stuff.

Francis

We are using Microsoft CRM (4.0 since last weekend) and we changed our masters were the original field says TXT to USAGE=A4000, ACTUAL=A4000, $
No read problem so far, but I'm not sure what will happen if a field is really more than these 4000 characters.
We do however have small problems if in these fields sometimes people used CRLF , tabs etc.
If you open such field in a microsoft program like ACCESS it looks good. Opened in PDF by Webfocus it looks dramatical. In HTML it's te same problem.
We can search and replace a bit, but it's not a nice solution.
We use 7.1.3

Frank, I can send you the user defined function I created if you like.

Thanks for comments.