Problem with Index Tuning Wizard

When running the Index Tuning Wizard (ITW) on our SQL Server we got a
error message : 'Could not complete cursor operation because the table
schema changed after the cursor was declared'
The ITW was used only to advice new indexes. I know that it creates
hypothetical indexes in the sysindexes table, but what are the changes
in the user-tables ?
How do you use the ITW without interfering with the real
schema-definitions ?
thanks,
RobbertHi,
Before using the ITW, you should get a load of the queries
you are running. You can capture the events in profiler
and then use that load file for the ITW to use.
hth
DeeJay
>--Original Message--
>When running the Index Tuning Wizard (ITW) on our SQL
Server we got a
>error message : 'Could not complete cursor operation
because the table
>schema changed after the cursor was declared'
>The ITW was used only to advice new indexes. I know that
it creates
>hypothetical indexes in the sysindexes table, but what
are the changes
>in the user-tables ?
>How do you use the ITW without interfering with the real
>schema-definitions ?
>thanks,
>Robbert
>.
>|||My theory on this is while you were running the ITW
someone in your tream made a change to the structure a
table, which caused it to get an error.
I have run ITW a number of times using our Production
Database, with no problems.
Peter
"We are all worms. But I believe that I am a glow-worm."
Winston Churchill
>--Original Message--
>When running the Index Tuning Wizard (ITW) on our SQL
Server we got a
>error message : 'Could not complete cursor operation
because the table
>schema changed after the cursor was declared'
>The ITW was used only to advice new indexes. I know that
it creates
>hypothetical indexes in the sysindexes table, but what
are the changes
>in the user-tables ?
>How do you use the ITW without interfering with the real
>schema-definitions ?
>thanks,
>Robbert
>.
>

Problem with FETCH LAST

Hello everyone, hope someone can help me with this.

I have a SQL stored procedure that inserts a record into a table,
creates a cursor to fetch the last record that was added to get the
unique key that was created and then writes that and other info to a
separate table. This procedure was working fine at our ISP under NT 4
and SQL 7.

We recently moved to another ISP on servers that are windows 2000 and
SQL 2000. Now this code is going kerplooey. It actually worked fine
in the staging area but now that it was moved into production, it is
not working. also wanted to mention that the production database was
restored from a backup. below is the code.

the first time this is run it is ok, for example the transaction
number is 1. the next time it is run, a new record is created in the
sweep results with a transaction number of 2. but for some reason,
when i declare the cursor to fetch the last record, it goes back to
the transaction number 1 record. so the counts from transaction 1
don't match counts from transaction 2 and the next step has an error
condition and doesn't work.

thanks in advance for any help you can provide

Ann Williams

-- update the sweep results table
INSERT tbl_sweepresults (del_wrkfeedback_count,
updnull_feedback_count, swp_feedback_count,
swp_count_error, del_error, updnull_error, swp_error, init_error,
sweep_date)
VALUES (@.var_del_wrkfeedback_count, @.var_updnull_feedback_count,
@.var_swp_feedback_count,
@.var_swp_count_error, @.var_del_error, @.var_updnull_error,
@.var_swp_error, @.var_init_error, GETDATE())
-- create cursor
DECLARE tbl_sweepresults_cursor SCROLL CURSOR FOR
SELECT transaction_no, sweep_date, init_error, updnull_feedback_count
FROM tbl_sweepresults
OPEN tbl_sweepresults_cursor
-- get transaction number, sweep date, init error, feedback sweep
count and pass to tbl_currentTrans for OPAL comparison
FETCH LAST FROM tbl_sweepresults_cursor INTO @.var_transaction_no,
@.var_sweep_date, @.var_init_error, @.var_swp_countzero
DELETE tbl_currentTrans
INSERT tbl_currentTrans (current_transaction_no, current_sweep_date,
current_init_error, current_swp_countzero)
VALUES (@.var_transaction_no, @.var_sweep_date, @.var_init_error,
@.var_swp_countzero)
-- close the cursor
CLOSE tbl_sweepresults_cursor
DEALLOCATE tbl_sweepresults_cursorIs the value you are trying to retrieve an IDENTITY column? If so,
SCOPE_IDENTITY() is what you need. It returns the last inserted identity
value.

Since your cursor declaration doesn't include an ORDER BY clause you've been
lucky that it ever gave a meaningful result. FETCH LAST will just return an
indeterminate row from the table. Moving to another system (perhaps one with
more read-ahead cacheing) has shown up this defficiency which relied on the
engine always returning the last-inserted row.

Q. Why insert the row you've just added into another table
(tbl_currentTrans)? After all you already have it in a table and you know
the primary key.

--
David Portas
----
Please reply only to the newsgroup
--