Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Accessing SQL NTEXT Fields

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Accessing SQL NTEXT Fields
 Login/Join
 
<ccline>
posted
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
 
Report This Post
Kerry
Expert
posted Hide Post
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. Big Grin


Cheers,

Kerry
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
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,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
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!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
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).


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
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


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
FrankDutch
Virtuoso
posted Hide Post
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
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Frank, I can send you the user defined function I created if you like.

Thanks for comments.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Accessing SQL NTEXT Fields

Copyright © 1996-2020 Information Builders