stored procedure creation error: already an object named xxx in the database

Question

stored procedure creation error: already an object named xxx in the database

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

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

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

just my 2ct

Maybe you should uncheck the sp as startup-sp before you drop it ?

if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[sp_schedule_status_reset]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

begin

EXEC master..sp_procoption 'sp_schedule_status_reset','startup','false'

drop procedure [dbo].[sp_schedule_status_reset]

end

or

use the alter procedure syntax

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ABerkowicz SSC Enthusiast Points: 183 More actions · Answer 2

By any chance, do you have a table named the same thing as your stored proc? I have received the error message when I try to create an object such as a SP and the name is exactly the same as the table.

jdalonzo SSCrazy Points: 2232 More actions · Answer 3

I apologize that this is about to hard to read, but I can't get this site to keep my spaces. Let's try this again -- I can't recreate this problem on any of my other servers, so I don't think that there is an easy answer -- I am trying to find out how to find an object that I can't find using Enterprise Manager or Query Analyzer but that the system seems to think is out there somewhere. I need to rewrite it, but I can't find it to delete it or alter it, and I can't create a new one because it already exists somewhere -- I just can't find it (or any other object by the same name). I know the SQL to create, drop, and alter is correct because they work on another server, so I don't expect the problem to be there (but I have been known to be wrong from time to time). ----------------------------- All of the following scripts are being run as owner and administator in the master db. ---------------------------------------------------------------------------------------- Create a new one:

CREATE PROCEDURE [dbo].[sp_schedule_status_reset] AS ....(script posted earlier).... --> There is already an object named 'sp_schedule_status_reset' in the database. -------------------------------------------------------------------------------------------------- Look for it: select * from sysobjects where id = object_id(N'sp_schedule_status_reset') --> (null) --------------------------------------------------------------------------------------------------------- Try to delete it: drop procedure [dbo].[sp_schedule_status_reset] -->

Cannot drop the procedure 'dbo.sp_schedule_status_reset', because it does not exist in the system catalog. ------------------------------------------------------------------------- Tried changing it to not startup :

EXEC master..sp_procoption 'sp_schedule_status_reset','startup','false' --> The object 'sp_schedule_status_reset' does not exist in database 'master'. ---------------------------------------------------------------------------------------------------

Try to alter it:

ALTER PROCEDURE [dbo].[sp_schedule_status_reset]

WITH recompile

AS ....(same as earlier create script) --> Invalid object name 'dbo.sp_schedule_status_reset'. ------------------------------------------------------- Try to create it again: --> There is already an object named 'sp_schedule_status_reset' in the database. ---------------------------------------------------------------

All of the above SQL statements work on another server, just not on this one. Is there somewhere other than the the sysobjects table that I might find this?

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 4

I had this exact same problem on one (and only one) of my servers.

Your not going to like my solution. I got tired of fighting it and reinstalled SQL server. Problem solved.

Have you thought about calling microsoft?

jdalonzo SSCrazy Points: 2232 More actions · Answer 5

Reinstalling SQL Server is a bit drastic -- this is a production server, and (for some reason) they frown on downtime.

Guess I'll try Microsoft -- just wanted to see if there was something I was missing first.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 6

I can almost guarantee that you have a non-sproc object that has the same name as your sproc. You can check this by running:

select *

from sysobjects so (nolock)

Where so.name Like '%' + <TableName> + '%'

Check the xtype column (see BOL keyword sysobjects for descriptions)

Signature is NULL

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 7

Why can't you try

ALTER PROCEDURE vs. CREATE PROCEDURE ???

If you are 100% sure that the proc is there and you can't see it try this and see what it gives you

Good Hunting!

AJ Ahrens

webmaster@kritter.net

johncyriac SSCommitted Points: 1542 More actions · Answer 8

Invisible stored procedre

Hi

Use the object search in your SQL Query Analyser and right click on the sp name ,press edit ,and do your modifications there

regards

john

jdalonzo SSCrazy Points: 2232 More actions · Answer 9

select * from sysobjects so (nolock) where so.name like '%sp_schedule_status_reset%' --> (null)

jdalonzo SSCrazy Points: 2232 More actions · Answer 10

select * from sysobjects so (nolock) where so.name like '%sp_schedule_status_reset%' --> (null)

jdalonzo SSCrazy Points: 2232 More actions · Answer 11

Query Analyzer Object Search on all object types on all db returned nothing. Alter procedure.... --> invalid object name. Create procedure --> There is already an object named sp_schedule_status_reset in tahe database. .

ABerkowicz SSC Enthusiast Points: 183 More actions · Answer 12

I know that someone said to reinstall SQL, which is a bit drastic but may need to be done but have you tried just rebooting the server? Maybe start here?

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 13

Maybe defeat it with it's own weapons ?

use master

select object_id('sp_schedule_status_reset') as The_Nasty_One