Data Source Issues and Workarounds

Data Source Issues and Workarounds

  • Comments 13

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 http://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 (http://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='"http://..."'. 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 (http://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 http://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.

  • Hi,

    So how do we do in a production environment if we don't run Kerberos?

    Thanks

    /Jonas

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

  • 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?

  • I've been getting an 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" when trying to use a data view web part to display an XML Web Service with SPD.

    The web service connects OK but when I click "Show Data" I get the error. I have tried the suggestion from Steven here but I still get the error

    Here is my web service XML file after modifying it.

    <udc:DataSource xmlns:udc="http://schemas.microsoft.com/data/udc" MajorVersion="2" MinorVersion="0"><udc:Name>Webs on mvm-001</udc:Name><udc:ConnectionInfo><DataSourceControl><![CDATA[<%@ Register tagprefix="SharePoint" namespace="Microsoft.SharePoint.WebControls" assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %><SharePoint:SoapDataSource runat="server" id="SoapDataSource" AuthType="None" WsdlPath="/Projects/_vti_bin/Webs.asmx?WSDL" SelectUrl="http://xxx-xxx/Projects/_vti_bin/Webs.asmx" SelectAction='"http://schemas.microsoft.com/sharepoint/soap/GetWebCollection"' SelectPort="WebsSoap12" SelectServiceName="Webs"><SelectCommand><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/ xmlns="http://schemas.microsoft.com/sharepoint/soap/" /></soap:Body></soap:Envelope></SelectCommand></SharePoint:SoapDataSource>]]></DataSourceControl></udc:ConnectionInfo><udc:Type MajorVersion="1" MinorVersion="0" Type="Soap"/></udc:DataSource>

    The server I am working on is inside a facility behind a firewall that is doing NAT - 1:1 and therefore I have set up AAM with the internal and public mapping. But I still get the error when trying to Show Data.

    So I installed SPD on the box inside the facility and set up the data view and it works fine when viewed through a remote console session on that box.  Outside though (through a browser), the web part displays an "Unable to display this web part" error. I used relative URL.

    Does anyone have any thoughts on what I could do to work around this issue?

    Thanks in advance

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

  • 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

  • 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

  • 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

  • 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?

  • 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!

  • 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?

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

  • 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!

Page 1 of 1 (13 items)
Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post