SharePoint with SQL 2000 vs. SQL 2005: CAML Strictness

I’m working at a client that is developing a highly complex application which is using SharePoint to store some of its content.  The application is written in C# and contains a lot of CAML to go grab content from SharePoint, of course.

We realized the other day that while the developers are working in an environment with SQL 2005, the QA, UAT, and PROD environments are running SQL 2000.  Yes, we should have known this all along, and no, it isn’t very smart.  But let’s put those issues aside. (NEVER allow your environments to get out of synch.  If you do, you aren’t really testing appropriately.)

We had pages failing in the SQL 2000-based environments which worked just fine with SQL 2005.  We narrowed it down to a small section of code which contained CAML to get items from a list:

query.Query = String.Format("<Where><And><Eq><FieldRef Name=’Originator’ /><Value Type=’Text’>EM402357</Value></Eq><Eq><FieldRef Name=’IsDraft’/><Value Type=’Text’>True</Value></Eq></And></Where><OrderBy><FieldRef Name=’DateSaved’ Ascending=’TRUE’ /></OrderBy>");

The test on IsDraft (we also won’t discuss here why the developers aren’t using the built in SharePoint document status concepts) was treating it as a text field, when in fact it was a Yes/No column, or Boolean. Making the simple change in the CAML below solved the problem:

query.Query = String.Format("<Where><And><Eq><FieldRef Name=’Originator’ /><Value Type=’Text’>EM402357</Value></Eq><Eq><FieldRef Name=’IsDraft’/><Value Type=’Boolean’>1</Value></Eq></And></Where><OrderBy><FieldRef Name=’DateSaved’ Ascending=’TRUE’ /></OrderBy>");

The developers were caught by the fact that SQL 2005 is more lenient in how it allows you to specify a value for a Boolean.  In general, write your code, in this case CAML, to the strictest rule and you will be in a much better spot.