SQL Server Performance

Table schema changed after cursor was delcared

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by altsek, Jan 10, 2005.

  1. altsek New Member

    I am running process in VBA that creates 6 million records in SQL server database. After creating little bit less than half I get error "could not complete cursor operation because table schema changed after the cursor was declared". Every time when I start where I left off I get this error after processing about half of the records. I noticed that available space of the database is 0 MB even “unrestricted file growth” is selected.
  2. satya Moderator

    What is the %age of the growth specified in the database properties?
    Also check for free space available on the disks where data and log files are placed.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. FrankKalis Moderator

  4. altsek New Member

    10%.
    We checked for free space on the disk right away. That was my first guess.

    This process creates records through ADO record set. There is a parent table, when record created then process updates parent record. Also there is error table that is updated through ADO record set in the same process.
  5. Adriaan New Member

    Any particular reason why this is a client-side procedure? If you are just creating new records based on specific data already within the database, I would recommend creating a server-side stored procedure that takes some parameter(s), and have the client app call the stored procedure.

    If there is additional data needed that is available in the client app only, see if you can upload that to a holding table in the database, and in your stored procedure retrieve the info from the holding table.

    Any particular reason why you are logging "errors"? You can probably predict those errors if you run some queries up front, like in "Does a record with the new info already exist?"
  6. altsek New Member

    The process itself is really complicated. There are a lot of validations and calculations on the client side before record is created. This process was running for almost a year now once a quarter, but we never had this kind of problem, neither that many records.
    The error table is related to data validation. It#%92s not just checking if records exist or not, it is series of calculations, comparison of totals and such.
  7. Adriaan New Member

    Not knowing the circumstances, but it should be possible to write a procedure that does the same validations and calculations on SQL Server. Perhaps you are using non-SQL data sources?
  8. altsek New Member

    I just wanted you to know that the problem was with optimization job scheduled during the night after we noticed that error occurs at the same time. It took us a wile to figure this out, but I am happy we finally did.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  9. rahmed New Member

    got any solution for this ???

    I am getting problem too

    Rizwan Ahmed
  10. satya Moderator

    May check any other processess are blocking this particular process or any scheduled jobs affecting the values.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. altsek New Member

    Now if we run this process we cancel all maintenance jobs for the database.
  12. bijesh New Member

    Hi All,
    Even I am getting this same error. I am using ASP page to query (Select statement) on SQL table.

    I get this error sometimes but not always.

    Thanks,
    Bijesh
  13. MohammedU New Member

    Generally you get this error when object is modified or dropped and recreated...

    Use cursors only when there is no solution using set based operation or while loop...

    When you are declaring the cursor try to use select statement temp table instead of permanent table...
    and do not modify the table schema inside the cursor..

    Can you please post the code..


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page