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.

Comments

• Anonymous
  June 20, 2008
  Hi, So how do we do in a production environment if we don't run Kerberos? Thanks /Jonas

• Anonymous
  June 22, 2008
  PingBack from http://www.scriptbest.com/2008-06/team-double-click/

• Anonymous
  June 23, 2008
  Thanks for the info.  I have a question.  I'm trying to make a connection to a web service that is on another IIS server.  The method I'm calling does not require any parameters.  When I try to view the data, I get the error you mentioned.  I tried your trick and that did not solve the problem.  Any ideas?

• Anonymous
  August 27, 2008
  The comment has been removed

• Anonymous
  September 15, 2008
  Has anyone found an answer to Tim's question?  I am running in the same kind of a problem

• Anonymous
  February 01, 2009
  Hi, Is there a way of setting relative URL's or setting URL from a configuration element in a SharePoint:SoapDataSource SelectUrl property e.g. SelectUrl="/_vti_bin/usergroup.asmx" Zohaib

• Anonymous
  February 19, 2009
  Hi, I am struggling in displaying the "showdata" after passing the queryex to the search webservice in sharepoint designer .I am getting the following error " 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" i searched many threds,could not find any solution. please respond MVP's and geeks. i can retrieve the results using the tool http://www.mosssearch.com/searchwebservice.html provided by Shankar's musings article http://techdhaan.wordpress.com/2008/06/03/moss-2007-employee-directory-web-part-using-search-and-data-view-web-parts/ here is my queryEx: <QueryPacket xmlns="urn:Microsoft.Search.Query"> <Query><SupportedFormats><Format>urn:Microsoft.Search.Response.Document:Document</Format></SupportedFormats><Context> <QueryText type="MSSQLFT" language="en-us">select preferredname,Title, Path, Description, Write, Rank,Size from scope() where "scope" = 'people' order by preferredname ASC</QueryText></Context><Range><StartAt>1</StartAt><Count>100</Count></Range><EnableStemming>true</EnableStemming><TrimDuplicates>true</TrimDuplicates><IgnoreAllNoiseQuery>true</IgnoreAllNoiseQuery><ImplicitAndBehavior>true</ImplicitAndBehavior><IncludeRelevanceResults>true</IncludeRelevanceResults><IncludeSpecialTermResults>true</IncludeSpecialTermResults><IncludeHighConfidenceResults>true</IncludeHighConfidenceResults></Query></QueryPacket> and i also can see the result query by using this code in visual studio webreference program.. this is the program i used for debugging: class Program    {        static void Main(string[] args)        {            sharepointdev.QueryService qs = new ConsoleApplication2.sharepointdev.QueryService();            qs.PreAuthenticate = false;            qs.Credentials = System.Net.CredentialCache.DefaultCredentials;            DataSet ds = new DataSet();            //string ds;            ds = qs.QueryEx(@"<QueryPacket xmlns=""urn:Microsoft.Search.Query""> <Query><SupportedFormats><Format>urn:Microsoft.Search.Response.Document:Document</Format></SupportedFormats><Context> <QueryText type=""MSSQLFT"" language=""en-us"">select preferredname,Title, Path, Description, Write, Rank,Size from scope() where ""scope"" = 'people' order by preferredname ASC</QueryText></Context><Range><StartAt>1</StartAt><Count>1000</Count></Range><EnableStemming>true</EnableStemming><TrimDuplicates>true</TrimDuplicates><IgnoreAllNoiseQuery>true</IgnoreAllNoiseQuery><ImplicitAndBehavior>true</ImplicitAndBehavior><IncludeRelevanceResults>true</IncludeRelevanceResults><IncludeSpecialTermResults>true</IncludeSpecialTermResults><IncludeHighConfidenceResults>true</IncludeHighConfidenceResults></Query></QueryPacket>");        }    } I have also checked the query by directly passing it to queryex method in query service. http://sharepointdev/_vti_bin/search.asmx?op=QueryEx and I do get the result set. all i need is to get the result set once i clik on show data in sharepoint designer. have followed this article(http://techdhaan.wordpress.com/2008/06/03/moss-2007-employee-directory-web-part-using-search-and-data-view-web-parts/) and can not see the result after repated trails in every possible way. seems like problem is with the sharepoint designer. I have even tried with the sharepoint support link: http://support.microsoft.com/kb/923173 but has no luck any suggestions or help is much much appreciated.... can somebody please help me. naresh

• Anonymous
  May 13, 2009
  It is actually possible to set the <queryOptions> options directly in the SOAP request instead of "working it around in the view". To do that, modify the SOAP envelloppe node directly in SoapDataSource node that has been added to the aspx page after adding the datasource web part. You can then add any parameters you want. Can be usefull to specify the IncludeAttachmentURL options on List Items. Jonathan

• Anonymous
  July 17, 2009
  I am attempting to reference lists.asmx in SharePoint Designer to return the contents of a project tasks list and display it in a DataViewWebPart.  From the data source library tab, I add lists.asmx and use GetListItems to return the fields.  I get every field I need except status.  How do I get the status field to display?

• Anonymous
  December 13, 2009
  Still getting this error? Check this article: http://wadingthrough.wordpress.com/2008/05/21/data-view-web-part-web-services-aamsoh-my/ Worked for me!

• Anonymous
  February 19, 2010
  I'm having an issue when trying to add a data source.  I need to share a list from a parent site to a sub-site.  Initially I was able to setup the data source, but had what I thought was an issue, so I removed the data source.  When I figured out my other issue, now when I go back to previous sub-site and attempt to add the data source back in, it throws an error and tells me it can't connect.  I see nothing left of the previous data source in the data source library, but I'm thinking there may be something I'm not seeing somewhere else that needs to be deleted before it will allow me to re-add the parent site as a data source. Any ideas?

• Anonymous
  February 19, 2010
  Never mind last post.  I just tried it for the 20th time and it finally added back in.

• Anonymous
  January 11, 2012
  So how do you use a DataFormWebPart to connect to a web service authenticating with SSO (single sign-on)?  In SharePoint 2010 this is easy, but in MOSS it is undocumented!