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

    Question

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


    Thursday, March 15, 2012 1:02 PM

Answers

All replies