locked
Maximum number of records exported to Excel RRS feed

  • Question

  • Hi,

    currently with CRM 4, the default numbert of records you can export to an Excel Static Spreadsheet are 10,000, however we have a little over 200,000, which we are exporting in 10,000 lots to setup a quarterly report.....

    .....So, our intention is to change the value to 250,000, which would be done overnight, and only by 1 persion, and only every quarter..my questions are 1: is that too large for CRM and 2: What could be the possible implications/risks in doing this?


    Cheers and regards Rossco
    Wednesday, March 31, 2010 10:40 PM

Answers

  • Dynamics workbooks still have the 10k limit but you can remove it from the SQL query by right-clicking on the cell A2 and selecting Edit Query..., then clicking the SQL button in the Query Editor window. As Aaron says, for infrequent exports, a lot more efficient than reg-hacking.

    Leon Tribe
    Want to hear me talk about all things CRM? Check out my blog 
    http://leontribe.blogspot.com/ 
    or hear me tweet @leontribe


    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Thursday, April 1, 2010 6:16 PM
    • Marked as answer by Jim Glass Jr Thursday, April 1, 2010 8:20 PM
    Thursday, April 1, 2010 6:15 PM

All replies

  • One big factor to consider is the number of columns you are exporting. I have found that to be an issue on even exporting 10,000 records when all attributes were selected to be exported.

    Personally, given the volume you have I would consider using the SDK to retrieve them and export to a file.


    Jerry http://www.crminnovation.com
    Wednesday, March 31, 2010 10:48 PM
  • Use excel 2007 (1m lines) and export to excel via a dynamic workbook (no 10,000 record limit) then copy and paste special values to another workbook.  Saves you 247 steps ...
    Thursday, April 1, 2010 2:03 AM
  • Please check the following URL for the same. Hope this helps.

    Thursday, April 1, 2010 8:08 AM
    Answerer
  • Dynamics workbooks still have the 10k limit but you can remove it from the SQL query by right-clicking on the cell A2 and selecting Edit Query..., then clicking the SQL button in the Query Editor window. As Aaron says, for infrequent exports, a lot more efficient than reg-hacking.

    Leon Tribe
    Want to hear me talk about all things CRM? Check out my blog 
    http://leontribe.blogspot.com/ 
    or hear me tweet @leontribe


    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Thursday, April 1, 2010 6:16 PM
    • Marked as answer by Jim Glass Jr Thursday, April 1, 2010 8:20 PM
    Thursday, April 1, 2010 6:15 PM
  • Thanks all for your help....

     

    Much appreciated :-)


    Cheers and regards Rossco
    Saturday, April 3, 2010 8:36 PM
  • Hi,

    I have tried editing the max records to excel options but can't seem to get it to take......changed to 50000 but still only exports 10000 to excel static sheet....any ideas aside from contact MS Support?


    Cheers and regards Rossco
    Wednesday, May 26, 2010 6:45 AM
  • have you restarted your CRM server after change, also remove all temp internet files from user PC because this value is cached..

     

    Wednesday, May 26, 2010 7:54 AM
    Answerer
  • have you restarted your CRM server after change, also remove all temp internet files from user PC because this value is cached..

     

    Doh! No i didn't, does that mean the SQL server also needs restarting?
    Cheers and regards Rossco
    Wednesday, May 26, 2010 8:49 AM
  • No, I think restarting CRM web site (iisreset) should be enough for this change to reflect through to the client side.

     

    Wednesday, May 26, 2010 9:21 AM
    Answerer
  • No, I think restarting CRM web site (iisreset) should be enough for this change to reflect through to the client side.

     


    OK,

    thanks for that.....I will give that a try tonight when users are not on the system and report back...


    Cheers and regards Rossco
    Wednesday, May 26, 2010 8:09 PM
  • No, I think restarting CRM web site (iisreset) should be enough for this change to reflect through to the client side.

     


    Hi again,

    we did an (iisreset) and it still did not work.....am I changing the value in the correct place...?...as follows..[SQL Server]-[organisationbase]-[MaxRecordsForExportToExcel]-[default value for binding]-[((10000))]?

    Frogot to add, right click/modify option.

    or am I in the wrong place?

     


    Cheers and regards Rossco
    • Edited by GrenadaV Thursday, May 27, 2010 8:50 PM More information
    Thursday, May 27, 2010 8:46 PM
  • Please make sure you are updating correct database..database name wound be your <<orgname>>_MSCRM.

    Also what's the values you are setting now? is it CRM 3.0 or CRM 4.0?...after updating run following query to check.

      select MaxRecordsForExportToExcel from OrganizationBase

     

    Thursday, May 27, 2010 9:02 PM
    Answerer
  • Please make sure you are updating correct database..database name wound be your <<orgname>>_MSCRM.

    Also what's the values you are setting now? is it CRM 3.0 or CRM 4.0?...after updating run following query to check.

      select MaxRecordsForExportToExcel from OrganizationBase

     


    Hi,

    thanks for getting back so soon, yep, organisationbase ITOMICTest_MSCRM on our dba01 server and after running your query it came back with a result of 10000 but I have set that option "default value for binding ((10000))" to 50000 and it is CRM 4.0....


    Cheers and regards Rossco
    Thursday, May 27, 2010 9:18 PM
  • if query comes back with result 10000 this means it is not updated in the database correctly..how are you updating the values in this table row..

    try running following query

              Update OrganizationBase

              Set MaxRecordsForExportToExcel = 50000

               where OrganizationId = <<Your Org Id>>

     

    Hope this helps..

     

     

    Thursday, May 27, 2010 9:28 PM
    Answerer
  • Thanks for all your help MayankP, really appreciated

    Am I changing in in the correct place...?

    Also, ran that query but getting the following syntax error...

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '<'.

    Sorry, forgot to put in original query that the syntax error is referring to....

    Update OrganizationBase
    Set MaxRecordsForExportToExcel = 50000
    Where OrganizationId = <<ITOMICTest_MSCRM>>

     


    Cheers and regards Rossco
    • Edited by GrenadaV Thursday, May 27, 2010 10:53 PM More info
    Thursday, May 27, 2010 10:20 PM
  • Okay , Just run following query with out condition

               Update OrganizationBase

              Set MaxRecordsForExportToExcel = 50000

     

    let me know how you get on..

     

    Friday, May 28, 2010 8:21 AM
    Answerer
  • Okay , Just run following query with out condition

               Update OrganizationBase

              Set MaxRecordsForExportToExcel = 50000

     

    let me know how you get on..

     


    Hi again,

    Ran that query now and came back with following answer...

    (1 row(s) affected)

    So I take it that this has worked?, will do an IIS Reset tonight to see if it has worked..and will report back. Thanks :-)


    Cheers and regards Rossco
    Sunday, May 30, 2010 8:58 PM
  • Yes, it should work fine now..let me know how you get on..
    Monday, May 31, 2010 10:54 AM
    Answerer
  • Hi MayankP,

    YAY!! it worked....:-)

    Thanks very much for helping me out with this, that is brilliant :-)


    Cheers and regards Rossco
    Monday, May 31, 2010 8:34 PM
  • Hi,

    I'm a new user of CRM 2011 and want to accomplish the same thing (making the limit higher for export). Is this supported by Microsoft?

    Thursday, October 10, 2013 9:22 AM