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.

SharePoint List Column Naming Best Practice

Try to use English column names (like "Finding Filed By" rather than "FindingBy").  It’ll make your UI nicer to look at.  Also beware of the fact (you’ve no doubt noticed this) that there are actually two names for each column, the display name and the internal name.

For instance, if you create a column called "Finding Filed By", the internal name is "Finding_x0020_Filed_x0020_By".  There is a 32 character cutoff, including the "_x0020_"s. If you rename the column to "FindingBy", the internal name is still "Finding_x0020_Filed_x0020_By".

What I usually do is fiddle while I’m getting things right and then delete the columns that have internal names that are far off from their display names and then add them back so that things are consistent.

Technorati Tags: ,

Interesting Article on SharePoint at Computerworld

There’s an interesting article on SharePoint over at the Computerworld Web site:SharePoint challenges IT as the Excel, Access of the day.

The most interesting part to me is the is the unflattering comparison between the evolution of SharePoint and Lotus Notes usage in the enterprise: "Departmental users used those tools to build applications that collect and manage data because they couldn’t quickly get the projects onto IT’s development schedule."  Yes, that is the basic point, isn’t it?

As technologies have evolved into true "end-user computing tools", IT just hasn’t kept up.  Complex development lifecycles aren’t well-suited to small application programming projects.   Those nasty users are just out there trying to get their work done, and if the IT Department won’t help, they will find a way to work around it.  It doesn’t matter what tool you talk about, it can happen this way.

Do those end users always architect good solutions?  No.  Should they be using the latest ‘"hammer" to solve all of their technology needs?  No.  What they need form IT is often less about development help and more about just good sound advice.  IT departments that aren’t enthusiastically getting into that advice business are going to be failing on many levels.

I truly do worry that SharePoint may become the next Lotus Notes.  Both SharePoint and Lotus Notes (which I used in the past and actually liked) put tremendous power in the hands of "end users".  (Maybe we should stop using the term "end users".  These days, everyone uses computers, and almost everyone dabbles in some sort of "development".)  Part of my mission as a SharePoint evangelist (read: enthusiast, junkie…) is to make sure that doesn’t happen.

Use SharePoint Designer to Email Daily Task Reminders

Paul Galvin has written a very nice article over at the new SharePoint Magazine about how to set up daily email task reminders just using SharePoint Designer.  The article is well written and shows how you can get something done that you might have been sure you needed to go to Visual Studio to accomplish.

The comments (so far) are interesting, too.  Several people (furuknap and Nikhil) express concern about trying to do this sort of thing in Designer.  I understand their point, but if taking this approach brings relatively simple workflow more in the reach of Designer users, then I’m all for it.  Paul points out several issues with using his methods, and as long as you are aware of them, you ought not be afraid of the approach.

Paul also hints at several ideas he’s holding for other articles:

  • Workflows "cannot be easily ported anywhere, period. There are clever workarounds to this problem, but that’s for a future article. (Hint -edit the XOML file directly, replacing task, list and other GUIDs manually.)  This analogous to the way that I copy or move DVWPs around from place to place, should I need to.  In fact, at this point, I almost always switch from ListIDs to ListNames in my DVWPs.  The XOML files are just XML files like everything else: to edit them in Designer, right click on them and open as text.
  • With this approach, "it would be hard to decipher the history and require a lot of clicking.  There is a clever solution to this problem as well, again for a future article. (Hint – create entries in a custom list using a common key field to group them together.)  Having workflow events add new entries to other lists can work well, but you do need to watch out if this is going to cause your lists to have thousands of items. The rule of thumb out there is that list objects (roots of lists and subfolders) shouldn’t contain more than 2000 items each.  I haven’t seen going beyond this limit (even greatly) be a problem, but it’s something to watch out for.

SharePoint Stump the Panel

There’s a new forum over at EndUserSharePoint.com called Stump the Panel.  The goal is to answer questions from real end users — no fancy coding allowed, just "out of the box" functionality available through the UI (they acknowledge that a little XSLT is OK).

If you like brain teasers of the SharePoint variety, check it out. You might enjoy your visit and help someone as well.