Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 SQL Server 6.5 \ SQL Server 7.0
 sp_configure is missing

Author  Topic 

francisb
Starting Member

7 Posts

Posted - 2009-12-03 : 18:59:30
Hi,

I have a customer who somehow managed to delete sp_configure from their master database. This is a SQL 6.5 server.

Using another 6.5 server, I managed to script out sp_configure into a file. So far so good. However, when I try to install it, I'm getting a message saying 'updates to catalog are not allowed'.

Hummm... all right, so using Enterprise Manager, I open up the configuration form for this server, try to change Allow Updates from 0 to 1 and then I get 'cannot find sp_configure'! I also tried to update sysconfigures directly... still no success.

I'm running in circles here...

How can I compile sp_configure back into the master database? Is there a trace flag I can use to start the SQL Server in a mode that would allow me to do this?

Thanks for any help!
Regards ~ F.


tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-03 : 19:06:37
You'll probably need to reinstall the software as who knows what else is missing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

francisb
Starting Member

7 Posts

Posted - 2009-12-03 : 21:00:39
I don't disagree. However, 6.5 is much less user friendly then the newest version when it comes to re-attaching the databases after I give this SQL server a new heart... I can't simply dettach/attach the DBs. If I recall correctly, I'd have to recreate the devices and the DBs using the exact names and sizes. Is thee an easier way to do this?

That server will be de-comissionned in a few months. They will not upgrade the SQL server or application that is using it as they are moving to a different system (they've been acquired). I just need this server to survive a few more months.

Hence the initial attempt to just re-install sp_configure. Isn't there any way I can allow catalog updates with a start-up flag?

Thanks ~ F.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-03 : 22:26:16
I worked extensively on SQL Server 6.5 about 10+ years ago, but unfortunately my memory is very fuzzy on it these days.

What configuration are you trying to update?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

francisb
Starting Member

7 Posts

Posted - 2009-12-03 : 22:45:58

Simply setting Allow Updates to 1 would allow me to re-install sp_configure. It's a catch 22 right now.

I can't install sp_configure because Allow Updates is set to 0 and I can't set Allow Updates through Enterprise Maanger, because sp_configure isn't installed.

Regards ~ F.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-03 : 23:27:07
But why do you need sp_configure? Is the fact that it is missing causing an issue?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

francisb
Starting Member

7 Posts

Posted - 2009-12-03 : 23:40:02
Yes - I also have data corruption on one of the databases on that same server and I'm running out of locks as I'm trying to salvage what I can from the corrupted table.

So the immediate need is to increase the available Locks, but I'm pretty sure I'll run into other things as well and having sp_configure installed (as it should) will allow me to alter the server config as needed while I'm trying to ressucitate this server.

~ F.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-03 : 23:49:06
I realize I'm not being any help, but since no one else is responding I might as well keep asking questions. Does the customer have a good backup of the master database? If they don't, then perhaps you could install 6.5 on another available server using the exact same paths, stop the SQL services on both systems, copy over the master files from the new system, startup SQL services on your problematic system. You'll lose logins and anything else stored in master, so be sure to script that stuff out before you try this. I know this would work in 2000 and above, but I'm not quite sure about 6.5 as I don't think I ever tried it there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

francisb
Starting Member

7 Posts

Posted - 2009-12-04 : 00:12:18
Yeah - I'm really trying to avoid re-building the whole SQL server.
I guess I'll have to resort to something like that in the end if I can't find a way to get access to this server's configuration...

Thanks for trying anyway :)

Regards ~ F.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-04 : 00:57:11
Sorry I don't have the magic answer, good luck!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

francisb
Starting Member

7 Posts

Posted - 2009-12-05 : 02:12:32
Well well well... I just found my magic answer!

It turns out starting the SQL Server in single user mode (startup param: -m) actually allows updates to catalogs.

So I did that and managed to recompile the sp_configure script that I salvaged from another server. SQL server swallowed it without any complaints this time. Lovely!

I thought I'd post the solution here in case anyone would encounter a similar problem.

Cheers!
~ Francis

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-05 : 05:38:07
Thank you for the followup and glad you got it working!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page
   

- Advertisement -