My client has just started getting a problem with their SQL server.
All of a sudden many many of the SPs they use daily got an error with the following msg - 'XXX' is not a recognized built-in function name.
I've had a look at the database and the function appears gone, its not showing in through SQL Management studio.
I had a copy of the function handy so I tried to recreate it and got the error
Msg 2714, Level 16, State 6, Line 3
There is already an object named 'XXX' in the database.
I attempted to drop the function and got the following.
Msg 3701, Level 11, State 5, Line 1
Cannot drop the function 'XXX', because it does not exist or you do not have permission.
So then I started investigating the sysobjects tables, nothing returned. I tried all lower and upper case and also sub strings within the function name, no luck.
select * from sysobjects where name like '%XXX%'
To give a little back story this SQL server is used for a large ERP and the same server is running about 5 other live sites currently (all working fine). At the end of each month they copy (backup and restore) all live databases to dev copies, this error started after a copy -- not sure if this was the cause or a coincidence. The dev database this was copied to works fine.
I'm not sure where to go from here with this issue, I don't a lot of Googleing but not had any luck. I'm not very familiar with the IT team onsite, its possible they did something wrong but I can't think what or how.
I would really appreciate your help.