Sunday, December 25, 2011

DBReindexing script runs through QA but fails through SQL Job

An interesting and new thing noticed with a script. A normal DB re-indexing script which has cursor for collecting DB names and another dynamic cursor for collecting table names and then performing ‘Alter Rebuild Index ALL’ on all tables of databases. Here the issue noticed is the script runs fine without any issue in query analyzer(QA) and rebuilds all indexes. BUT when scheduled through a job the same script fails with below error:

“A cursor with the name 'TableCursor' does not exist. [SQLSTATE 34000] (Error 16916).  The step failed.”

I verified weather any table/schema name conflicting with system functions but that’s not the issue. Thinking it’s a permission issue verified the owner of the job and made sure it’s ‘sa’,  but still job fails. When verified the code ‘TableCursor’ is the cursor I am trying to create dynamically to fetch table names. If it’s syntax error it should not run through QA as well.

But later on one of my colleague suggested weather the issue with CURSOR scope? And when verified the answer is YES. The scope of the cursor is the issue, so verified the DB option ‘Default CURSOR’ was set to ‘Local’ and once after changing this option to ‘Global’ this script started running fine through SQL job.

No comments:

Post a Comment