Object already exists, but can't drop it!

Question

Object already exists, but can't drop it!

ty 54262

Mr or Mrs. 500

Points: 554
More actions
April 13, 2014 at 10:59 pm

#396595

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.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

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 Mr or Mrs. 500 Points: 554 More actions · Answer 2

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 Mr or Mrs. 500 Points: 554 More actions · Answer 3

ty 54262

Mr or Mrs. 500

Points: 554

April 14, 2014 at 4:41 am

#1705714

--

Lowell SSC Guru Points: 323518 More actions · Answer 4

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 Mr or Mrs. 500 Points: 554 More actions · Answer 5

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 SSC Guru Points: 442467 More actions · Answer 6

Lynn Pettis

SSC Guru

Points: 442467

April 14, 2014 at 6:12 pm

#1705956

Try the same query using sys.objects.

ty 54262 Mr or Mrs. 500 Points: 554 More actions · Answer 7

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 SSC Guru Points: 1004414 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ty 54262 Mr or Mrs. 500 Points: 554 More actions · Answer 9

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)

Lowell SSC Guru Points: 323518 More actions · Answer 10

well i can think of two things that might explain this:

1: you are not a sysadmin, and the object in question is outside of your permissions scope. ie the sysadmin created a table/procedure/function/view named "HighCharacter", so it exists, but you cannot see it due to permissions. this is most likely true, since your LIKE statement on sys.objects did not return the object in question.

2. a little tunnel vision possibility: you want to create a FUNCTION named "HighCharacter", but a Table,View or Procedure already exists by that name.

DROP FUNCTION would fail, becuase it should be DROP PROCEDURE, maybe.

select

schema_name(schema_id) As SchemaName,

name As ObjectName,

type_desc as ObjectType

from sys.objects where name = 'HighCharacter'

Create your Function with a different name, like as HighCharacterV2 and i bet it works just fine.

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 Mr or Mrs. 500 Points: 554 More actions · Answer 11

Lowell (4/15/2014)
well i can think of two things that might explain this:
1: you are not a sysadmin, and the object in question is outside of your permissions scope. ie the sysadmin created a table/procedure/function/view named "HighCharacter", so it exists, but you cannot see it due to permissions. this is most likely true, since your LIKE statement on sys.objects did not return the object in question.
2. a little tunnel vision possibility: you want to create a FUNCTION named "HighCharacter", but a Table,View or Procedure already exists by that name.
DROP FUNCTION would fail, becuase it should be DROP PROCEDURE, maybe.
select
schema_name(schema_id) As SchemaName,
name As ObjectName,
type_desc as ObjectType
from sys.objects where name = 'HighCharacter'
Create your Function with a different name, like as HighCharacterV2 and i bet it works just fine.

Hi Lowell,

I think there must be either something I'm missing or there is actually a fault within something in SQL. I'm using the SA account and have confirmed it has sysadmin role. Also your select query returns nothing, no matches found.

My understanding of SA with sysadmin was full access (it doesn't even check permissions), is that true? If so why can't I see it.

I attempted the drop command for every object type I could think of; table,view,function, procedure,trigger, keys, constraint etc -- All returned same error.

Creating the function with a new name works fine as expected. - We can't simply use a new name because the function is called in literally thousands of places. We have managed to implement a workaround into the business critical procedures so they are able to work but its not ideal.

I asked the client to attempted a backup and restore, still not luck. The restored DB still has the issue.

I happy to keep on looking into suggestions, however, my feel this is actually some kind of fault.

Does anyone know, where I can take the problem from here, as in MS support and what is the process?

ManicStar SSCoach Points: 17992 More actions · Answer 12

Did you try running a dbcc checkdb on the database its supposed to be in? Are you getting any errors from your storage/san?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Try this and let us know the results:

select

schema_name(schema_id) As SchemaName,

name As ObjectName,

type_desc as ObjectType

from sys.all_objects where name = 'HighCharacter'

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 14

I always run into this problem when it's an object with a different schema than dbo. See if my article about Dropping Schema Owned Tables[/url] helps you out. It's about tables, but the same principal applies.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.