Lenght of NText field

[PHRED-SE]

I have been using MSAccess databases when working with CF for the past 8 years.
The following query or something very much like it has been hugly successful
in getting PossibleCauses records where the PossibleCauseDesc (memo field) has
a value stored in it:

<cfquery name="qryPC" datasource="#sDataSource#">
SELECT ProblemID, PossibleCauseDesc, Verified, Evidence
FROM PossibleCause
WHERE Len(PossibleCauseDesc) > 0
</cfquery>

I'm now converting from MSAccess to SQLServer. Since the Memo field becomes
an NText field when the database is UpSized to SQL Server, I can no longer use
the Len funciton to determine if the PossibleCauseDesc field has any data in it
because the NText field holds a pointer to the data rather than the data
itself. I've looked through all my books on the subject and can not determine
how to do this query in SQL Server.

I could work around the problem by eliminating the WHERE clause and determine
what I want to know after the query is run, but I have to believe that the most
efficient way to do this is within the Query rather than after the query with a
<cfloop>.

Thanks in advance for your suggestion(s).

:-}
Len

[mxstu]

Well ...
A) Do your really need to use a SQL Server 'text' field? In SQL Server 2000
varchar fields could store up to 8000 characters and nvarchar 4000 characters
(I believe), which is usually sufficient for storing most types of
"descriptions".
B) Do you really need 'ntext' instead of 'text'. (i.e. are you actually
storing unicode data ?)

The DATALENGTH() function can be used on ntext fields. See the Transact-SQL
help for more information.