Using OLEDB with SharePoint Lists

I noticed the folks at connectionstrings.com provide an example connection string which uses OleDb to connect to a SharePoint list. Being a database person, the idea of querying a SharePoiint list like a table sounded interesting, so I set out to write a few lines of Powershell to test the idea:
 
The only install needed is the 2007 Office System Driver: Data Connectivity Components. Note: This step is not necessary if you have Office 2007. Next I created a new SharePoint list called "test" with a default column of "Title" and added a couple of items to the list using the SharePoint UI.  Using the technique described in Finding the Id (Guid) for a SharePoint List, I obtained the GUID of SharePoint list which is used to construct  the connection string.
 
The following Powershell commands illustrate selecting, updating and deleting items from a SharePoint list. 
    

#Select
$connString = ‘Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://sharepoint.acme.com/IT/DBAdmin/;LIST={a113df9b-e56e-49d2-b786-03d170d18dbc};
$spConn = new-object System.Data.OleDb.OleDbConnection($connString)
$spConn.open()
$qry=‘Select * from list’
$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$spConn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
$da.fill($dt) > $null
$dt

#Update
$connString = ‘Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://sharepoint.acme.com/IT/DBAdmin/;LIST={a113df9b-e56e-49d2-b786-03d170d18dbc};
$spConn = new-object System.Data.OleDb.OleDbConnection($connString)
$spConn.open()
$qry = "UPDATE LIST SET Title = ‘Test1’ WHERE Title = ‘Title1’"
$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$spConn)
$cmd.ExecuteNonQuery()

#Delete
$connString = ‘Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://sharepoint.acme.com/IT/DBAdmin/;LIST={a113df9b-e56e-49d2-b786-03d170d18dbc};
$spConn = new-object System.Data.OleDb.OleDbConnection($connString)
$spConn.open()
$qry = "DELETE FROM LIST WHERE Title=’Test’"
$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$spConn)
$cmd.ExecuteNonQuery()

#Insert. NOTE: THIS DOES NOT WORK!
$connString = ‘Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://sharepoint.acme.com/IT/DBAdmin/;LIST={a113df9b-e56e-49d2-b786-03d170d18dbc};
$spConn = new-object System.Data.OleDb.OleDbConnection($connString)
$spConn.open()
$qry = "INSERT INTO LIST (Title) VALUES (‘Test3’)"
$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$spConn)
$cmd.ExecuteNonQuery()

 
For some reason the select, update and delete work perfectly, however the insert statement produces the following error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Cannot update ‘Title’; field not updateable.
At line:1 char:21
+ $cmd.ExecuteNonQuery( <<<< )
 
I’ve tried different variations of the insert command including adding a row to a DataTable and updating the DataAdapter and using OleDbcommandbuilder all with the same result. After a few hours of trying to make the insert work and web searches I noticed other people have reported the same issue. Lacking support for adding items to a list is big deal and greatly limits the applicability of using OleDB against a SharePoint list, nonetheless I may still use the select technique to return a list as a DataTable from Powershell.
 
 
 
Advertisements
This entry was posted in PowerShell. Bookmark the permalink.

8 Responses to Using OLEDB with SharePoint Lists

  1. Max says:

    Just wanted to thank you for an Excellent blog. I gave myself a 1% chance of getting bulksqlcopy to work on a discountasp.net server but your tips worked fabulously!

  2. Praful says:

    Thanks for the excellent post. This is exactly what I am trying to do. There is one particular thing that is not clear and I would be thankful if you could shed some light on it. I am trying to read a sharepoint list and when I execute the powershell script as myself, it works perfectly. But if I launch powershell (using run as) using a service account ( that has access to the list), after the da.fill() statement, I get a prompt asking me to enter the credentials for accessing the sharepoint site. When I enter the credentials for the service account, it works. This is preventing me from being able to schedule this as a job on a server 2003 machine since, it keeps prompting me for credentials. I don;t understand as to why is it behaving differently for the service account. Any ideas?Thanks!!-p

  3. Chad says:

    I noticed the same problem in my envrionment. Our web folks put SSL on our SharePoint servers a couple of months after I created the Powershell and like you, my script stopped working. I’m also prompted for usernae/password. I’ve tried to workaround the issue, but haven’t come up with a solution. I’ve noticed that once you’ve logged into a desktop session you are not prompted nor are you prompted after the second login prompt when using run as. You could run the script from locked desktop running under the service account, but that is not ideal. At this point I’m just running my script interactively rather than scheduled. If you find a solution, please post.

  4. Praful says:

    Hey Chad,Try this – Open Internet Explorer with your service account credentials(using Run as). Under Internet Options, on the security tab, click on Local intranet. Click on sites, advanced and add your sharepoint site to the list. Then click on Custom level… and in the User Authentication section, under Logon, select the Automatic logon with current user name and password option. Try scheduling your script now. (For some reason I couldn’t add sites to my Local intranet zone so I configured the Internet zone to use the current username and password. It seems to work). Let me know if it works or not!

  5. Chad says:

    Thanks for the info, I’ll try it.

  6. Chad says:

    Sorry I haven’t seen that issue. My usage of OLE DB to SharePoint has been limited. I do wonder if there is some kind of a timeout. It doesn’t appear this is settable option for a SharePoint connection string.

  7. nzwizard says:

    Hi
    I am taking taking your approch to get data out from sharepoint list but its not working

    #Select
    $connString =‘Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=http://;LIST={3237d325-7e6f-4029-a993-cdfba4ccaa4a};‘
    $spConn = new-object System.Data.OleDb.OleDbConnection($connString)
    $spConn.open()
    $qry=‘Select * from list’
    $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$spConn)
    $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
    $dt = new-object System.Data.dataTable
    $da.fill($dt) > $null
    $dt

    I get error in $da.fill($dt) > $null
    “exception calling fill with 1 argument(s)”

    Do you have any adea ?Any help appriciated.

    Thanks

    • cmille19 says:

      I would verifying just the connectivity and table/list name is correct before filling the dataset:
      #Select
      $connString =‘Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=http://;LIST={3237d325-7e6f-4029-a993-cdfba4ccaa4a};‘
      $spConn = new-object System.Data.OleDb.OleDbConnection($connString)
      $spConn.open()
      $spConn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)

      Does this work and are you getting “list” as one of the tables returned from GetOleDbSchemaTable?

Comments are closed.