Data Source Issues and Workarounds

Hey everyone! I’m Steven Weber, and I want to talk about a couple of issues with SharePoint Designer and SOAP services, and more importantly, provide tips on how to get around them.

Issue 1: Accessing SQL Server SOAP objects

One of the cool features in SQL 2005 was the ability to automatically create SOAP services from stored procedures or T-SQL. SQL Server takes care of all of the SOAP implementation details, so all you have to worry about is getting the queries correct! There are some permissions concerns to be aware of – SQL doesn’t allow anonymous access to these services, so you’ll need to jump through some extra hurdles to get everything working (if you’re not in a production environment, the easy way around all of these problems is to install SharePoint, SPD, and SQL on the same box). There’s more information on SQL SOAP services at https://msdn.microsoft.com/en-us/library/ms191310.aspx.

SharePoint Designer can access these services just like any other SOAP service. Just fire up the Data Source Library task pane, and add a new XML Web Service, point it at your SQL box (https://sqlservername/?wsdl), and you’re good to go! Well, almost anyway. It turns out that if you try to view the data for this web service, you’ll get the uninformative error message: "The server returned a non-specific error when trying to get data from the data source. Check the format and content of your query and try again. If the problem persists, contact the server administrator."

So how do we solve this problem?

In the folder list, open the _catalogs folder, then the fpdatasources folder. In this folder, you should see a XML file with the name of the SQL SOAP service that you created in the Data Source Library. Double click on it to edit the file. Search for “SelectAction” (assuming, of course, that your SOAP service was getting data). Now, add single quotes before and after the double quotes, so the SelectAction attribute looks like this: SelectAction='"https://..."'. Now save the file, and try to show the data again. Like magic, it’s suddenly working!

Issue 2: Using <> in SOAP calls

SharePoint has a default web service called Lists.asmx (https://servername/_vti_bin/lists.asmx?wsdl) which provides access to lists and their items. One of the operations it supports is called “GetListItems,” which returns information about items in the list based on the specified query. More information about this operation is at https://msdn.microsoft.com/en-us/library/lists.lists.getlistitems.aspx).

In the Data Source Library, create a new XML Web Service source, and connect to lists.asmx. One of the parameters for GetListItems is called “query.” It takes a CAML block and filters the list items based on the results of that query. Enter a valid CAML query (go ahead and steal it from the MSDN article – I won’t mind), and save the data source.

If you view the data, you’ll once again get the standard error message: "The server returned a non-specific error when trying to get data from the data source. Check the format and content of your query and try again. If the problem persists, contact the server administrator."

What’s going on this time? It turns out that the soapdatasource object is incorrectly over-encoding the greater than and less than characters, so when it gets sent to the server, it’s not valid XML anymore. Over-encoding will occur on any field in the SOAP call that contains these characters.

So how do you get around this problem? For most of the items in the CAML filters as well as sorting or paging, you simply need to add those to the DataFormWebPart instead.  Create the data source again, but this time leave the query parameter blank.  Save the source, then add it to a page.  Click on the On Object UI box to bring up the Common Data View Tasks, then select Filter, Sort or Paging.  This brings up the appropriate dialog box where you can create the view you want.  Other properties like FieldRefs and queryOptions will not work in the SOAP envelope and will need to be worked around in the view (for example, removing fields from the view itself after it’s been created).

The downside to this approach is that you’ll need to set these filter values on every DataFormWebPart instead of just once, at the data source level.