"Unicode data in a Unicode-only collation..." Error and Writing a Migration to Fix It

The problem

I've been maintaining a Rails project at work during my down-time, and recently ran into the following error after launching my web app to CloudFoundry:

Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

The project started in 2007 and has experienced on-and-off development for a long time, so when I saw this error, I wasn't entirely surprised: Practically every tool we use in that project is deprecated now that it's nearly 2013. So, where did this error come from? Why did it decide to show up 5 years into development? And why does it not show up in the development build? Well, this project has seen a lot of developers, and each one for only a couple weeks at a time. So I imagine this problem has always been there, or popped up during a recent CloudFoundry update, and it's just been ignored until now.

Note: I have recently found that this fix does not work as well as I thought it did. Looking for a better solution for the time being, will update later.

The first question: What caused this error?

Dissecting the error given above, it looks like a database issue with Unicode processing caused by an ntext variable. For various vague reasons, cloud-based applications occasionally don't get along well with nvarchar and ntext variables in SQL. Why? Well, these kinds of variables are used to store Unicode characters to potentially store multi-lingual data which can become quite large very quickly. About twice as large, in fact. Why do sites like CloudFoundry and Heroku get mad at these variables sometimes but seemingly not all the time? I have no idea.

Anyway, I looked through the columns in my database and, sure enough, there was a single nvarchar(MAX) column staring me in the face.

How do we fix this?

Write a simple migration to change the column. Looking into it briefly, the maximum number of bytes a varchar can hold in SQL is 8000. Now I know what you're saying. 8000 bytes? That's not nearly enough for my data! That's likely not true. This wall of text is 8000 bytes. Unless your writing a web application to have people submit high school essays or college theses, 8000 bytes is a lot. So let's write a migration! Fortunately, this is trivial. Assume our column name is "about_me" and our table name is "profile". Following the Rails Migration Guide, we start the terminal in our root directory:

> rails generate migration ChangeAboutMeColumnToVarChar

Now, we write our up and down methods. Remember, our column is about_me and our table is profile. In my case, the about_me column used to be a nvarchar(MAX) variable, which translates to a text variable in Rails. Following the Rails Migration Guide again, we have:

# db/migrate/ChangeAboutMeColumnToVarChar

class ChangeAboutMeColumnToVarChar < ActiveRecord::Migration
  def up
    change_column :profile, :about_me, :string, :limit => 8000
  end
 
  def down
    change_column :profile, :about_me, :text
  end
end

Now we run our migrations:

rake db:migrate RAILS_ENV="development"

And the problem should be fixed. Of course, after running the migrations on the local database, change the RAILS_ENV option to any other database that may need updated when you're ready.