locked
Not able to search my Sharepoint List column names in Sql Server Database RRS feed

  • Question

  • Hi Folks,

    I am using Sharepoint 2007 with Sql Server 2008.

    Unfortunately, I need to write a query against Sharepoint List for my client requirement. Looking in AllList table's tp_Field, I got all my List column names mapped with field name in DB. However, I am not able to find couple of my List columns in AllUserData table in database.

    Let me give more info. on my List. My list has 50+ columns with 20+ datatime columns. I am searching for 20+ datetime columns in AllUserData tabl. But, i am not able to search it. While googling, i got to know that AllUserData table has 64nvarchar, 8 datetime columns etc....

    So, my question here is where my other datetime columns in Sharepoint are saved which i cannot search in AllUserData table ? If I can see data in Sharepoint List when i open List, it means that Sharepoint is fetching from somewhere ?

    Pls advice.


    Thanks, Chintan


    • Edited by Chintan123 Monday, October 17, 2011 11:56 AM
    Monday, October 17, 2011 10:42 AM

Answers

  • Hi Chintan123,

    First, it is not recommended to operate with database directly in this way, you may consider using SharePoint API to meet the requirements.

    AllUserData table in site content database is used to store all items in any list of the SharePoint site, as we can see in the AllUserData table schema, the table has 64 nvarchar columns used to store text columns, 16ints, 12 floats, 8 datetime field.

    If the DateTime Field is more than 8, then when new an item in the list, there will be more than one record in the AllUserData table for the list item, for example, I have 10 DateTime fields in the list, there will be two records in the table for the new list item, in this way, we can have as many as DateTime fields in the list, you can check whether it is the issue.

    Thanks,
    Qiao



    Thursday, October 20, 2011 8:53 AM
    Moderator

All replies

  • I do not believe that a list is stored in SS as a table.  It's data resides in the content database, but I don't know exactly how. 

    Here's a blog (which I didn't really read, but maybe it will guide you in the right direction)

    How to pull Sharepoint list data from SQL Server

     


    Steve Clark, MCTS | Twin-Soft.com
    Monday, October 17, 2011 3:17 PM
  • Hi Chintan,

    This will store the record in Same Table i.e.{All User Datta}. but with different row & with Same ID.

    ~Datta

     

     

    Wednesday, October 19, 2011 3:13 AM
  • Thanks SClark_MCTS70-542 for your reply. Your URL doesn't satisfy my requirement. Pls advice.

    Thanks Patil for your reply. Yes, our data is store in AllUserData but there are only 8 datetime fields exists in this table and my sharepoint list has 20 datetime columns. So where other datetime values are store if it exceed 8th datetime field in AllUserData table. Pls. advice.


    Thanks, Chintan
    Wednesday, October 19, 2011 12:34 PM
  • Hi Chintan123,

    First, it is not recommended to operate with database directly in this way, you may consider using SharePoint API to meet the requirements.

    AllUserData table in site content database is used to store all items in any list of the SharePoint site, as we can see in the AllUserData table schema, the table has 64 nvarchar columns used to store text columns, 16ints, 12 floats, 8 datetime field.

    If the DateTime Field is more than 8, then when new an item in the list, there will be more than one record in the AllUserData table for the list item, for example, I have 10 DateTime fields in the list, there will be two records in the table for the new list item, in this way, we can have as many as DateTime fields in the list, you can check whether it is the issue.

    Thanks,
    Qiao



    Thursday, October 20, 2011 8:53 AM
    Moderator
  • Hi Qiao,

    Thanks a lot for your solution. Yes, they are store in multiple rows. We can distinguish them with tp_RowOrdinal column.


    Thanks, Chintan
    Friday, November 4, 2011 9:59 AM