SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Object already exists, but can't drop it!


Object already exists, but can't drop it!

Author
Message
ty 54262
ty 54262
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 30
Hi All,

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.
free_mascot
free_mascot
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3991 Visits: 2235
What kind of permission do you have on server? If you do not have permission than too such kind of error message pop up.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
ty 54262
ty 54262
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 30
Hi Mate,

Thanks heaps for the reply!

I was using the sa account, so I don't think permissions are an issue - happy to be told otherwise.

I found this article the other day which explains a similar problem (better than I have) but didn't seem to solve it.
http://support.microsoft.com/default.aspx?scid=kb;en-us;827448&Product=sql2k
This article doesn't seem to mention functions, perhaps that the difference?
ty 54262
ty 54262
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 30
--
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28359 Visits: 39959
if you are sure you are not mispelling it, it's probabyl under a differnet schema, and you get partial matches.
you should explicitly name the schema and object name:

SELECT schema_name(schema_id) as SchemaName,name as ObjectName from sys.procedures where name = 'myProcedure'
IF object_id('dbo.myProcedure') IS NOT NULL
DROP PROCEDURE dbo.myProcedure;



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ty 54262
ty 54262
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 30
Hi Lowell,
Thanks for the response, tried your query but no luck.
Even just a simpler version didn't work:
SELECT * from sys.procedures where name = 'XXXX'
SELECT * from sys.procedures where name like '%XXXX%'

I tried this against A WORKING databases and it still didn't work, does "sys.procedures" store functions?
ON A WORKING DATABASE - changing your query to use sys.objects works perfectly.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39646 Visits: 38560
Try the same query using sys.objects.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ty 54262
ty 54262
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 30
Lynn Pettis (4/14/2014)
Try the same query using sys.objects.


Thanks for the suggestion but no luck, in my opinion it is not in the sys.objects view.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86810 Visits: 41103
It sounds like either you're in the wrong database when you're looking for things or the things are in the wrong database. They didn't get copied to the master database by mistake, did they?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ty 54262
ty 54262
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 30
Jeff Moden (4/14/2014)
It sounds like either you're in the wrong database when you're looking for things or the things are in the wrong database. They didn't get copied to the master database by mistake, did they?


I can't see how they would have copied to master, how would I find these?

I've just run this script, which fails as expected.
USE Live_DB
GO
select name from sys.objects where name like '%HighCharacter%'
select object_id('HighCharacter')
GO
CREATE FUNCTION [dbo].[HighCharacter] (
) RETURNS HighLowCharType
AS
BEGIN
RETURN REPLICATE(NCHAR(65392), 4000)
END;
GO
drop function [HighCharacter];
GO
print ''

USE Pilot_DB
GO
print 'select on pilot'
select name from sys.objects where name like '%HighCharacter%'
select object_id('HighCharacter')
GO

USE Master
GO
print 'select on master'
select name from sys.objects where name like '%HighCharacter%'
select object_id('HighCharacter')
GO




Which outputs the below - Note there is no exact match on name in first select from broken DB:
name
--------------------------------------------------------------------------------------------------------------------------------
BAC_HighCharacter
EXTGEN_GenerateBAC_HighCharacter
GenerateHighCharacter
TY_HighCharacter

(4 row(s) affected)


-----------
NULL

(1 row(s) affected)

Msg 2714, Level 16, State 3, Procedure HighCharacter, Line 5
There is already an object named 'HighCharacter' in the database.
Msg 3701, Level 11, State 5, Line 1
Cannot drop the function 'HighCharacter', because it does not exist or you do not have permission.

select on pilot
name
--------------------------------------------------------------------------------------------------------------------------------
GenerateHighCharacter
HighCharacter

(2 row(s) affected)


-----------
726579414

(1 row(s) affected)

select on master
name
--------------------------------------------------------------------------------------------------------------------------------

(0 row(s) affected)


-----------
NULL

(1 row(s) affected)

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search