sp_helpDB only works from master

Question

sp_helpDB only works from master

MarkusB

SSC-Dedicated

Points: 37370
More actions
February 26, 2007 at 2:37 am

#68193

Hello everybody,
I'm at a customer and noticed that a lot of databases had owners which didn't exiast anymore. So I changed the owner for all databases to sa. The problem I now experience is when I execute sp_helpdb from any user database I get the following error: "A cursor with the name 'ms_crs_c1' does not exist. The statement has been terminated." The result set doesn't show a value for db_size, but all other columns are filled.
Only in master or msdb it executes without error. I'm logged on with a sysadmin account.
The server is SQL 2000 SP4 Ent. Edition. Something seems to be wrong with authentification, but I can't find the problem. Any ideas what I can do to fix this?
Tia Markus
[font="Verdana"]Markus Bohse[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

I think I found the problem. On all databases there sp_helpdb reported an error, the database option 'default to local cursor' was set to ON. After changing this to OFF, it works.

Now is there any risk involved by changing this for all the databases ?

Markus

[font="Verdana"]Markus Bohse[/font]

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 2

I'd have to say "No". The default for this setting is "OFF". The only risk is if there's some really poor code written for GUI's that doesn't ID the type of cursor... then, it's likely not going to be a "local cursor" which is why someone thought of the setting. I'd also make sure the MODEL database is set correctly so that when you make new DB's, they will have the correct setting, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

Jeff,

thank's for your reply. I agree with you that it shouldn't be a big issue. What confuses me is the fact that BOL says that turning this option off is mainly for backwardcompatibility and yet system stored procedures seem to depend on it.

Anyway I will change the databases.