none
Import and Export data

    Question

  • hi all.

    i'm using sql server 2000, i want to move my database into sql server 2008.

    i use import and export data, process was success, but in all table in sql 2008 there are no primary key, no default value for each fields of tables, no database diagram,  please help

     

    thanks

     

    Tuesday, May 04, 2010 5:16 AM

Answers

  • As per my knowledge ,IMPORT EXPORT Wizard Doest not import  the data if table has Identiy columns.

     Few Options i consider

    1. If  tables number is small  try to remove the Identity col in the Destination tables  & keep Identity after you done importing .

    2.Use the Generate Scripts Option , Script for server version SQLSERVER 2008 Level 100, keep Script Data option true which scripts data too.

    3.Take a Back UP Copy & restore it on 2008

    • Marked as answer by Mr Chang Friday, May 07, 2010 6:08 AM
    Thursday, May 06, 2010 8:19 PM

All replies

  • You can try migrating the schema first before using import/export for data migration. Script the database schema from SQL Server 2000 and recreate the schema on SQL Server 2008 (an empty database) before you do the import/export of data.

    Wednesday, May 05, 2010 12:54 AM
  • when i run the query in sql 2008, scrip was successful, there are primary key in all tables.

    but when import data from sql 2000, there are an error message  :

     

     Validating (Error)
    Messages
    Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "id".
     (SQL Server Import and Export Wizard)
     
    Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
     (SQL Server Import and Export Wizard)
     
    Error 0xc004706b: Data Flow Task 1: "component "Destination - Accesslog" (34)" failed validation and returned validation status "VS_ISBROKEN".
     (SQL Server Import and Export Wizard)
     
    Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
     (SQL Server Import and Export Wizard)

    Wednesday, May 05, 2010 3:02 AM
  • As per my knowledge ,IMPORT EXPORT Wizard Doest not import  the data if table has Identiy columns.

     Few Options i consider

    1. If  tables number is small  try to remove the Identity col in the Destination tables  & keep Identity after you done importing .

    2.Use the Generate Scripts Option , Script for server version SQLSERVER 2008 Level 100, keep Script Data option true which scripts data too.

    3.Take a Back UP Copy & restore it on 2008

    • Marked as answer by Mr Chang Friday, May 07, 2010 6:08 AM
    Thursday, May 06, 2010 8:19 PM
  • Thank you
    Need Help
    Friday, May 07, 2010 2:00 PM
  • Yes, it’s there, if you run IMPORT/EXRORT wizard in SQL 2008/2005 you will find a button called EDIT Mapping where you can find the option Enable Identity insert ON.

    I can say this is the beauty feature of MS Import/Export wizard.

    You have multiple options in IMPORT/EXPORT wizard like delete old rows and add new record directly so you no need to truncate the table and insert it.

    I have personal experience when I was running out of time to resolve the Collation issue and this Wizard help me a lot to complete my task like a miracle and more over huge amount of data migrated successfully.

    Thanks,

    Sandeep

    • Proposed as answer by lucinpub Wednesday, January 25, 2012 8:34 PM
    Monday, May 10, 2010 10:32 AM
  • Thanks @Sandeep, that was the solution for me.
    • Proposed as answer by lucinpub Wednesday, January 25, 2012 8:33 PM
    • Unproposed as answer by lucinpub Wednesday, January 25, 2012 8:34 PM
    Wednesday, January 25, 2012 8:32 PM
  • Hi

    I installed a web application named: Nopcommerce 2.6 on my local SQL server 2012 as its database and Now I am trying to publish the local SQL server onto a hosted SQL server on my hosting provider account and I did change the edit mapping and enabled Identity insert on, however I still get the following Error message, I sincerely appreciate it if you could help me because It has really taken a lot of time for me to develop this local database and not being able to publish it will be a disaster:

    Copying to [dbo].[ActivityLog] (Error)
    Messages
    Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "Destination 4 - BackInStockSubscription" has started.
     (SQL Server Import and Export Wizard)
     
    Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "Destination 3 - Affiliate" has started.
     (SQL Server Import and Export Wizard)
     
    Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion  in "Destination 4 - BackInStockSubscription" has ended.
     (SQL Server Import and Export Wizard)
     
    Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion  in "Destination 3 - Affiliate" has ended.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK__Address__3214EC0707020F21'. Cannot insert duplicate key in object 'dbo.Address'. The duplicate key value is (1).".
     (SQL Server Import and Export Wizard)
     
    Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Destination 2 - Address.Inputs[Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Destination 2 - Address.Inputs[Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination 2 - Address" (169) failed with error code 0xC0209029 while processing input "Destination Input" (182). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK__Activity__3214EC0703317E3D'. Cannot insert duplicate key in object 'dbo.ActivityLogType'. The duplicate key value is (1).".
     (SQL Server Import and Export Wizard)
     
    Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Destination 1 - ActivityLogType.Inputs[Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Destination 1 - ActivityLogType.Inputs[Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination 1 - ActivityLogType" (86) failed with error code 0xC0209029 while processing input "Destination Input" (99). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
     

    Saturday, July 28, 2012 6:32 PM
  • Thanks @Sandeep X 2!

    I am knee deep in a migration to AWS RDS from SQL 2008 R2 and you saved me a buch of time.

    In case anyone else in a similar situation finds this, my process worked out to...

    1) Manually create the databases

    2) Use Generate Scripts on the source databases to create the table schema w/ script indexes and generate scripts for dependent objects set to True 

    3) Use the Import / Export wizard on the source databases with the Drop Existing Records on Destination and Identity Insert boxes firmly checked

    4) Save the dtsx packages to the file system so they can be run once for testing and again just prior to go live

    Dave

    Tuesday, September 18, 2012 2:52 AM