Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Makati City, Philippines
    Posts
    2

    Unanswered: MS SQL 2000 Temporary table data truncated and loop variable set to null

    I hope some guru MS SQL guys could help me out on these dilemna I'm
    experiencing, The behaviour is quite abnormal. Considering the tempdb ssize was already increased to 500 MB. And the server I'm using is a P4 pentium type with 128MB RAM and 30 GIG HD with Windows 2000 advanced server on it.

    The temporary table data truncates upon reaching second up to 4th row while loop and the loop variables are set to null values. These things happen inside triggers with 2 nested stored procedures. Everytime the loop comes back from the nested stored procedures the temporary table rows are truncated in the memory and the loop variable was set to null. So what happened was, instead of updating several rows the trigger updated only a single row.

    I really hope a MS SQL Guru out there could give me a good samritans
    deed. Thanks and more power to DBForums.

    Regards,
    Reginald Gonzales
    IT Consultant
    Makati City, Philippines

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Could you post the triggers an SP?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2002
    Location
    Makati City, Philippines
    Posts
    2
    Originally posted by Paul Young
    Could you post the triggers an SP?
    Ans: Thanks for the reply, actually we're 5 guys doing
    an inventory system in VB6/MS SQL2000 - n-tier architecture,
    the triggers after updates of a particular status has within
    it 'EXEC SPROCS' statements. Updating several rows inside
    SQL Analyzer updates everything. Like the example below:

    **(All 4 rows were updated)

    BEGIN TRANSACTION
    UPDATE dbo.tblTrxUniversalTrxDtl
    SET trx_dtl_status_id = 'APRVD'
    WHERE trx_hdr_id = 'RRH020000287' AND
    trx_dtl_id = '000000000001'
    COMMIT TRANSACTION
    BEGIN TRANSACTION
    UPDATE dbo.tblTrxUniversalTrxDtl
    SET trx_dtl_status_id = 'APRVD'
    WHERE trx_hdr_id = 'RRH020000287' AND
    trx_dtl_id = '000000000002'
    COMMIT TRANSACTION
    BEGIN TRANSACTION
    UPDATE dbo.tblTrxUniversalTrxDtl
    SET trx_dtl_status_id = 'APRVD'
    WHERE trx_hdr_id = 'RRH020000287' AND
    trx_dtl_id = '000000000003'
    COMMIT TRANSACTION
    BEGIN TRANSACTION
    UPDATE dbo.tblTrxUniversalTrxDtl
    SET trx_dtl_status_id = 'APRVD'
    WHERE trx_hdr_id = 'RRH020000287' AND
    trx_dtl_id = '000000000004'
    COMMIT TRANSACTION

    **But IF you do it like this :
    **(only one row was updated)

    BEGIN TRANSACTION
    UPDATE dbo.tblTrxUniversalTrxDtl
    SET trx_dtl_status_id = 'APRVD'
    WHERE trx_hdr_id = 'RRH020000287'
    COMMIT TRANSACTION

    Is there a particular undocumented command to
    increase the cache or buffer size of stored procedures and
    triggers?

    Thanks and more power to you Paul and to your office and
    as well as to DBForums.

    Regards,
    Reginald Gonzales
    IT Consultant
    Makati City
    Phillipines

Posting Permissions

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