All posts by David Wise

SharePoint

Accessing SharePoint List Data as XML

5 Comments

The other day, I found myself in need of a way to access SharePoint list data as XML but the only method available to me was a simple http GET.  This is not too bad, but what can I get from SharePoint via GET?  RSS is too limited and scraping the page is too painful and error-prone to even contemplate. 

It turns out that there is an option available in 2007 that was carried forward from SharePoint 2003 / FrontPage days: owssvr.dll.  But this isn't some forgotten FrontPage artifact, it is still a central part of SharePoint.  In fact, if you pull up a list view and then view the source looking for owssvr.dll, you will see that this is the mechanism behind both the Export to Spreadsheet and Open with Access options on the list Actions menu.

How It Works

Simply put together a URL like this:

http://MyServer/%5Bsite%5D/_vti_bin/owssvr.dll?Cmd=Display&List={listGuid}&XMLDATA=TRUE

This will only return the fields that are defined on the default view of the list.  If you need specific fields then you need to create a view with those fields and pass the View ID as well, like this:

http://…/owssvr.dll?Cmd=Display&List={listGuid}&view={viewGuid}&XMLDATA=TRUE

Specifying Fields to be Returned

There is also a Query parameter that lets you specify which fields are to be included in the resulting XML, regardless of how the view is defined.  For example, if you wanted just to bring back the Title and Status Fields, you would add the field names separated by spaces (URL Encoded, of course) like "&Query=Title%20Status".  If you want to return all fields, use an asterisk (*) instead of field names.

http://…/owssvr.dll?Cmd=Display&List={listGuid}&query=Title%20Status&XMLDATA=TRUE

Filtering Data

Regardless of whether you pass a view or use the default it will still use the filter defined by that view.  Not bad, but you can trim this data even more by including a filter of your own using the FilterFieldn and FilterValuen arguments in the querystring.  These are the same values that are passed when you use the filter options in the column headers of a view which makes it pretty easy to track down exactly what needs to be passed.  Simply pull up the view that is your starting point and use the column filters to create your desired filter.  Once you have it, grab all of the FilterField and FilterValue items from the querystring and add them on to yours.

http://…/owssvr.dll?Cmd=Display&List={listGuid}&query=Title%20Status&XMLDATA=TRUE&FilterField1=Status&FilterValue1=In%20Progress

For a full list of what can be done with this technique, check out the URL Protocol or the older Using the URL Protocol on MSDN.

Development

InfoPath 2007 / WCF Nasty Gotcha

1 Comment

I've spent the better part of two days tracking down an extremely nasty bug in InfoPath that corrupts an InfoPath form completely and almost terminally after connecting to a WCF (Windows Communication Foundation) web service.  Each time you try to open the form in design mode, InfoPath kicks out the error below and then refuses to open the form.

"InfoPath cannot open the following form: C:longpath oform.xsn.  The XML Schema file specified in the form definition (.xsf) file for dataObject 'xyz' cannot be used"

Once this happens, there is no way to get InfoPath to edit the form again.  However, there is a fix.

The Fix – Recovering your work

First, we have to get you so you can edit your form again.  Since the actual error hides itself completely during the initial design session it is possible to lose a lot of work, depending on how much you worked on the form during the session where you connected it to the web service.  Either way, you have to fix both problems in order to get the form working properly anyway.

  1. Create a new folder and copy your "corrupt" .XSN file to that folder. 
  2. Rename it from .XSN to .CAB (Yes, .XSN is really just a .CAB)
  3. Use a compression utility that can read CAB files (like WinZIP) to unpack the contents of the file into that folder. 
  4. Open the Manifest.xsf file in a text editor (NOT in InfoPath) and look for "<xsf:dataObjects>"  under that node, you will see several <xsf:dataObject> nodes. 
  5. Delete all <xsf:dataObject> nodes that point to WCF web services.  You can spot these easily because they usually have ".svc" somewhere in the wsdlUrl attribute of the <xsf:webServiceAdapter…> child node.
  6. Save the Manifest.xsf file. 
  7. Now right-click on the manifest.xsf and select 'Design'.  This should open InfoPath and will give you a warning that the form has been moved.
  8. Click ok. At this point, the form should open in design properly.  You should also see "[Read-Only]" in the title bar.
  9. Go through all views in your form and disconnect any controls that were bound to the WCF data sources.  Sometimes this is as simple as converting them from whatever type they are to text boxes.
  10. Click File -> Save As and save the file to the folder where you want the file to reside.  This will automatically save it as an .XSN file again.
  11. Close InfoPath (very important- don't just close the form)
  12. Browse to the the new folder and find the new .XSN file
  13. Right-click on it and select 'Design'.
  14. Click 'Preview' to preview the form.  If you get the error "InfoPath cannot open the selected form", it means that one of your controls is still bound to one of the data sources you removed earlier.  Check your controls again and also check any Rules you may have defined under Tools -> Form Options -> Open and Save -> Open behavior.

At this point, you should now be back editing your form as normal, just without the web service data.  Warning: do NOT reconnect to the web service until the next part of the fix is completed.  Doing so immediately lands you back at step 1!

The Fix – Fixing the Web Service

This will require the help of the author of the web service who will probably deny that there is an error and try to blame InfoPath because it works in all of his loosely typed test applications.  You must convince them that the problem really is with the way the service is defined.  Perhaps this entry will help, perhaps not.  Either way, he will have to do something with his web service if he wants that data exposed in your form because it won't work as it is right now.  I was lucky that the developer of the web service was every bit as curious about this error as I was so we were able to troubleshoot this from both ends.

The root of the problem is that one or more of the underlying data types is defined with an improper Namespace in the [DataContract] attribute or one that doesn't match all of the others.  InfoPath is extremely particular and if the namespaces don't match precisely, it will cause the error mentioned at the start of the article.  It is case-sensitive.

To track down exactly where the problem lies involves a little bit of detective work and some of the files extracted from Step 3.  When you created the Data Connection to the web service call in InfoPath you gave it a name like 'GetSomeDataFromJoe'.  If you look in the folder from Step 3, you should see 4 or 5 files called GetSomeDataFromJoeXX.xsd where XX is a number between 1 and 99.  These are the actual schema files that InfoPath pulled down when you connected to the web service.  Send these to the developer.  If he looks through them, he will find one (usually the second in the series) that shows the data types found in the web service.  One or more of the types he is using should be missing completely from the XSD files which means that those are the ones with the broken namespace.

After fixing this, the developer will need to build and then publish the web service before you can attempt to connect to it again.

Putting it All Back Together

Now that the developer has assured you that the fix has been made you will need to reconnect to the web service. To reconnect safely, try it this way:

  1. Backup your current working form (.xsn) from the first part of the fix – just in case the developer hasn't gotten the fix quite right yet.
  2. Open the form for design in InfoPath
  3. Reconnect to the web services
  4. Save the form and close InfoPath
  5. Reopen the form in InfoPath. 
  6. Switch to Design mode.
  7. If it opens properly then the developer cleaned up his namespaces and it is safe to reconnect your controls to the web service data.
  8. If you still get the "… XML Schema file … cannot be used" error, then the problem is still in the web service.  Delete the .XSN (because it is now corrupted again) and restore it from your backup.  If you jumped ahead and reconnected without backing up, then you get to do this whole thing all over again starting with Step 1 under "Recovering Your Work". 

Behind the Scenes (IMO)

The reason for the error is because of the way that InfoPath pulls the schema for a WCF web service.  When it connects and pulls down the schema info from the WSDL, it also pulls down all of the schemas for the data types used in the service.  In this case, the namespace of one of those schemas didn't match precisely so it couldn't be found later on, which is logical and expected behavior. However, InfoPath itself ignores that error during the session in which you originally connect to the web service.  It is not until you close InfoPath and then go back in that you get bitten by it.  What would be nice is if InfoPath kicked out the error when you connected to the service rather than laying a trap for you later on.

The truly curious part of all of this is that despite having an invalid schema, you can still open the "corrupt" .xsn file for data entry in InfoPath and it works perfectly.  One would think that something as substantial as a broken schema would kill the form no matter what.

SharePoint

Getting to SharePoint Usage Data

Leave a comment

I was challenged recently.  It was claimed that there was no way to get usage data out of SharePoint beyond the normal SiteActions -> Site Usage Reports page, which isn't terribly useful if you need actual numbers.

Sure, that page includes some nice graphs and some of the data that is available and it saves the typical user from having to create a common bunch of graphs by hand and even has an option to export the data to Excel.  The problem is the data being exported does not include actual hit data, but averages over the last 30 days.  If you need more detailed information than that, it is not readily available from this summary page.  Which was where the challenge began.

Simple Usage Reports – Just the facts!

SharePoint 2003 had a hidden usage page at /_layouts/1033/usagedetails.aspx and that had decent data.  The good news is that this is still hidden in 2007 but has been moved just slightly to /_layouts/usagedetails.aspx (it redirects automatically).  It has detailed data for Page, User, OS, Browser and Referrer and can slice the data into a Monthly Summary or a Daily Summary.

FliCC

FliCD

 

The bad news is that this data cannot be directly exported in any form – but it does copy-paste very nicely into Excel so it is situationally useful.  However, most people really don't want to be doing this manually all the time.

Technically, I had beaten the challenge there, but my programmer instincts kicked in because I kept thinking: "There has to be a better way!" So I kept looking and found a bunch more.

Getting the data remotely

The same data shown above is also available via http GET using the owssvr.dll, as shown below.  To get the last 30 days, change the BlobType parameter to "Old"

http://%5Bserver%5D/%5BSite/_vti_bin/owssvr.dll?Cmd=GetUsageBlob&BlobType=Current

Ah, but there is a catch.  The data it returns is a BLOB (Binary Large OBject) and your browser doesn't know what to do with it.  Fortunately, there is a BlobParser (Download) available from Microsoft that can split this data out.  Catch #2 is that this parser is a C++ Windows application and most of us are in a C#/VB.Net web-based world.  The textbox that this app uses to output the parsed data is also Excel-friendly so that data can be copy-pasted here as well.  Useful, but still not quite what I was looking for.

If you are interested in pursuing this, the format of the blob data is available on MSDN for C++.  Yeah, you'll have to convert it manually to whatever language you are using.

 

Using the SharePoint object Model

SPSite.UsageInfo [link] – top level stats but nothing like what a typical stat request requires

In order to get actual stats, you need to go to the SPWeb for each site you want info on and call GetUsageData(), passing the proper parameters for the data desired.  Pretty simple and this returns the same data as the BLOB above only in a more useful form, a DataTable.  From here, you can do whatever you want with it.  Export it, save it to disk, stream it to the browser, toss it in a database, etc..  You get the idea.  (Very Important – remember to .Dispose() of your objects!)

Of course, this means deploying code onto your servers, but you are getting pretty good at that by now, aren't you?

.Net version is here and here –

 

[[ is there a web service? ]]

[[ Direct SQL ]] – include normal caveat from MS: don't hit the DB directly cuz it might change in a service pack … blah blah blah

 

So, the next time you hear someone say that there is no way to get usage data out of SharePoint, you can tell the right where to go!   (Here, I mean.  What were YOU thinking?)

SharePoint

Proper Disposal of Objects in SharePoint

Leave a comment

I just ran across this article on MSDN that is a fantastic read.  If you do any work where you manipulate SharePoint objects via code, especially SPSite and SPWeb then you must read this.  The article is outlines many of the gotcha's lurking in the object model that you may not be aware of.  There is also another article that touches on object disposal as well but lists a few other areas to watch for.

If you are seeing performance, memory and stability issues in a SharePoint server that is running custom code then that code should be probably be reviewed while the above articles are fresh in mind.

Development

Bizarre InfoPath Dialog

Leave a comment

I'm trying to open a local copy of an InfoPath form that was originally opened from SharePoint.  The actual .xsn file that I've been working with is on my local file system and that is where I'm opening it from.  However, when I open it, I get the dialog below:

Fli118

 

Ok…. so … I know the template on my local machine is the one I want to open and I know that it is different than what is in SharePoint, so I naively click on the "Keep Form Template on Your Computer" button, thinking that it will, you know, keep the one that is on my computer rather than the one in SharePoint.  Silly me.  That option actually opens the original template from SharePoint, which is a "replace" of my local copy in my way of thinking of things.

Now, I realize that a very careful reading of the actual message (which nobody actually does until after the fact) will somewhat point you to the right answer, but having to click "Replace…" in order to keep local changes and "Keep…" to replace local changes is just a little bit counter-intuitive. Maybe a "use this one" button under each option might have been clearer?  Just a thought…