Mystery Server ODBC Error Solved!

Several weeks back, I posted a problem with updating MCMS channel properties using a custom web form. Each time I set the Channel.StartDate property (or any of the channel's properties for that matter), it sent back a "Server ODBC error. Please contact the administrator" message. No further details were given.

What's really strange was that we have been using the same set of codes for months now and it didn't give any problems until recently.

So, after talking to Microsoft Support and running a few tests of our own, here's what we found:

1. The "Sever ODBC Error" is really a general error message that is sent back from the server whenever an error occurs while updating/reading the database. It could mean anything - from passing in a bad GUID as an input parameter to an unsuccessful attempt to get the lock on the table to be updated.

2. We ran SQL Profiler. In our case, the error generated by SQL is always: #1222. Error #1222 means that an attempt to get a lock has timed out. And the error occurs at the point in which the channel's properties are being updated on the database.

3. The error does not always occur. And when it does, it disappears when the web service is restarted.

2+3 led us to believe that there are locking issues with the database implying potential problems with the code. But what could be wrong with it? Afterall, we are using only methods from the MCMS PAPI.

Looking at the code on the web form, we observed a pattern. The form contained two key buttons:
a. Update saves the contents of the form.
b. Publish makes it available online.

When authors work on the web form, they would typically update it before publishing it, clicking Update before Publish.
Update -> Publish

Behind each button is a call to a CmsApplicationContext in the required publishing mode. And herein lies the problem. When the CmsApplicationContext of Update() does not release the lock, the CmsApplicationContext of the next transaction, Publish(), is not able to get hold of it. As a result, the error is raised.

The same error is raised when two authors click on the Publish/Update buttons at the same time.

Okay, so the code was causing this lock contention, but what can be done to prevent it? Looking at the API, there isn't a SignOut/Logout/Close function once a CmsApplicationContext is established. The closest thing we get is the CmsApplicationContext.Dispose() method.

So I added Dispose() for each CmsApplicationContext created at the end of each transaction... and voila! The errors disappeared.

Actually, the whole thing has been documented. Take a look at the documentation for the StartDate property which says:
---
This property can only be set if the CanSetProperties property has a value of true for the current User and the PublishingMode is Update. However, even if both of these conditions are satisfied, an attempt to set this property can still fail such as when, for example, the object is being edited concurrently by another user. Therefore, setting this property should be enclosed in appropriate try...catch blocks.
---

But why oh why doesn't it give a more descriptive error message?

The conclusion is: Calling Dispose() is a *must* especially when working with the longer running CmsApplicationContext object.

Thanks to all who have helped solve the problem. Special thanks to Jason Ren, Angus Logan and Stefan Goßner. We just rolled out the latest release of the site today, and it feels good to have the site running smoothly once again :-)