Hi,

I'm using version 2.1.5.26079 of the RS Data Extension. On my development computer, everything is working fine. On my report server, I keep getting the following error : System.Xml.XmlException: Root element is missing.

Here is a part of the Reporting Services Log covering the error:

w3wp!processing!1!10/8/2008-14:39:25:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create command for data source 'SharePoint_Data'., ;
 Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create command for data source 'SharePoint_Data'. ---> System.Xml.XmlException: Root element is missing.
   at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.ThrowWithoutLineInfo(String res)
   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
   at System.Xml.XmlTextReaderImpl.Read()
   at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.LoadXml(String xml)
   at Enesys.ReportingServices.Ersde.ErsdeCommand..ctor(ErsdeConnection connection)
   at Enesys.ReportingServices.Ersde.ErsdeConnection.CreateCommand()

I'm running the evaluation version of the Data Extension and I have installed it this morning on my report server.

Thank you in advance,

Eric Brousseau
Software developper,
AIA Automation Inc.

Hi,

Here is the query I was using:

<?xml version="1.0" encoding="utf-8" ?>
<root>
    <list title="Contrats" relativeSiteUrl="" tableName="Contracts" useDisplayName="true">
        <fields>ID, Numéro de contrat, Description, Date d'échéance, Actif</fields>
        <query></query>
    </list>
    <list title="Activités" relativeSiteUrl="" tableName="Activities" useDisplayName="true">
        <fields>ID, Code Activité, Description, Actif</fields>
        <query></query>
    </list>
    <list title="Secteurs" relativeSiteUrl="" tableName="Sectors" useDisplayName="true">
        <fields>ID, Code Secteur, Description, Actif</fields>
        <query></query>
    </list>
    <list title="Activités spécifiques" relativeSiteUrl="" tableName="SpecificActivities" useDisplayName="true">
        <fields>ID, Code Activité, Description, Actif</fields>
        <query></query>
    </list>
    <list title="Tâches" relativeSiteUrl="" tableName="Tasks" useDisplayName="true">
        <fields>ID, Contrat, Activité, Secteur, Activité spécifique, Titre, Priorité, Responsable Interne, Responsable Externe, Entrepreneur, Date d'échéance, Actif</fields>
        <query></query>
    </list>
    <list title="Suivis" relativeSiteUrl="" tableName="Trackings" useDisplayName="true" stripHtml="true">
        <fields>Tâche, Date, Détails, Created By</fields>
        <query></query>
    </list>

    <sqlOp op="join">
        <dstTableName>TasksAndContract</dstTableName>
        <parentTableName>Tasks</parentTableName>
        <childTableName>Contracts</childTableName>
        <parentFieldName>ContratID</parentFieldName>
        <childFieldName>ID</childFieldName>
    </sqlOp>
    <sqlOp op="join">
        <dstTableName>TasksAndActivities</dstTableName>
        <parentTableName>TasksAndContract</parentTableName>
        <childTableName>Activities</childTableName>
        <parentFieldName>ActivitéID</parentFieldName>
        <childFieldName>ID</childFieldName>
    </sqlOp>
    <sqlOp op="join">
        <dstTableName>TasksAndSectors</dstTableName>
        <parentTableName>TasksAndActivities</parentTableName>
        <childTableName>Sectors</childTableName>
        <parentFieldName>SecteurID</parentFieldName>
        <childFieldName>ID</childFieldName>
    </sqlOp>
    <sqlOp op="join">
        <dstTableName>TasksAndSpecAct</dstTableName>
        <parentTableName>TasksAndSectors</parentTableName>
        <childTableName>SpecificActivities</childTableName>
        <parentFieldName>Activité spécifiqueID</parentFieldName>
        <childFieldName>ID</childFieldName>
    </sqlOp>
    <sqlOp op="join">
        <dstTableName>FullTracking</dstTableName>
        <parentTableName>Trackings</parentTableName>
        <childTableName>TasksAndSpecAct</childTableName>
        <parentFieldName>TâcheID</parentFieldName>
        <childFieldName>ID</childFieldName>
    </sqlOp>

    <resultSet>FullTracking</resultSet>
</root>

This query is working fine on my development computer in VS2005.

As for using version 3, I am supposed to deliver this product by the end of the week, so we cannot wait for version 3 to come out. Still, I've been taking a look at the new SqlQuery statement and it would have been something nice to use in this project, since I need to run filter queries on all 5 linked tables. But well, the report is already working right now in version 2.

Eric Brousseau
AIA Automation Inc.

P.S.: Je parle aussi français, si ça peut vous simplifier la vie.

Hi,

I've just tried to delete and re-deploy the report. No luck.

Can version 3 of the Data Extension be used with version 2 queries? If so, I might try using version 3 on my server and see if this helps.

Eric Brousseau
AIA Automation Inc.

Yes version 3 will be able to execute "SqlOp" operations for compatibility purposes though they should be considered "obsolete".

Even if I understand that you have already worked out your report using the version 2.1.5, I would recommend version 3 for a new installation as all our efforts are geared toward this new version. This version also includes a logging framework that makes it possible to collect detailed information when encountering problems difficult to reproduce.

I suppose your SharePoint server is a Windows SharePoint Services installation as MOSS requires AD as far as I know.

I propose the following actions:

To check that you can access SharePoint xml web services from your server, you can try to use SSRS XML Data source. We have already created 2 reports relying on an embedded XML datasource. One for retrieving list columns, another one for retrieving list items.
 The reports are available at the following address: GetListUsingXmlDataSource.zip.

You will need to change the connection string of the embedded data source to correspond to your own server and list:

Install ERSDE Version 3 on both your server and your BIDS (report designer) installation. It will not overwrite your current version 2 installation. They can be installed side by side. Once it is installed, a new Datasource type will be available (Enesys SharePoint Data Extension 2008) so that you can report over both version by specifying the appropriate datasource type.

• Create a new shared Datasource of type "Enesys SharePoint Data Extension 2008".
• Create a copy of the very simple report (with just one list) you have already created for test purpses.
• Change this copy so that it points to the new datasource.
• Ensure it works within BIDS and deploy the report (it should also deploy the new datasource). Execute the report at the server level.

Please let me know the results.

Regards,

Hi,

Well, it seems I spoke a little too fast about having everything working... The "sqlOp" tag from my original query is causing errors in version 3. I get a "Syntax error: missing operand after 'échéance' operator. (System.Data)

It seems that having a single quote ( ' ) character in one of the field name (in this case "Date d'échéance") is causing this error.

I'll try to rewrite the query with the newer construct from version 3 to see if I still get this error.

Eric Brousseau
AIA Automation Inc.

Hi,

I've re-written my queries using a "sqlQuery" tag and it works now.

It's just too bad that the naming scheme for the columns has changed from V2 to V3. Personnaly, I prefered the "TableName.FieldName" syntax when retrieving fields with the same name from different table rather than "FieldName:TableIndex". But at lease I can use AS clauses in the sqlQuery to rename these fields as I want.

Regards,

Eric Brousseau
AIA Automation Inc.