1 Reply Latest reply: May 15, 2014 10:05 AM by mdyoung _ RSS

    Querying Sharepoint Lists Using Monarch

    ARICE01 _

      Ideally, I would like to directly query Sharepoint 2007 List using Monarch v10.  I am not aware that this is possible.  My program management office utilize Sharepoint extensively to collaborate with our Project Managers all over the world.


      We currently export Sharepoint list as MS Access files or Excel files, where we then read, minipulate and report using Monarch v10.  The inefficiency of this process is the interim step of exporting from Sharepoint, and/or the opening of Excel to refresh the List data before opening the Monarch Projects to do the analysis and reporting.


      Does anyone have any slick suggestions where we can read Sharepoint lists directly of invoke a procedure, possibly using a command file, to refresh the Access or Excel files and quickly get into the Monarch projects to do the real work.


      I believe all we want to do is possible using Data Pump, but we are not using the software and not likely to buy it in this economy.


      Any helpful suggestions would be much appreciated... Regards,

        • Querying Sharepoint Lists Using Monarch
          mdyoung _



          I do know one way that does work without having to export anything, but it is a little cumbersome to setup. Below is a quick summary of what is involved. This technique does require you having at least datareader access to the SQL Server database that's hosting your SharePoint data.


          Use MS Access as the bridge between Monarch and the actual SQL Server that is hosting the SharePoint database.

          Link to the "Lists" and "UserData" tables, which contains data related to all SharePoint lists.

          Create MS Access query to pull the information you need.

          Use Monarch to connect to the MS Access database and pull from the created query.



          Now for the details....


          First off, I'm using Monarch Pro v9, Access 2003 (2007 will work), and SharePoint 2007 services.


          Either create a new MS Access database or use an existing one of your choice.


          Use the "Link Table" feature to connect to the SharePoint SQL Server database.


          Select the "dbo.Lists" and "dbo.UserData" tables.

          Here are some things to be aware of concerning the "UserData" table (this is where the "[I]cumbersome[/I]" part kicks in).

          The list's column names are not included. Data is stored in fields such as nvarchar1, nvarchar2, int1, int2, int3, etc. depending on the column type definition when the list was created. However, the list's column names are defined in the "Lists" table, but they are in XML format. So pretty much useless in this solution.


          The column order on the SharePoint list isn't necessarily in the same order as the field names in the database table. For instance:  If the SharePoint list is in the order of Column1, Column2, and Column3, the data may be stored in nvarchar3, nvarchar1, nvarchar2 respectively. You'll just have to figure out which column in the list matches up to which field in the table.


          If a column in the SharePoint list actually references or links to another list, the linking column will more than likely be one of the "int" fields in the database table, which represents the primary ID key of the other list.



          Create a query that pulls from the "UserData" table. If you need to link to the "Lists" table, link using the tp_ID /Bfield in the "Lists" table to the tp_ListID /Bof the "UserData" table.


          Select the fields that you want and give them an alias name that matches the column names of the SharePoint list.


          In the criteria section of the tp_ListID /Bfield from the "UserData" table, paste (with brackets) the list ID. You can find the list ID using the following steps:


          Go to the SharePoint site and click on the desired list.


          Click on the "Modify settings and columns" link under the "Actions" section.


          Look at the URL. The list ID will be in between the curly {} brackets. When copying the ID, make sure to include the brackets.



          Once you have the query pulling the data you want, save and close it.



          Repeat step 4 for every list you may need to reference or link together. To clarify, you should have one query for every list you want to use.


          If you made more than one query, make a final query that pulls the other queries together; this way, you have only one query to pull into Monarch.


          Once you have the final query you need, close the Access database file.


          Open Monarch, connect to the Access file, and pull in the desired query's data.



          I did try this on my own SharePoint environment and it worked just beautifully. Hope this helps you out... or at least gives you another idea on how to solve this issue. If you don't use my way, I sure would be interested knowing what solution you did come up with.




          Micheal Young