SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 SQL Server 6.5 \ SQL Server 7.0
 sp_configure is missing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

francisb
Starting Member

Canada
7 Posts

Posted - 12/03/2009 :  18:59:30  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 12/03/2009 :  19:06:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Canada
7 Posts

Posted - 12/03/2009 :  21:00:39  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 12/03/2009 :  22:26:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Canada
7 Posts

Posted - 12/03/2009 :  22:45:58  Show Profile  Reply with Quote

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

USA
36845 Posts

Posted - 12/03/2009 :  23:27:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Canada
7 Posts

Posted - 12/03/2009 :  23:40:02  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 12/03/2009 :  23:49:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Canada
7 Posts

Posted - 12/04/2009 :  00:12:18  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 12/04/2009 :  00:57:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Canada
7 Posts

Posted - 12/05/2009 :  02:12:32  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 12/05/2009 :  05:38:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000