Object already exists, but can't drop it!

  • 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.

  • 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."

  • 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?

  • --

  • 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!

  • 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.

  • Try the same query using sys.objects.

  • 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.

  • 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)

  • 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)

  • 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!

  • 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?

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

  • 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'

  • 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.

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

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