SQL Server BCP - Nulls getting exported as spaces as opposed to blanks

SQL Server just drove me crazy with a BCP issue today and though I usually don't put a lot of tech-stuff on my blog, this one is well worth it I guess.

The issue:

I had a stored procedure that uses bcp to export data from a table and surprisingly all nulls were being exported as spaces. I tried explicitly using ISNULL to populate an empty string, tried replacing nulls with empty string in the table itself; but no luck. Google didn't have anything useful either.

The solution:

Specifying the COLLATE attribute on all the character columns to use COLLATE SQL_Latin1_General_CP1_CI_AS as shown below:

[EMAIL_ADDR] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

After adding this to the source table, all nulls were exported as blanks with a simple bcp out.

Dedicated to that lone programmer somewhere breaking his/her head to figure out the solution to this issue :)

[jk_ajp_common.c (651)]: ajp_connection_tcp_get_message: Error - jk_tcp_socket_recvfull failed

This is in no way a technical blog, but to go with one of my new year wishes - helping as many as I could, a small piece of info for people who are facing difficulties with Mod jk configuration (usually connecting Apache httpd server and Tomcat web container)

[jk_ajp_common.c (651)]: ajp_connection_tcp_get_message: Error - jk_tcp_socket_recvfull failed

This was an error in the mod_jk.log that was driving me nuts for almost three full days and I tried to use every possible life line I had; but I couldn't spot anything remotely useful. This bug database had some suggestion about setting the maxProcessors of Tomcat and maxClients of Apache httpd to the same number; but in my case I was the only person hitting the test server that was running Apache 1.3.27 (yeah it was pretty old ;)) and Tomcat 5.5.x on a Solaris 9 Unix box. So naturally that solution didn't help. I believe this whole maxProcessor, maxClients thing has been taken out in the latest version of Apache or at least I didn't find it when I configured Apache 2.2 on the same server...

The simple reason behind this error is that mod_jk.so - the library file that is referenced by the connector code was not built properly. The list below would be my order of troubleshooting and fixing the problem:

1. If the application is really loaded heavily and the error happens once in a while (not consistently), I'd try to check the maxProcessors, maxClients stuff mentioned above first.

2. Try to build the mod_jk.so once more. It's really this simple. The instruction also comes with the source download. If you end up in System errors during the build or the code doesn't compile without errors, chances are that the gcc/cc compilers that you have in the system are not functioning correctly or (in case of a CC) the compiler might not be ANSI-C compliant. The simplest option is to download the latest version of GCC (in my case it was a Solaris 9 box and I got the installable from here), install it and then build your mod_jk.so once again. If you are new to installing stuff on Unix, this link may guide you (again Solaris specific but other flavors of Unix shouldn't differ too much).

3. If you ever had an option of upgrading Apache and your connectors I'd blindly go for it. The whole process of installing a fresh copy of GCC, Apache, Tomcat and the Connectors should not take you more than 4-5 hrs at the maximum; while troubleshooting some System error like that might kill you for days together (that too when your 'technical zilch' boss is having a keen watch over you :)). And the best part is, if you go with the latest version, chances of ending up in a System error like this is extremely remote and naturally many of the previous known issues would've been fixed in the current release.

Hope this helps and stay happy! :)