benc's blog

Here's another post for you future googlers trying to track down the cause of an obscure InfoPath/SharePoint error message. In my own googlings, I found a number of questions about the problem, but no working solutions. I came up with the solution eventually… here it is.

The problem
…feel free to skip on down to the solution, past the irreverent narrative

In today's episode, we look into the life and times of Hal the SharePoint server and Dave the Office user. Among his other assets, Hal hosts a Form Library, which holds XML forms submitted by InfoPath. Hal does all sorts of funky workflow on the forms behind the scenes, but that's another story.

Anyway, Dave has been happily InfoPathing for some time, beautifully oblivious to what's going on underneath the hood. But one fateful day, Dave can suddenly no longer submit his InfoPath forms to Hal. When he clicks the submit button, InfoPath barfs with:

InfoPath cannot submit the form.
An error occurred while the form was being submitted.
The form cannot be submitted to the following location: http://hal/sites/food/requests/Pie for Dave.xml
The site may be offline, read-only, or otherwise unavailable.

Lies! The site is up and running! To no avail, Dave screams, "Why can't I submit? Open the pod bay doors, Hal!"

Alarmed, Dave's minions from the IT department come over and have a look-see. They notice that Dave can submit InfoPath forms just fine on the PC down the hall. It's definitely a problem with Dave's PC. (Hal takes this in stride – of course it's not a SharePoint problem. Further evidence that he's infallible.)

So the IT dudes google the error message, and lo! The hotfix mentioned in KB 907652 looks promising. Half an hour later, the unreleased hotfix has been successfully extracted from the depths of Microsoft PSS. Life is good… at least until Dave points out that the patch didn't solve his problem!

The solution

Turns out that KB 907652 was a false lead in Dave's case (and, I suspect, in a number of other folks' cases as well). The real fix is our good friend KB 287402.

Much of Microsoft Office 2003's SharePoint integration is accomplished using the WebDAV protocol. (Technically, WebDAV is an extension to HTTP. And Microsoft has their own name for it, web folders.) A prime example of this is InfoPath, which submits to Form Libraries using WebDAV.

So when something goes wrong with web folders, something often goes wrong with InfoPath, too. This connection isn't readily apparent, but there you have it.

You be dead, foo'!

I'll leave you with this random tidbit: KB 287402 has a laundry list of things to try. In Dave's case, we hit gold on the third step, which involves deleting HKEY_LOCAL_MACHINESOFTWAREClassesCLSID{BDEADF00-C265-11D0-BCED-00A0C90AB50F}.

Er, did Mr. T have a hand in this? Go on, just tell me you're not amused by first 4 bytes of that GUID. I dare you, foo'!

Here's another post for you future googlers trying to track down the cause of an obscure InfoPath/SharePoint error message. In my own googlings, I found a number of questions about the problem, but no working solutions. I came up with the solution eventually… here it is.

The problem
…feel free to skip on down to the solution, past the irreverent narrative

In today's episode, we look into the life and times of Hal the SharePoint server and Dave the Office user. Among his other assets, Hal hosts a Form Library, which holds XML forms submitted by InfoPath. Hal does all sorts of funky workflow on the forms behind the scenes, but that's another story.

Anyway, Dave has been happily InfoPathing for some time, beautifully oblivious to what's going on underneath the hood. But one fateful day, Dave can suddenly no longer submit his InfoPath forms to Hal. When he clicks the submit button, InfoPath barfs with:

InfoPath cannot submit the form.
An error occurred while the form was being submitted.
The form cannot be submitted to the following location: http://hal/sites/food/requests/Pie for Dave.xml
The site may be offline, read-only, or otherwise unavailable.

Lies! The site is up and running! To no avail, Dave screams, "Why can't I submit? Open the pod bay doors, Hal!"

Alarmed, Dave's minions from the IT department come over and have a look-see. They notice that Dave can submit InfoPath forms just fine on the PC down the hall. It's definitely a problem with Dave's PC. (Hal takes this in stride – of course it's not a SharePoint problem. Further evidence that he's infallible.)

So the IT dudes google the error message, and lo! The hotfix mentioned in KB 907652 looks promising. Half an hour later, the unreleased hotfix has been successfully extracted from the depths of Microsoft PSS. Life is good… at least until Dave points out that the patch didn't solve his problem!

The solution

Turns out that KB 907652 was a false lead in Dave's case (and, I suspect, in a number of other folks' cases as well). The real fix is our good friend KB 287402.

Much of Microsoft Office 2003's SharePoint integration is accomplished using the WebDAV protocol. (Technically, WebDAV is an extension to HTTP. And Microsoft has their own name for it, web folders.) A prime example of this is InfoPath, which submits to Form Libraries using WebDAV.

So when something goes wrong with web folders, something often goes wrong with InfoPath, too. This connection isn't readily apparent, but there you have it.

You be dead, foo'!

I'll leave you with this random tidbit: KB 287402 has a laundry list of things to try. In Dave's case, we hit gold on the third step, which involves deleting HKEY_LOCAL_MACHINESOFTWAREClassesCLSID{BDEADF00-C265-11D0-BCED-00A0C90AB50F}.

Er, did Mr. T have a hand in this? Go on, just tell me you're not amused by first 4 bytes of that GUID. I dare you, foo'!

Here's a couple quick queries to try against a SharePoint content database. Nothing too interesting, but I've found them useful once or twice as a SharePoint administrator, so I thought I'd share them.

The usual caveats about running queries directly apply, of course. Microsoft's Keith Richie puts it best in his latest post about finding orphan sites (a good read, by the way):

DISCLAIMER: This post shows using Query Analyzer to query data in your SharePoint sites. By no means does this mean that you should change any thing in the database. This is simply for "READING" values. And even this should be done during Off-Peak hours.

This first query finds the 100 largest documents across all sites in the current content database. Can be useful for identifying bottlenecks.

  SELECT TOP 100      DirName + '/' + LeafName AS name,      DATALENGTH(Content) AS contentlen  FROM      Docs  --WHERE  --    (LeafName LIKE '%.doc')    -- optionally, find documents of this type  ORDER BY      contentlen DESC  

Second, here's a query to find email addresses by pattern. Keep in mind that data mining for email addresses is kinda evil… just make sure you're doing this for a good cause.

One such cause: A number of your users have alphanumeric pagers with their own email addresses (e.g., 5558675309@pager.phonecompany.com). These email addresses are set up to receive certain alerts from certain lists (e.g., custom lists named "High-Priority Service Requests" and the like.) Your company needs to know what those numbers are. And you'd rather not dig through multiple pages of user info on multiple sites, searching for email addresses not matching the pattern "@example.com". The solution:

  SELECT      UserInfo.tp_Email,      UserInfo.tp_Login,      UserInfo.tp_Title,      UserInfo.tp_Notes,      UserInfo.tp_Deleted,      UserInfo.tp_SiteAdmin,      Sites.FullUrl  FROM      Sites,      UserInfo  WHERE      Sites.Id = UserInfo.tp_SiteId      AND ASCII(UserInfo.tp_Email) > 0      AND UserInfo.tp_Email NOT LIKE '%@example.com'  ORDER BY      UserInfo.tp_Email

Finally: I've got a really neat (and unambiguously useful, for once) query coming up later, so stay tuned.

–>

Here's a couple quick queries to try against a SharePoint content database. Nothing too interesting, but I've found them useful once or twice as a SharePoint administrator, so I thought I'd share them.

The usual caveats about running queries directly apply, of course. Microsoft's Keith Richie puts it best in his latest post about finding orphan sites (a good read, by the way):

DISCLAIMER: This post shows using Query Analyzer to query data in your SharePoint sites. By no means does this mean that you should change any thing in the database. This is simply for "READING" values. And even this should be done during Off-Peak hours.

This first query finds the 100 largest documents across all sites in the current content database. Can be useful for identifying bottlenecks.

  SELECT TOP 100      DirName + '/' + LeafName AS name,      DATALENGTH(Content) AS contentlen  FROM      Docs  --WHERE  --    (LeafName LIKE '%.doc')    -- optionally, find documents of this type  ORDER BY      contentlen DESC  

Second, here's a query to find email addresses by pattern. Keep in mind that data mining for email addresses is kinda evil… just make sure you're doing this for a good cause.

One such cause: A number of your users have alphanumeric pagers with their own email addresses (e.g., 5558675309@pager.phonecompany.com). These email addresses are set up to receive certain alerts from certain lists (e.g., custom lists named "High-Priority Service Requests" and the like.) Your company needs to know what those numbers are. And you'd rather not dig through multiple pages of user info on multiple sites, searching for email addresses not matching the pattern "@example.com". The solution:

  SELECT      UserInfo.tp_Email,      UserInfo.tp_Login,      UserInfo.tp_Title,      UserInfo.tp_Notes,      UserInfo.tp_Deleted,      UserInfo.tp_SiteAdmin,      Sites.FullUrl  FROM      Sites,      UserInfo  WHERE      Sites.Id = UserInfo.tp_SiteId      AND ASCII(UserInfo.tp_Email) > 0      AND UserInfo.tp_Email NOT LIKE '%@example.com'  ORDER BY      UserInfo.tp_Email

Finally: I've got a really neat (and unambiguously useful, for once) query coming up later, so stay tuned.

–>

As promised, here is the first in a series of queries against SharePoint's (either WSS or SPS) content database. This particular query returns all document libraries for all sites in the current content database that have event sinks attached. Quite useful for keeping track of things in environments that use event sinks extensively.

Note that it would be fairly straightforward to implement this in the object model. But it wouldn't be as quick and simple as this query:

  SELECT      Webs.FullUrl,      Lists.tp_Title,      Lists.tp_EventSinkAssembly,      Lists.tp_EventSinkClass,      Lists.tp_EventSinkData  FROM      Lists,      Webs  WHERE      Webs.Id = Lists.tp_WebId      AND Lists.tp_EventSinkAssembly IS NOT NULL  ORDER BY      Webs.FullUrl,      Lists.tp_Title  

As promised, here is the first in a series of queries against SharePoint's (either WSS or SPS) content database. This particular query returns all document libraries for all sites in the current content database that have event sinks attached. Quite useful for keeping track of things in environments that use event sinks extensively.

Note that it would be fairly straightforward to implement this in the object model. But it wouldn't be as quick and simple as this query:

  SELECT      Webs.FullUrl,      Lists.tp_Title,      Lists.tp_EventSinkAssembly,      Lists.tp_EventSinkClass,      Lists.tp_EventSinkData  FROM      Lists,      Webs  WHERE      Webs.Id = Lists.tp_WebId      AND Lists.tp_EventSinkAssembly IS NOT NULL  ORDER BY      Webs.FullUrl,      Lists.tp_Title  

When first exposed to SharePoint, many a newbie thinks, "Ah, so SharePoint has this content database thingie. So to start developing on the SharePoint platform, I need to learn how to run queries against this database, right?"

This idea dies rapidly once the SPNewbie dives into the SDK (and the SharePoint community). The object model has its kinks, but is much cleaner to work with than straight SQL queries. Ditto for SharePoint's built-in web services.

And sooner or later, the fledgling SPGuru will see this dire warning, oft-repeated in the SDK itself:

Modifying the database schema or database structures is not supported. Changes that you make to the database contents may be overwritten when you install updates or service packs for Windows SharePoint Services, or when you upgrade an installation to the next product version.

Because of this, some SharePointers come to treat the content database as a black box, never to be touched (much less understood).

That's a shame. The warning is worded strongly, as it should be. But don't overreact — if you read the warning thoroughly, you'll notice that it doesn't forbid you from just looking at your data on the SQL side.

There are some very useful things you can do using SQL queries against the content database. And some of these things are impossible (or at least difficult) using the object model, web services, and/or good old stsadm.exe. Besides, to be able to utilitize SharePoint (or any platform, for that matter) to its full potential, you have to really understand how it works under the hood.

So dive in to the databases — look at the schemas, build and run some SELECT queries. The database tables are documented in the SDK, after all.

Standard disclaimer: Always run your queries in a test environment first! Anything that updates your data (as opposed to simply querying it) shouldn't be done lightly, if at all. Adding your own triggers, views, and stored procedures is even more risky (and not supported by MS). A good rule of thumb: look, but don't touch (unless you really know what you're doing). But the whole point of this post is: don't be afraid to look!

In some future posts, I'll show you some SELECT statements that I've found useful (mostly from an administrator's point-of-view).

When first exposed to SharePoint, many a newbie thinks, "Ah, so SharePoint has this content database thingie. So to start developing on the SharePoint platform, I need to learn how to run queries against this database, right?"

This idea dies rapidly once the SPNewbie dives into the SDK (and the SharePoint community). The object model has its kinks, but is much cleaner to work with than straight SQL queries. Ditto for SharePoint's built-in web services.

And sooner or later, the fledgling SPGuru will see this dire warning, oft-repeated in the SDK itself:

Modifying the database schema or database structures is not supported. Changes that you make to the database contents may be overwritten when you install updates or service packs for Windows SharePoint Services, or when you upgrade an installation to the next product version.

Because of this, some SharePointers come to treat the content database as a black box, never to be touched (much less understood).

That's a shame. The warning is worded strongly, as it should be. But don't overreact — if you read the warning thoroughly, you'll notice that it doesn't forbid you from just looking at your data on the SQL side.

There are some very useful things you can do using SQL queries against the content database. And some of these things are impossible (or at least difficult) using the object model, web services, and/or good old stsadm.exe. Besides, to be able to utilitize SharePoint (or any platform, for that matter) to its full potential, you have to really understand how it works under the hood.

So dive in to the databases — look at the schemas, build and run some SELECT queries. The database tables are documented in the SDK, after all.

Standard disclaimer: Always run your queries in a test environment first! Anything that updates your data (as opposed to simply querying it) shouldn't be done lightly, if at all. Adding your own triggers, views, and stored procedures is even more risky (and not supported by MS). A good rule of thumb: look, but don't touch (unless you really know what you're doing). But the whole point of this post is: don't be afraid to look!

In some future posts, I'll show you some SELECT statements that I've found useful (mostly from an administrator's point-of-view).

This is one of those "after banging my head on the desk more times than I care to admit, I finally got x and y to play nice together; here's how you do it" posts.

I was trying to set up a test environment with a single-server deployment of SharePoint Portal Server (SPS) using an MSDE database. I also wanted to enable SSL on the server.

So — and this was my mistake — I started off by creating my own certificate with SelfSSL and installing it for the default virtual server in IIS. In theory, this virtual server would then be extended with WSS when I installed SPS, with the SSL certificate remaining in place. Didn't quite work that way, though.

In case you haven't heard about it, SelfSSL is one of the excellent free tools provided by Microsoft in the IIS 6.0 Resource Kit. It lets you quickly create self-published test certificates, allowing you to use SSL in your test environments. Great tool, but since it's a free (unsupported?) tool, it has some rough edges. It doesn't always play nice with other applications — in this case, MSDE.

MSDE is another useful, free product. It's the poor man's version of SQL Server 2000. Unless you're using it's the full SQL Server, SPS relies on MSDE to hold its data. When you install SPS with MSDE, it creates an MSDE instance named SharePointPortal.

However, the MSDE installation will fail if you have any SelfSSL certificates installed! Both WSS and SPS will install just fine, but MSDE will fail with several of these in the setup logs:

SSL Security error :ConnectionOpen (SECDoClientHandshake())

KB 309398 explains this in more detail, and tells you how to temporarily uninstall your certificates while you install MSDE. Alternately, I could have avoided all of this mess by waiting to install the SelfSSL certificate until after the SPS installation.

And one more thing…

The issue I described above will not affect you if you're just installing WSS (not SPS) with WMSDE on a server with a SelfSSL certificate.

WSS without SQL Server 2000 — essentially the poor man's version of SPS — installs WMSDE, which is a specialized version of MSDE that is, in some ways, actually more powerful than MSDE. For one thing, it isn't affected by the SSL issue.

Apparently, upgrading from WSS to SPS actually downgrades (crossgrades?) your WMSDE database to MSDE. Check out Graham Tyler's SharePoint Storage Overview – MSDE vs WMSDE vs SQL Server 2000 post for more on the strange world that is SharePoint storage.

This is one of those "after banging my head on the desk more times than I care to admit, I finally got x and y to play nice together; here's how you do it" posts.

I was trying to set up a test environment with a single-server deployment of SharePoint Portal Server (SPS) using an MSDE database. I also wanted to enable SSL on the server.

So — and this was my mistake — I started off by creating my own certificate with SelfSSL and installing it for the default virtual server in IIS. In theory, this virtual server would then be extended with WSS when I installed SPS, with the SSL certificate remaining in place. Didn't quite work that way, though.

In case you haven't heard about it, SelfSSL is one of the excellent free tools provided by Microsoft in the IIS 6.0 Resource Kit. It lets you quickly create self-published test certificates, allowing you to use SSL in your test environments. Great tool, but since it's a free (unsupported?) tool, it has some rough edges. It doesn't always play nice with other applications — in this case, MSDE.

MSDE is another useful, free product. It's the poor man's version of SQL Server 2000. Unless you're using it's the full SQL Server, SPS relies on MSDE to hold its data. When you install SPS with MSDE, it creates an MSDE instance named SharePointPortal.

However, the MSDE installation will fail if you have any SelfSSL certificates installed! Both WSS and SPS will install just fine, but MSDE will fail with several of these in the setup logs:

SSL Security error :ConnectionOpen (SECDoClientHandshake())

KB 309398 explains this in more detail, and tells you how to temporarily uninstall your certificates while you install MSDE. Alternately, I could have avoided all of this mess by waiting to install the SelfSSL certificate until after the SPS installation.

And one more thing…

The issue I described above will not affect you if you're just installing WSS (not SPS) with WMSDE on a server with a SelfSSL certificate.

WSS without SQL Server 2000 — essentially the poor man's version of SPS — installs WMSDE, which is a specialized version of MSDE that is, in some ways, actually more powerful than MSDE. For one thing, it isn't affected by the SSL issue.

Apparently, upgrading from WSS to SPS actually downgrades (crossgrades?) your WMSDE database to MSDE. Check out Graham Tyler's SharePoint Storage Overview – MSDE vs WMSDE vs SQL Server 2000 post for more on the strange world that is SharePoint storage.