SQL Server 2008 R1 Reporting Services reports on a SharePoint 2010 Document Library

• Thursday, March 15, 2012 1:02 PM

   

   
  

  0

  Dear All,

  I am trying to create a SQL Server Reporting Services report based on a SharePoint 2010 document library, but am running into issues which I don't understand!

  The production environment I am ultimately aiming for is running SQL Server 2008 R1, so does not have access to the datasource type of "Microsoft SharePoint List". Therefore I am trying to use the datasource type of "XML".

  The SharePoint site on the production environment I am connecting to is called http://SharePoint/guide, and I have a document library called "Guidelines Documents", but with a URL of "g docs1" (http://SharePoint/guide/g%20docs1)

  The settings I have used when creating a data source in Reporting Services are as follows:
  * Type: XML
  * Connection String: http://SharePoint/guide/
  * Credentials: Use Windows Authentication (Integrated Security)
  in the "query builder..." screen:
  * command type: Text
  * Query text: 
   <Query>
   <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
   <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
   <Parameters>
       <Parameter Name="listName">
          <DefaultValue>G docs1</DefaultValue>
       </Parameter>
   </Parameters>
   </Method>
   </Query>

  If I hit the exclamation mark (execute) button on the query designer window, I get the following message:

   TITLE: Microsoft SQL Server Report Designer
   ------------------------------
   An error occurred while executing the query.
   Error while reading xml response.
   ------------------------------
   ADDITIONAL INFORMATION:
   Error while reading xml response. (mscorlib)
   ------------------------------
   'ie' is an undeclared namespace. Line 138, position 106. (System.Xml)
   ------------------------------
   BUTTONS:
   OK
   ------------------------------

  and if I drill into the technical details of the error, I get:

   ===================================
   An error occurred while executing the query.
   Error while reading xml response. (Microsoft SQL Server Report Designer)
   ===================================
   Error while reading xml response. (mscorlib)
   ------------------------------
   Program Location:

   Server stack trace:
      at Microsoft.ReportingServices.DataExtensions.XmlDP.InMemoryCommand.Execute()
      at Microsoft.ReportingServices.DataExtensions.XmlDP.InMemoryCommand.<>c__DisplayClass1.<ExecuteReader>b__0()
      at Microsoft.ReportingServices.DataExtensions.XmlDP.WebRequestHelper.ExecuteServerCallAsNtUser(ServerCall serverCall, WindowsIdentity ntUser)
      at Microsoft.ReportingServices.DataExtensions.XmlDP.InMemoryCommand.ExecuteReader(CommandBehavior behavior)
      at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.AsyncExecuteReader(IDbCommand command, CommandBehavior behaviour)
      at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
      at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
      at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
   
   Exception rethrown at [0]:
      at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
      at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
      at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.ExecuteReaderHandler.EndInvoke(IAsyncResult result)
      at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.<>c__DisplayClass1.<ExecuteActiveQueryCallback>b__0()
      at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.EndExecuteReaderInUiThread(GetDataReaderHandler getDataReaderCallback)
   ===================================
   'ie' is an undeclared namespace. Line 138, position 106. (System.Xml)
   ------------------------------
   Program Location:
   
      at System.Xml.XmlTextReaderImpl.Throw(Exception e)
      at System.Xml.XmlTextReaderImpl.Throw(String res, String arg, Int32 lineNo, Int32 linePos)
      at System.Xml.XmlTextReaderImpl.LookupNamespace(NodeData node)
      at System.Xml.XmlTextReaderImpl.ElementNamespaceLookup()
      at System.Xml.XmlTextReaderImpl.ParseAttributes()
      at System.Xml.XmlTextReaderImpl.ParseElement()
      at System.Xml.XmlTextReaderImpl.ParseElementContent()
      at System.Xml.XmlTextReaderImpl.Read()
      at System.Xml.XmlTextReader.Read()
      at Microsoft.ReportingServices.DataExtensions.XmlDP.InMemoryCommand.ProcessSoapHeadersAndSetReaderOnResponse(XmlReader reader)
      at Microsoft.ReportingServices.DataExtensions.XmlDP.InMemoryCommand.Execute()

  I am not mentioning an "ie" namespace anywhere in my query or anything else that I see that I can control. Does anybody know what is going on here?
   
  To try and identify why this problem was occurring I used a second environment with SQL Server 2008 R2 but still using the "XML" data type. This appears to be a similar setup to above, but I get a different error:
  

  
   ===================================
   An error occurred while executing the query.
   Error while reading xml response. (Microsoft SQL Server Report Designer)
   ===================================
   Error while reading xml response. (mscorlib)
   ------------------------------
   Program Location:
   
   Server stack trace:
      at Microsoft.ReportingServices.DataExtensions.XmlDP.InMemoryCommand.Execute()
      at Microsoft.ReportingServices.DataExtensions.XmlDP.InMemoryCommand.<>c__DisplayClass1.<ExecuteReader>b__0()
      at Microsoft.ReportingServices.DataExtensions.XmlDP.WebRequestHelper.ExecuteServerCallAsNtUser(ServerCall serverCall, WindowsIdentity ntUser)
      at Microsoft.ReportingServices.DataExtensions.XmlDP.InMemoryCommand.ExecuteReader(CommandBehavior behavior)
      at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.AsyncExecuteReader(IDbCommand command, CommandBehavior behaviour)
      at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
      at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
      at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
   
   Exception rethrown at [0]:
      at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
      at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
      at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.ExecuteReaderHandler.EndInvoke(IAsyncResult result)
      at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.<>c__DisplayClass1.<ExecuteActiveQueryCallback>b__0()
      at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.EndExecuteReaderInUiThread(GetDataReaderHandler getDataReaderCallback)
   ===================================
   DTD is prohibited in this XML document. (System.Xml)
   ------------------------------
   Program Location:
   
      at System.Xml.XmlTextReaderImpl.Throw(Exception e)
      at System.Xml.XmlTextReaderImpl.ThrowWithoutLineInfo(String res, String arg)
      at System.Xml.XmlTextReaderImpl.ParseDoctypeDecl()
      at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
      at System.Xml.XmlTextReaderImpl.Read()
      at System.Xml.XmlTextReader.Read()
      at Microsoft.ReportingServices.DataExtensions.XmlDP.InMemoryCommand.ProcessSoapHeadersAndSetReaderOnResponse(XmlReader reader)
      at Microsoft.ReportingServices.DataExtensions.XmlDP.InMemoryCommand.Execute()

  Again, I can't see why it's complaining about using DTD; I don't believe I am using a DTD (but I might be wrong!).

  I have tried installing ADO.NET Data Services Update for .NET Framework 3.5 SP1 (not appropriate) and ADO.NET Data Services v1.5 CTP2 (as per: http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpointforsharepoint/thread/ee506212-6d65-462d-93e6-59b6f4f416c2#c1306e4f-b674-42df-a33a-9efa9c1f5589)

  Does anybody have any ideas how I can troubleshoot this further? I am trying to answer the issues on my production environment ultimately; but suggestions why it won't work on my development environment are also welcome!

  Thanks in advance!

  Paul.

  
  

  • Edited by onatandemmission Thursday, March 15, 2012 1:02 PM
  •  
  • Reply
  • Quote