locked
BDC data in WSS_Content database? RRS feed

  • Question

  • I know that all data from SP lists is stored in WSS_Content database in table dbo.AllUserData. But, in which table is data from external lists stored, I can't find it anywhere. I'm looking for bdc_identity of my external items.

    Problem:

    I have 2000 items limitation on BDC list (and I have no need for more), but I would like to access to bdc_identity od 3000-th item. All items have CustomId column with my ID, which is counter. SPQuery returns nothing for items that have CustomId larger than 2000. I tought that 2000 items limitation is applied only to rendering of BDC list and that I can access every BDC item through SPQuery, but it is not possible, so that is why I want to know where in WSS_Content db are BDC items strored.


    Wednesday, March 7, 2012 2:50 PM

Answers

  • External lists are like views on data in another data repository (like an Oracle database) and a SharePoint UI gets warapped around it, so the data stays where it is and isn't stored in the SharePoint content db at all. I'd advise you to read thru http://sharepointdragons.com/2012/02/13/creating-a-bcs-net-assembly-connector/ , that'll teach you a lot.

    Kind regards,
    Margriet Bruggeman

    Lois & Clark IT Services
    web site: http://www.loisandclark.eu
    blog: http://www.sharepointdragons.com



    Wednesday, March 7, 2012 2:58 PM
  • Hi Mario,

        Data from external lists is stored only in the external system. External data is brought into the SharePoint list at run time. Out of the box, SharePoint limits external lists to 2000 items like you said. You can change these limits using Powershell. Please check out this Blog for more information. http://blogs.msdn.com/b/bcs/archive/2010/02/16/bcs-powershell-introduction-and-throttle-management.aspx

      Best of luck.

    • Marked as answer by Mario Zagreb Wednesday, March 7, 2012 3:14 PM
    Wednesday, March 7, 2012 3:00 PM
  • The maximum threshold for a list is not 5000. The maximum for a List View is 5000. SharePoint Server 2010 can handle 30,000,000 items per list. You have the ability to create very large lists. SharePoint just prevents showing more than 5,000 items by default to prevent stress on the server. Here is a total list of information regarding the recommended limitations of SharePoint Server 2010.


    http://technet.microsoft.com/en-us/library/cc262787.aspx

    I think you'll learn a bit more by looking at link.


    • Edited by Beau Cameron Thursday, March 8, 2012 4:04 PM
    • Proposed as answer by Beau Cameron Thursday, March 8, 2012 7:39 PM
    • Marked as answer by Mario Zagreb Thursday, March 8, 2012 9:53 PM
    Thursday, March 8, 2012 4:03 PM

All replies

  • External lists are like views on data in another data repository (like an Oracle database) and a SharePoint UI gets warapped around it, so the data stays where it is and isn't stored in the SharePoint content db at all. I'd advise you to read thru http://sharepointdragons.com/2012/02/13/creating-a-bcs-net-assembly-connector/ , that'll teach you a lot.

    Kind regards,
    Margriet Bruggeman

    Lois & Clark IT Services
    web site: http://www.loisandclark.eu
    blog: http://www.sharepointdragons.com



    Wednesday, March 7, 2012 2:58 PM
  • Hi Mario,

        Data from external lists is stored only in the external system. External data is brought into the SharePoint list at run time. Out of the box, SharePoint limits external lists to 2000 items like you said. You can change these limits using Powershell. Please check out this Blog for more information. http://blogs.msdn.com/b/bcs/archive/2010/02/16/bcs-powershell-introduction-and-throttle-management.aspx

      Best of luck.

    • Marked as answer by Mario Zagreb Wednesday, March 7, 2012 3:14 PM
    Wednesday, March 7, 2012 3:00 PM
  • Thank you for the quick answers.

    What about bdc_identity? How can I get bdc_identity from first 2000 items and not the rest? Is it stored somewhere or is it created during the data fetch from external database, and if it is, by what algorithm?


    Wednesday, March 7, 2012 3:19 PM
  • Are you trying to take the BDC Identity and store it some where? What is your purpose of the Identity? For the SPListItem, the column is item["BdcIdentity"].

    Let me know if this helps. Thanks,

    Wednesday, March 7, 2012 3:38 PM
  • Are you trying to take the BDC Identity and store it some where? What is your purpose of the Identity? For the SPListItem, the column is item["BdcIdentity"].

    Let me know if this helps. Thanks,

    I can get to bdc identity through SPListItem, but only for first 2000 items. What about the others (concidering that I don't want to increase number of throtle)?

    I need bdc_identity to show certain item in display form. I have my customId in my database and I can fetch item from my database by that ID, but I need bdc id so that I can display item. On what rule is bdc_identity created? I can recognize my customid in display url, for example in bdc_identity "__bgc000230053003300" my CustomId is 253, but I can't figure it out how the rest of the id is created

    Wednesday, March 7, 2012 4:02 PM
  • I think you need to open your threshold so your list can hold more items if you are trying to retrieve these from your list in SharePoint.What is your consideration for not increasing the throttle?
    Wednesday, March 7, 2012 5:13 PM
  • I think you need to open your threshold so your list can hold more items if you are trying to retrieve these from your list in SharePoint.What is your consideration for not increasing the throttle?
    Decrease in performance. I haven't really tried it, but I suppose that that is the reason why throttle is so low in the first place
    Thursday, March 8, 2012 8:01 AM
  • I understand the concern. How many items are you planning on returning? Just remember, List Views Threshold is 5000 by default. So when changing your Throttle, you would want to keep it under 5000. Try testing it out and see if you see a big performance hit by changing your throttle from 2000 to 4000. Best of luck.
    Thursday, March 8, 2012 2:36 PM
  • I understand the concern. How many items are you planning on returning? Just remember, List Views Threshold is 5000 by default. So when changing your Throttle, you would want to keep it under 5000. Try testing it out and see if you see a big performance hit by changing your throttle from 2000 to 4000. Best of luck.

    Only 5000? That is useless. What is the point of BDC or Sharepoint in general if it can only work with such small amount of data?

    Currently, I have 250 000 rows in my db, and it will grow. It seems I will have to give up the BDC and make everything custom. Nevermind, I've learned something here :)

    Thanks for the answers!

    Thursday, March 8, 2012 3:55 PM
  • The maximum threshold for a list is not 5000. The maximum for a List View is 5000. SharePoint Server 2010 can handle 30,000,000 items per list. You have the ability to create very large lists. SharePoint just prevents showing more than 5,000 items by default to prevent stress on the server. Here is a total list of information regarding the recommended limitations of SharePoint Server 2010.


    http://technet.microsoft.com/en-us/library/cc262787.aspx

    I think you'll learn a bit more by looking at link.


    • Edited by Beau Cameron Thursday, March 8, 2012 4:04 PM
    • Proposed as answer by Beau Cameron Thursday, March 8, 2012 7:39 PM
    • Marked as answer by Mario Zagreb Thursday, March 8, 2012 9:53 PM
    Thursday, March 8, 2012 4:03 PM