Post reply

Using the Export Wizard with MS Access issue

  • July 27, 2007 at 4:55 am

    #117796

    I'm trying to export data from some fairly small databases (approx 50Mb each, with no tables holding more than 4,000 records) to MS Access. Doing the same in SQL 2000 works first time with no problem. However if I try to export all tables from one of these databases using the SSMS Export Wizard I first get a warning message thus :

    'A large number of tables are selected for copying and the wizard may not be able to copy all the tables in a session'

    At this point I'd like to select the 'Optmize for many tables' checkbox, but unfortunately it's greyed out (although it is available if I opt to export to another SQL database).

    Continuing I now get the following error :

     Prepare for Execute (Error)

    Messages

    Error 0xc0202009: {8BBA8815-84DE-441D-8CC7-B2DE299F2C40}: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Unspecified error".

     (SQL Server Import and Export Wizard)

     

    Error 0xc020801c: Data Flow Task: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

     (SQL Server Import and Export Wizard)

     

    Error 0xc004701a: Data Flow Task: component "Destination 64 - jptsys_isel_dflt" (11642) failed the pre-execute phase and returned error code 0xC020801C.

     (SQL Server Import and Export Wizard)

     

    If I select a small number of tables then the export will work fine.

    I've tested with both Express and Development editions.

    My questions are :

    • Why is the 'Optimize for many tables' option greyed out when exporting to MS Access?
    • Why does exporting to Access work so well in SQL 2000 but fail so miserably in SQL 2005?

  • July 27, 2007 at 8:24 am

    #722891

    Ian the limit is 64 tables. you must select 64 tables at a time.

    This table jptsys_isel_dflt is the limit.

    I'm not sure if Microsoft fixed this in SP2.

    Alex S

  • July 27, 2007 at 8:45 am

    #722899

    Thanks for your reply Alex.

    Any idea why this limitation was introduced?

    regards

    ian

  • July 27, 2007 at 8:55 am

    #722908

    no idea.

    but i'm guessing it was fixed in SP2.

    Alex S

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply