Some System Functions are Missing in Master db 1

[JohnBates]

hi,

I have 3 SQL Server 2000 installations (sp3a)

1 of them is missing 3 *system* user defined functions in the master db:

- fn_escapecmdshellsymbols
- fn_escapecmdshellsymbolsremovequotes
- fn_get_sql

They did not get deleted; I don't think this server ever had these functions.

I tried copying them from another server - by right-click copy - apparently you cant copy a fn if it is owned by system_function_schema ?

This problem is causing the REPL Distribution Agent Cleanup job to fail.

Is there any way to copy a system function ?
or will I have to re-install SP3a ?

Thanks, John

 

[mrdenny]

You could try reinstalling the service pack. That "should" put them back. I would try this on a dev system first though.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JohnBates]

thanks mrdenny,

"I would try this on a dev system first though. "

I wish I had one.

John

 

[NathanGriffiths]

Try this, its a bit messy but will get you what you need.

On an uncorrupted master db:

USE master

/*
Change server settings to allow updates to system tables
*/

EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE

/*
Set uid of function owner to 1 ("dbo")
*/

UPDATE sysobjects 
SET uid = 1
WHERE [name] in ('fn_escapecmdshellsymbols','fn_escapecmdshellsymbolsremovequotes',
'fn_get_sql')

Now that dbo is the owner of these objects, you can script the text of these functions (Script to clipboard as Create...) in Query Analyzer, or use the Properties tab in Enterprise Manager, and use this script to create them in the master database where they are missing.

Note that when they are scripted, the script will recreate them with system_function_schema as the owner.

Once you have recreated these functions, put the uncorrupted master database settings back to normal:

/*
Set the object owner back to system_function_schema
*/

UPDATE sysobjects 
SET uid = 4
WHERE [name] in ('fn_escapecmdshellsymbols','fn_escapecmdshellsymbolsremovequotes',
'fn_get_sql')


/*
Prevent updates to system tables
*/

EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE

Hope thats helps!

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[JohnBates]

Thanks Nathan !

I will backup master, then give your idea a try.

Have a good day down under.

John

 

[JohnBates]

... I have an update that may help someone else along the way.

I chose to just reinstall service pack sp3a.

They recommend that if Terminal Serives is running, you stop/disable that before installing the service pack, so I did that. Stopped SQL Server Agent.

Logged on under the same login that SQL Server Agent runs under.

Then installed the database engine components from the sp3a CD-ROM.

Rebooted. Now the Distribution Cleanup job (a replication job) runs successfully !

Thanks to everyone that submitted an idea. John