October 20, 2008 at 2:54 pm
Hi. I have Tom Pullen's procedure for selectively defragmenting indexes deployed on several SQL 2000 Servers (SP3). One one server, it has stopped working. The message I get every time it attempts to run:
"[SQLSTATE 01000] (Message 0) A cursor with the name 'tables' does not exist. [SQLSTATE 34000] (Error 16916) A cursor with the name 'tables' does not exist. [SQLSTATE 34000] (Error 16916) A cursor with the name 'tables' does not exist. [SQLSTATE 34000] (Error 16916) A cursor with the name 'tables' does not exist. [SQLSTATE 34000] (Error 16916) Could not complete cursor operation because the set options have changed since the cursor was declared. [SQLSTATE 42000] (Error 16958). The step failed."
A link to Tom's procedure:
http://www.sql-server-performance.com/articles/per/automatic_reindexing_sql2000_p1.aspx
Does anyone have any insights?
Thank you.
October 20, 2008 at 10:16 pm
Did you check its ownership?
October 21, 2008 at 11:12 am
Yes. ownership has not changed and the owner is an admin of the SQL Server. Thsi exact sp runs successfully on other servers, and used to, on this one.
'
February 9, 2009 at 9:19 am
Hello, I'm the author of that SP.
For this issue (if it's not too late, and looking at the date of your post it may well be), I would start by looking at the set options on the respective servers/SPs and checking them. You can do this by inserting within the SP the statement
dbcc useroptions
which will print out what's set at the time of execution. does it match? If not, use an explicit SET to make them the same within the SP.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply