AutoSys Workload Automation

  • 1.  Migration Error - Unable to insert row into database

    Posted Mar 28, 2019 03:14 PM

    Hello,

     

    I am migrating from v11.3 to v12.2.  When running the migration I received the following errors:

     

    Unable to insert table row into database: TableRow [type: ESP_PEOPLESOFT_JOB, values: [10761403, TAX7, SQR Report, TAX7, BATCH, null, PSNT, PDF, null, WEB, CENTRAL_PR_MGR,CENTRAL_PR_ACCT_AUDT,ITD_2ND_LEVEL_HELP_DESK, , QTRLYTAXRPTS, null, null, No, null, null, No, null, null, null, null]]

     

    com.ca.wa.engine.migration.table.TableWriteException: Unable to insert table row into database: TableRow [type: ESP_PEOPLESOFT_JOB, values: [10761403, TAX007, SQR Report, TAX007, HXBATCH, null, PSNT, PDF, null, WEB, CENTRAL_PR_MGR,CENTRAL_PR_ACCT_AUDT,ITD_2ND_LEVEL_HELP_DESK, , QTRLYTAXRPTS, null, null, No, null, null, No, null, null, null, null]]      at com.ca.wa.core.engine.migration.table.DbTableWriter.writeTableRow(DbTableWriter.java:83)      at com.ca.wa.engine.migration.table.TableMigrationStep.migrate(TableMigrationStep.java:102)      at com.ca.wa.engine.migration.table.TableMigrationDriver.migrate(TableMigrationDriver.java:207)      at com.ca.wa.engine.migration.Main.migrateTables(Main.java:551)      at com.ca.wa.engine.migration.Main.start(Main.java:336)      at com.ca.wa.engine.migration.Main.main(Main.java:244)     Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__H_PEOPLES__JOB_I__693CA210". The conflict occurred in database "STG122CUR_DB", table "dbo.H_GENERIC_JOB", column 'JOB_ID'.      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)      at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)      at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:548)      at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:479)      at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7344)      at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2713)      at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)      at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204)      at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:426)      at com.ca.wa.core.engine.migration.table.DBQueryAdapterBasedTableExportImportDetails.importOneRow(DBQueryAdapterBasedTableExportImportDetails.java:67)      at com.ca.wa.core.engine.migration.table.ESP_PEOPLESOFT_JOB_ExportImportDetails.importOneRow(ESP_PEOPLESOFT_JOB_ExportImportDetails.java:118)      at com.ca.wa.core.engine.migration.table.DbTableWriter.writeTableRow(DbTableWriter.java:69)

    How do I correct this issue? 

     

    ONLY 30 of 3662 ROWS CAME OVER with the MIGRATION..

     

    Thank you...


     



  • 2.  Re: Migration Error - Unable to insert row into database

    Broadcom Employee
    Posted Mar 29, 2019 09:49 AM

    Hi.

    See this error

    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__H_PEOPLES__JOB_I__693CA210". The conflict occurred in database "STG122CUR_DB", table "dbo.H_GENERIC_JOB", column 'JOB_ID'.      at 

    This means that the migration is trying to insert same data twice.  Are you re-running the migration as discussed here?

    If so, then you will have problems as the migration will try to insert the same data again.  Many tables have constraints that will prevent duplicate data.

    You will have to re-setup the R12, take backup of DB before doing the migration and then run migration.  If problems occur, restore the R12 DB to pre-migration and start again.

     

    HTH,

    Nitin Pande

    Broadcom



  • 3.  Re: Migration Error - Unable to insert row into database

    Posted Mar 29, 2019 10:21 AM

    Thanks!  This occurred after an uninstall (using the uninstall from the install directory) and reinstall.  I created a new database for the install and then migrated.  This is why I was confused about getting FK violations.  On the ESP_PEOPLSOFT_JOBS I dropped the table and copied the table from the 11.3 environment to the database.  What would you recommend?  I should not be getting FK errors with a NEW database and re-install.

     

    Thanks..



  • 4.  Re: Migration Error - Unable to insert row into database

    Broadcom Employee
    Posted Mar 29, 2019 12:46 PM

    Hi,

    If you have a brand new install then you should not be getting this error.  I would suggest to check your R12 DB table H_GENERIC_JOB for any records.  It should be empty or have very few records in it with a new install of the DE server.

    Also, your existing ESP_GENERIC_JOB table in R11.3 may have duplicate records, however this is extremely unlikely, unless constraints were manually removed on the table.

    Check this on 11.3 DB

     

    SELECT * FROM dbo.ESP_GENERIC_JOB where job_id = '10761403'

     

    If the above gives you more than one results, then you will not be able to move the history data.  Each 'job_id" can only have one row.  It also means that your 11.3 DB has been changed after the install, and you should then not move the historical data at all.

     

    HTH,

    Nitin Pande

    Broadcom



  • 5.  Re: Migration Error - Unable to insert row into database

    Posted Mar 29, 2019 12:54 PM

    There are 68 rows in the ESP_GENERIC_JOB table in v12 database.  The query supplied only returns one row. 

     

    Also, is it okay I copied the Peoplesoft table from the v11.3 environment to the v12 environment?

     

    Thanks...



  • 6.  Re: Migration Error - Unable to insert row into database

    Broadcom Employee
    Posted Mar 29, 2019 02:52 PM

    Hi,

    You should not copy any data or tables manually.  This will result in data inconsistencies and DE server to crash.

     

    Thank you,

    Nitin Pande

    Broadcom



  • 7.  Re: Migration Error - Unable to insert row into database

    Posted Mar 29, 2019 03:15 PM

    Should I redo the install/migration?  As I said, I do not know why this occurred.

     

    Thanks for your help!



  • 8.  Re: Migration Error - Unable to insert row into database

    Broadcom Employee
    Posted Mar 31, 2019 05:43 AM

    Yes, reinstall and migrate.

     

    Thank you,

    Nitin Pande

    Broadcom



  • 9.  Re: Migration Error - Unable to insert row into database

    Posted Apr 01, 2019 11:13 AM

    Thank you!  I use the uninstaller from the installation directory to remove as opposed to removing via control panel.  Is this correct?