locked
Query to reterive milion of records RRS feed

  • Question

  • Hello All,

    I am DW guy don't know programming ,

    I am calling CRM API in ssis script task in which I have to fetch around twenty lakh records round about , can any body let me know how I can achive this :  my code is as

    #region ProductBase fetch
                col = new ColumnSet();
                col.Columns = new string[] { "name", "productnumber" };
                query = new QueryExpression();
                query.EntityName = "product";
                query.ColumnSet = col;
                var ProductBaseResult = _serviceProxy.RetrieveMultiple(query);

                var test4 = ProductBaseResult.Entities.Length;
                #endregion

    but I came to know qeryexpresion has limit upto 5000 records. so how can I fetch 2000000 records.

    and insert it in sql table



    blog:My Blog/

    Hope this will help you !!!
    Sanjeewan

    Wednesday, May 24, 2017 12:39 PM

Answers

All replies

  • Hello,

    See the following thread

    https://social.microsoft.com/Forums/en-US/80c1eb70-81db-49d4-8dc8-4cd04f4ae37e/retrive-more-than-5000-records-from-crm-2011-using-web-service-and-c?forum=crmdevelopment


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, May 24, 2017 1:11 PM
  • You can meet your requirement using this article
    https://social.microsoft.com/Forums/en-US/48d616d4-7774-4646-b547-54eb4d01900d/query-to-reterive-milion-of-records?referrer=http://social.microsoft.com/Forums/en-US/48d616d4-7774-4646-b547-54eb4d01900d/query-to-reterive-milion-of-records?referrer=http://social.microsoft.com/Forums/en-US/48d616d4-7774-4646-b547-54eb4d01900d/query-to-reterive-milion-of-records?forum=crmdevelopment


    Regards Faisal

    Wednesday, May 24, 2017 1:56 PM
  • Hi,

    I assume you are working On-Premise (as you can retrieve 2 Mil records which is a setting you can change on the on premise database)

    That being the case you can simply access the CRM SQL Database directly and retrieve your information using the Views. (the only accepted way of access the CRM data "directly")

    However if you are online there are 2 options :

    1) You can use the above query as answered in the other post. ie page the results to get everything

    2) Request a backup from Microsoft, import it into an azure database and connect to with SSIS and do your thing.

    Naturally option 2 would be used for your initial bulk load and then get your updates via the api as and when they happen.

    regards,

    Don

    Wednesday, May 24, 2017 10:37 PM