Retrieve ntext field using ODBC Socket

[syseng]

Help....!

I just upgraded cfmx6.1 to cfmx7, and all queries retrieve ntext field from MS
SQL Server fail. It displays nothing or empty string for ntext field. I am
using "ODBC Socket" connection to MS SQL SERVER.

Please tell me what is the cause and how to fix this problem. Thanks a bunch!

Connie

[sdupre]

This is a known bug 57696 (low priority).

Most users find the JDBC driver much better (the workaround) and it will work
in this instance.

Is there any particular reason you can't use JDBC? (MS SQL Server)

Stephen Dupre
Macromedia QA

[syseng]

I think Macromedia should investigate their bug more and fix it. They can't
tell all people to use JDBC when they have this problem because each company
has different infrastructure or environment that sometimes JDBC is not a good
way.

By the way, I found out that if you query the ntext field alone, it will
return all data - more than 8000 characters (as using ODBC Socket). Sometimes
I can add additional fields, but when it hits to certain fields in the query,
ntext field won't return data - seems like running out of memory to hold data.
So the workaround solution for me is running query for ntext field alone, or
convert ntext field to text or varchar in sql statement - this is working for
me now.

Thanks for reply to my message Stephen!

[ii_bar]

Interestingly enough... you can not use the JDBC Driver with SQL Server 2000
Personal edition... you get a "connection refused" socket error. Therefore,
you can not access ntext fields under that environment. Either you have to
convert to SQL Server 2000 Developer Edition or equivelent in order to use the
JDBC Driver, then you may access the ntext field data normally.

[PaulH *TMM*]

ii_bar wrote:

> Interestingly enough... you can not use the JDBC Driver with SQL Server 2000
> Personal edition... you get a "connection refused" socket error. Therefore,
> you can not access ntext fields under that environment. Either you have to
> convert to SQL Server 2000 Developer Edition or equivelent in order to use the
> JDBC Driver, then you may access the ntext field data normally.

i don't believe that's true. if i recall correctly, it installs
w/trusted authentication only. you need to reset it to both (windows &
sql authentication) and use a sql server user in it's dsn to connect to
the database.

[tennistravel]

I have encountered the same ntext difficulty now that I've converted from MX to
MX 7. Specifying ODBC Socket as the driver for SQL Server 2000 allows a
connection (evidently the only choice for those of us on Windows XP sp2);
however, all ntext fields are returned as empty strings. Is there any option
other than to revert to Coldfusion MX?

[cychow69]

By the way, I found out that if you query the ntext field alone, it will return
all data - more than 8000 characters (as using ODBC Socket). Sometimes I can
add additional fields, but when it hits to certain fields in the query, ntext
field won't return data - seems like running out of memory to hold data. So the
workaround solution for me is running query for ntext field alone, or convert
ntext field to text or varchar in sql statement - this is working for me now.

Alternatively, you can also just lext the n field types at the END of your
query in the SELECT clause.

Regards,
Chung

[tennistravel]

Chung,
Putting the ntext field at the end of the query worked perfectly. Thanks, you saved me a lot of grief.

Roger