I have been working on a part of an application that stores translation data. How it works is very simple. A user would go in and translate text from one language to another and save the translation. Sounds very simple right? Yea, that is what I thought too. That was actually until I tried to save the data.

What was happening was this. The data being saved in the database was showing up as "?????? ???????" when querying the data through SQL Management Studio. I then double checked to see if I was going crazy. I ran a select query via ColdFusion and the data came back in the same way. I found this quite strange considering the cfstoredproc debug output of the insert looked good.

After doing some digging I made some alterations. The alterations were based on the fact that I was using the wrong data type. First I changed the parameter data type in the stored procedure from varchar to nvarchar. I also changed the table data type to nvarchar. This should now allow me to insert and store the data correctly.

I then reran the inserts and I was still at the same place. The data was still getting inserted wrong but the debugging of the insert looked accurate.

I then did some more digging and found something else I needed to change. This next change was to alter the datasource in ColdFusion to enable Unicode data. Once this was done I reran my tests.

BINGO! JACKPOT! YOU WIN! The data was now saved and retrieved as expected. Finally I was done with this part of the app. It was then, in the beginning moments of when I was reveling in my victory, that my world came crashing down around me.

One of the developers on my team came flying into my office and asked who broke the system. I said I made a change to the data source but it should not have broke everything. I then remembered that what should happen and what actually happens are usually 2 different things.

He then showed me the lovely error that was appearing all over the system:

view plain print about
1Msg 9402, Level 16, State 1, Procedure [stored_procedure_name], Line 0
2XML parsing: line 1, character 38, unable to switch the encoding

Our system uses XML passed into the database via stored procedures. The stored proc then can process multiple transactions in a single call. We process data in this was to reduce hits to the database and increase performance.

At this point I knew it was the data source setting change that caused it. I put the setting back and his error went away. We ran further tests turning the setting on and off while using profiler to look at the stored proc calls. We were able to narrow down the issue to our stored procs that process xml. It appeared that ColdFusion was passing the data as unicode when the xml was using utf-8 encoding. The database was choaking trying to switch the encoding back.

I now knew I had more work. The setting had to stay off so I had to find another way around my issue. So now my challenge was to figure out how to pass Unicode data to the database without turning on Unicode support.

After what seemed like weeks and attempt number 10 billion I was going to throw in the towel, I couldn't figure it out. I was done googling every combination of unicode and sql and coldfusion I could think of. But, I am not one to just give up.

I then had an idea, what if I alter the code to not use cfstoredproc and use cfquery instead. So, I reworked the code, created the query and BAM! It did not work. So, I kept trying different things. Finally, I hit the magic combination and it worked! The data actually saved and was retrievable. All this without enabling Unicode on the data source.

The end solution was quite simple but it was a long road to get there. All the changes to the stored proc and table for the nvarchar were accurate. The change was in how the query was formatted and how the stored proc was called.

First, I didn't name the parameters in the proc call. I just put them in the correct order. Later testing proved that this was not necessary and I could name the params. Second, was to add the Unicode identifier in front of the data being insert into an nvarchar data type. Here is what the end query looked like:

view plain print about
2    exec translations_upd
3        @trans_id= 1,
4        @localized_phrase = N'#transStr#',
5        @translated = 1

The main thing is in the @localized_phrase param. The "N" before the data was the trick. It must be capital or sql will throw an error.

My biggest issue with this whole thing was CFSTOREDPROC's inability to handle this without enabling Unicode on the datasource. Hopefully this will be corrected in future versions.

Till next time.