Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2001
    Posts
    32

    Unhappy Unanswered: Regarding SQL1476N The current transaction was rolled back because of error "-1477".

    Hello everybody

    Problem:

    I have a created a table wiht NOT LOGGED INITIALLY option.
    I have a SQL stored procedure which will alter the table with ACTIVATE NOT LOGGED INITIALLY and does insertion.and COMMIT after inserting all the records.But the exception "SQL1476N The current transaction was rolled back because of error "-1477"." is getting raised and No record is getting inserted into the table.

    Question:
    1) Can't ALTER table with NOT LOGGED INITIALLY from stored procedure?
    2) Why the table is becoming in accessible after calling the stored procedure?
    3) How to rectify the above problem?

    Please help me...........

    Thanks,
    Thangam
    Last edited by thangam; 10-10-02 at 01:44.

  2. #2
    Join Date
    Jul 2002
    Posts
    19
    hi

    The cause of error is clear: A table was being created with the NOT LOGGED INITIALLY option. During the same unit of work, an error occurred. This unit of work is rolled back, and any table that was created in it is dropped. This is what the code description says.

    As a rule of thumb do not provide any DDLs (ALTER in your case) as part of the application programs (Stored Procedures). I dont understand the need for altering the table and commiting everytime.. Is that absolutely necessary

  3. #3
    Join Date
    Jul 2001
    Posts
    32
    Thanks Gajendran.I want achieve the functionality TRUNCATE in my Stored procedure.I don't want to use DELETE & COMMIT or I don't want to load with empty flat file.

    This is my sample code

    CREATE PROCEDURE SAMPLE( OUT P_STATUS SMALLINT,OUT P_MSG_TEXT VARCHAR(100))
    LANGUAGE SQL
    BEGIN
    ECLARE L_ALTER VARCHAR(200) DEFAULT 'ALTER TABLE SAMPLE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    SET P_STATUS=1;
    SET P_MSG_TEXT=SQLCODE ||' - '||SQLSTATE;
    END;

    SET P_STATUS=0;
    -- Truncate table sample --

    EXECUTE IMMEIDATE L_ALTER;
    WHILE (_COUNT <10000 )
    DO
    INSERT INTO SAMPLE VALUES(L_COUNT);
    SET L_COUNT=L_COUNT+1;
    END WHILE;
    COMMIT;
    END;

    How do I truncate with out altering the table? Is there any way of doing it? Please guide me.

  4. #4
    Join Date
    Jul 2002
    Posts
    19
    Hi thangam
    Even without an Empty flat file you can try this

    import from nul of del replace into TABLENAME

    this will do the trick of truncating your table. You can avoid using the Alter Table.
    Hope this helps
    Regards
    C.Gajendran

  5. #5
    Join Date
    Jul 2001
    Posts
    32
    Thanks Gajendran.The problem is ,I can not execute import from stored procedure.I have to call external proceudre in sql stored procedure to invoke import utility which I don't want to do.
    Thanks,
    Thangam

Posting Permissions

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