Missing sp''s in master db

Question

Post reply

Missing sp''s in master db

Lisa-239472

Valued Member

Points: 71
More actions
December 29, 2005 at 12:30 pm

#111044

A scary thing has happened. I'm missing a couple of system stored procedures on my QA machine (e.g., sp_dbcmptlevel in master db). I tried re-installing service pack 3a (we're still at this level due to certain web apps), but I get errors during the install process. Any help in this matter would be greatly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Lisa,

How did it happened?

To fix: there was a post several days ago. See discussion

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=246152

"Dropped sp_spaceused

Posted 12/22/2005 5:58:00 AM"

There is a good advice by Brian.

Regards,Yelena Varsha

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

However, also check the upgrade scripts left by the service packs, also in the same directory. If they update a stored procedure during a service pack, it's not going to change that original file, so far as I am aware.

K. Brian Kelley
@kbriankelley

Lisa-239472 Valued Member Points: 71 More actions · Answer 3

There were 3 people at one time that had sysadmin access, but that has since change. Can only assume it was dropped by accident.

Unable to copy and execute sp_dbcmptlevel sp using script in procsyst.sql file (certain variables already declared). When I tried re-installing sp3a the error received was the following: "Error running script: sp3_serv_uni.sql(1)". Just really wanted to avoid rebuilding the master, but it looks like I'm running out of options...

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

Don't run the entire script. Rather, find the stored procedures which are missing and execute just the code related to them. For instance, run only the CREATE PROCEDURE statement for sp_dbcmptlevel (along with the SET ANSI_NULLS statement before and after).

If that's what you did, what do you mean by, "certain variables already declared?"

K. Brian Kelley
@kbriankelley

Lisa-239472 Valued Member Points: 71 More actions · Answer 5

That's exactly what I did. I only ran the code associated with the sp, however, this is the error message received: The variable name '@dbname' has already been declared. Variable names must be unique within a query batch or stored procedure. (Duh)

I inserted a begin statement before set no count on and an end stmt after return (0) -- sp_dbcmptlevel (at the end of the sp), but this didn't work either.

I can see this same sp in Dev/Prd and tried copying it and executing in the QA environment, but to no avail.

I'm perplexed...

SkiQueen SSC Journeyman Points: 75 More actions · Answer 6

OK, it's obvious I was having a bad day. Thank you for your reply...I inserted the SET ANSI_NULLS ON before declaring the variables and it worked fine.