Programmatically adding SharePoint Lists to external SQL database

Question

I need to export a SharePoint list to an external SQL database. My initial idea is to open the list as a datatable and bulk update. I created a console app I can run anytime I need to sync the data. Something like this:

 SPSecurity.RunWithElevatedPrivileges(delegate
 {
    using (SPSite site = new SPSite("http://MySite"))
    {
      using (SPWeb web = site.OpenWeb())
      {
         SPList myList = web.GetListFromUrl("/default/Lists/TestList/AllItems.aspx"); 
         SPListItemCollection myListCollection = myList.Items;
         BulkUpdate(myListCollection.GetDataTable(), "dbo.TEST_Table");
      }
   }
});


    private void BulkUpdate(DataTable table, string destName)
    {
        using (SqlConnection conn = new SqlConnection(connString))
        {
            using (SqlBulkCopy copy = new SqlBulkCopy(conn))
            {
                copy.ColumnMappings.Add(0, 0);
                copy.DestinationTableName = destName;
                copy.WriteToServer(table);
            }

        }
    }

Unfortunately, it isn't doing anything. I'm getting a COM error: "failed due to the following error: 80040154."

I'm not sure where it's coming from. If I remove the delegate to look into the code, it just tells me it's an invalid site, since it's being accessed externally.

Any thoughts?

Answer 1

I've been away from this for a while, but my solution was this - I was running a console app with SharePoint client dlls in MOSS. It's just not supported.

Answer 2

I think your list names have gone wrong. I can see that you are getting myListCollection from the list object myList.

 SPListItemCollection myListCollection = myList.Items;

But then the next statement,

BulkUpdate(labList.GetDataTable(), "dbo.TEST_Table");

This is using a list called lablist. Where did that come from? Or am I missing something?

Answer 3

OK, from bottom to top REM out the contents of your delegate. When does the error go away?

Do you have contents in your list? Can you try another list? In your code, can you try instantiating a new datatable, and copying the list content into there, and then loading that datatable's contents in the bulkupdate?

Answer 4

If you run console app on one of SharePoint server, app should work, but I think you forget to change platform from x86 to x64. This is common mistake when developing console application for SharePoint. Check Visual Studio project settings -> Build -> Platform target -> set x64.