Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    22

    Unanswered: Error while inserting data after renaming of the table through JDBC

    Hello All,

    We have very old schema (tables) with fairly large amount of data in each table , and we want to enhance complete schema and build views on top of the tables.i.e our old table name is xyz,we want to upgrade this table with new name xyz_tbl and view on top of this table is xyz ( same as old table names).

    While upgrading the schema instead of creating new tables and pushing existing data into new tables,I renamed the existing table to new table name and created view for each table same as old table name.


    old table name: xyz
    new table name: xyz_tbl
    view name: xyz

    I ran following commands to rename and recompile all the tables
    sp_rename xyz,xyz_tbl
    sp_recompile xyz_tbl

    and created view on top of the existing table called as xyz (same as old table name).

    I also created all the stored procedures and triggers to point to new table names.

    It seems every thing looks okay to me from isql, but while inserting and updating data into the table through JDBC using stored procedures it is creating the problem.It is not inserting any data into the table.

    I saw following error message in ASE server(12.5.2) log file.

    00:00000:00138:2006/04/26 17:47:08.70 kernel current process (0x42fa00a7) infected with 11
    00:00000:00138:2006/04/26 17:47:08.70 kernel ************************************
    00:00000:00138:2006/04/26 17:47:08.70 kernel SQL causing error : <procedure name>
    00:00000:00138:2006/04/26 17:47:08.71 kernel ************************************
    00:00000:00138:2006/04/26 17:47:08.71 server SQL Text: <procedure name>
    00:00000:00138:2006/04/26 17:47:08.71 kernel curdb = 10 tempdb = 2 pstat = 0x10108
    00:00000:00138:2006/04/26 17:47:08.71 kernel lasterror = 0 preverror = 0 transtate = 0
    00:00000:00138:2006/04/26 17:47:08.71 kernel curcmd = 195 program =
    00:00000:00138:2006/04/26 17:47:08.74 kernel pc 0x87e2019 ucbacktrace+0x89(0x0,0x1,0x930b1a4,0x42fa00a7,0x0)
    00:00000:00138:2006/04/26 17:47:08.75 kernel pc 0x81af795 terminate_process+0xc09(0x0,0xffffffff,0x930b1a4,0 x0,0x69d012c4)
    00:00000:00138:2006/04/26 17:47:08.75 kernel pc 0x88014f0 kisignal+0xf0(0xb,0x68e3c000,0x68e3c080,0xb,0x0)

    I could have created new tables and loaded data into the tables,but we have about 20 tables with large amount of data,and I have to do this at atleast five places since we are using same schema in all the places.

    am I missing some thing?Please respond.

    Thanks in advance.
    bsr

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    There are possibly other dependent objects on the original table. Run

    exec sp_depends 'xyz'

    From the list, I would drop and re-create all the pre-compiled objects.

  3. #3
    Join Date
    Dec 2005
    Posts
    22
    Thanks a lot for your immediate response,there are some triggers pointing to old tables xyzs ,as you suggested I recreated the triggers and other objects to point to new table names(xyz_tbl) and it is working fine.

    Will be there any data inconsistency issues with this process ( renaming of the tables instead of dropping and recreating of the tables)?


    Thanks a lot for your valuable time.
    bsr.

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    No lost data, other than what might have been lost due to the stack trace you reported in your initial message (i.e. data that didn't get inserted/updated in the command that was aborted).

    Michael

  5. #5
    Join Date
    Dec 2005
    Posts
    22
    Thanks Michael.Will These renamed objects create any problem in future?.I am assuming as long as all the procedures and triggers point to renamed table names we will be okay.

    Thanks in advance
    Last edited by bsr; 05-04-06 at 23:15.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •