Creative Juices Bo. Co.

Satisfy Your Thirst For Something Refreshing!

Problem: ColdFusion and MS-SQL NTEXT Returning Empty String

ColdFusion Or MS-SQL Returning [Empty String] Value For NTEXT

Oh MicroSoft, why must you be the bane of my existence? Seriously, if it's not your buggy web browsers, then it's using another one of your products. And this time it's MS-SQL. Ok, in all seriousness, I'm really not 100% sure if this is MicroSoft's fault or perhaps Adobe's ColdFusion. But either way this one through me for a loop. (And on a side note, why does it seem like this crap always happens on a Monday)

If you have been forced to use MS-SQL on one of your projects. And perhaps you've been banging your head up against your computer monitor for the past three hour, because you've been trying to pull data from an NTEXT column and it keeps coming back as an [empty string], then boy do I have a solution for you.

First, let's take a look at the problem. Basically I had a database table that had a NTEXT column called "test_data". I was using it to store JSON test result data for employees. I needed to create a report that would pull this data and then allow an administrator to see how their employees did on their test. It was a pretty simple query, nothing fancy at all. But the "test_data" column kept coming back empty in my queries.

The Problem
<cfquery name="test1" datasource="MyDSN">
   SELECT id, user_id, test_data, is_complete
   FROM test_scores
   WHERE id = 33

Using ColdFusion's cfdump, here's the results I received.

ColdFusion Or MS-SQL Returning [empty String] Value For NTEXT

As you can see, the "test_data" field says [empty String]. The problem is, that its not empty. I know this because I made a another simple query that checked the data length of the column and returned the top results. This query just had the "test_data" field, nothing else... this is when I started to see what was going on.

The Solution

Apparently, MS-SQL (or ColdFusion, the jury is still out on this one) has a problem with the ORDER that you place your column names in your SQL query. That's right, you heard me correctly. When I did my simple query, I was able to pull data. It only came back empty when I added additional column names. So here's my solution...

<cfquery name="test2" datasource="MyDSN">
   SELECT id, user_id, is_complete, test_data
   FROM test_scores
   WHERE id = 33

... and the results...

ColdFusion Or MS-SQL Returning [Empty String] Value For NTEXT Fixed

Notice how I placed the "test_data" at the end of the SELECT statement? That's all it took. Three hours wasted. Kudos, MicroSoft or Adobe, right when I was starting to believe that you guys couldn't suck anymore, you go and lower the bar just a tad bit more. I tip my hat to you sirs.