SQL Server Performance Forum – Threads Archive

SQl 2005 Cursor Issue

We have run into a rather strange issue. We have a job to back up all user and system databases. It works fine on SQL 2000. After we created the job on SQL2005 (sp2+hf), it sometimes worked but other time the job failed with this error. could not locate entry in sysdatabases for database ‘ ‘, no entry found with that name. We used a cursor to get the name from master..sysdatabases. It seems that the content of the cursor gets changed during the loop, and we have no idea why there is a row that has db name ‘ ‘. Does anyone know how the cursor behavior is changed in SQL 2005? Any assistance is appreciated.

---

Is there any entry in sysdatabases view where name = ”?
Is any of your database has auto close option enabled?
Is there any error in the sql error log when this job failed other than backup failed message?
MohammedU.
Moderator
SQL-Server-Performance.com

---

Other than saying the backup command has failed, there is no other errors in errorlog.
Auto close option is not enabled for all databases.
If you run ‘select name from master..sysdatabases’, all system and user databases name returned, not ‘ ‘.

---

You got the error while running the backup or restore? MohammedU.
Moderator
SQL-Server-Performance.com

---

We got the error during backup.

---

Can you post the code? Is any one created any databases while backup job running? MohammedU.
Moderator
SQL-Server-Performance.com

---

Copy the names into a table variable, and run the cursor off the table variable.

---

I’m having the same problem. Why would it make a difference to base your cursor off of a a user table (basically a copy of master..sysdatabases) as opposed to querying master..sysdatabases directly? Gotta love SQL 2005. Never had this problem in SQL 2000.

---

The point is that sysdatabases, as most "system tables", is probably not actually a table. This is why people keep telling you not to query system tables directly, but use the system SPs instead. Problem is of course that 9 times out of 10, most info from system SPs is stuff that you really don’t need, or you need to aggregate or cross-tab it to get something you can work with. Anyway, the table variable should guarantee a consistent list once the entries have been retrieved from sysdatabases, although you should probably use the output from the corresponding system SP instead – in which case (at least in SQL 2000) you can only use a temporary table, not a table variable.

---

… by the way, did you check the INFORMATION_SCHEMA views? Assuming they still exist in SQL 2005 …

---

Adrian, Thanks for the tip. I will use a table variable, as you suggested.

---

Yes they are available inSQL 2005 too with few more added.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>