SQL Server Performance Forum – Threads Archive

Table schema changed after cursor was delcared

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.

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.

Also, what does your creation process in VBA look like? —
Frank
http://www.insidesql.de

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.

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?"

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.

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?

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=’

‘ />]

got any solution for this ??? I am getting problem too Rizwan Ahmed

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.

Now if we run this process we cancel all maintenance jobs for the database.

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

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.

]]>