Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Object already exists, but can't drop it! Expand / Collapse
Author
Message
Posted Sunday, April 13, 2014 10:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 8:06 PM
Points: 8, 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.
Post #1561322
Posted Monday, April 14, 2014 12:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1561330
Posted Monday, April 14, 2014 4:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 8:06 PM
Points: 8, 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?
Post #1561425
Posted Monday, April 14, 2014 4:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 8:06 PM
Points: 8, Visits: 30
--
Post #1561426
Posted Monday, April 14, 2014 8:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1561508
Posted Monday, April 14, 2014 4:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 8:06 PM
Points: 8, 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.
Post #1561695
Posted Monday, April 14, 2014 6:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:03 PM
Points: 23,045, Visits: 31,569
Try the same query using sys.objects.



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)
Post #1561703
Posted Monday, April 14, 2014 6:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 8:06 PM
Points: 8, 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.
Post #1561705
Posted Monday, April 14, 2014 8:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561715
Posted Monday, April 14, 2014 11:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 8:06 PM
Points: 8, 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)
Post #1561745
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse