stored procedure creation error: already an object named xxx in the database
-
I am trying to drop and recreate a user stored procedure in the master db, but can't seem to do it. What am I missing? I can't find that it already exists when looking in Enterprise Manager. I also can't find it using Query Analyzer, yet I am unable to create it in Query Analyzer. (I must create it using a script).
In Query Analyzer, logged in as the owner and administrator, in the master db, here is what I am doing:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_schedule_status_reset]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_schedule_status_reset]
--> The command(s) completed successfully.
select * from sysobjects where name like 'sp_sch%'
--> returns only the sp_schemata_rowset
sp_help sp_schedule_status_reset
--> Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71
The object 'sp_schedule_status_reset' does not exist in database 'master'.
select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_schedule_status_reset]')
select * from sysobjects where id = object_id(N'[dbo].[sp_schedule_status_reset]')
select * from dbo.sysobjects where id = object_id(N'sp_schedule_status_reset')
select * from sysobjects where id = object_id(N'sp_schedule_status_reset')
--> all 4 return null
CREATE PROCEDURE [dbo].[sp_schedule_status_reset]
AS
.....
go
--> gives error message:
Server: Msg 2714, Level 16, State 5, Procedure sp_schedule_status_reset, Line 9
There is already an object named 'sp_schedule_status_reset' in the database.
CREATE PROCEDURE [sp_schedule_status_reset]
AS
.....
go
--> same error message:
Server: Msg 2714, Level 16, State 5, Procedure sp_schedule_status_reset, Line 9
There is already an object named 'sp_schedule_status_reset' in the database.
-
Please post your sp here.
-
if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[sp_schedule_status_reset]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_schedule_status_reset]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_schedule_status_reset]
AS
EXEC master..xp_cmdshell 'net start sqlserveragent', no_output
EXEC master..xp_cmdshell 'cscript %IPE_ROOTDIR%\Eng_Scripts\IPE_OnSQLRestart.vbs', no_output
UPDATE ipe..schedule_event set status = 'ERROR' where status in ('DELETED', 'RUNNING', 'COPYING') and event_type in ('Baseline', 'Trace', 'eDoc', 'Update')
UPDATE ipe..report set status = 'ERROR' where status = 'RUNNING'
GO
EXEC master..sp_procoption 'sp_schedule_status_reset','startup','true'
SET QUOTED_IDENTIFIER OFF
GO
-
Is this a typo in if exists (select 1 ...?
To check for existence of an object I use something like
IF OBJECT_ID(N'[dbo].[FUNWITHNULLS]') > 0
DROP TABLE FUNWITHNULLS
GO
Would
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Which database did you try to create this sp? Startup procedures must be owned by the database owner in the master database. Try to create it in master database.
-
I was in the master db. My first box did not store my spaces, so it was hard to read, sorry about that. (Having trouble getting this site to keep the spaces between the lines.) I was logged into the master db as the owner and administrator. My problem is that I can't find the existing stored procedure, yet when I try to recreate it, I get a message that it already exists.
I did several select statements to test if it was there, and could not find it in Query Analyzer or in Enterprise Manager. The select statements and my answers are in my first (messy) posting.
And select 1 is valid, not a typo.
-
I have no problem to run your script in my test system. What is your SQL Server version and service pack?
-
SQL Server 2000, SP3.
I guess a better way to phrase my question is: How do I go about getting rid of an old copy of a stored procedure so I can write a new copy if I can't find the old one, yet somewhere it exists (according to the SQL Server powers in charge of letting me create it).
The following sql statements all returned nothing (run by Query Analyzer in the master db):
select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_schedule_status_reset]')
select * from sysobjects where id = object_id(N'[dbo].[sp_schedule_status_reset]')
select * from dbo.sysobjects where id = object_id(N'sp_schedule_status_reset')
select * from sysobjects where id = object_id(N'sp_schedule_status_reset')
These returned the values after the -->
select * from sysobjects where name like 'sp_sch%'
--> returns only the sp_schemata_rowset
sp_help sp_schedule_status_reset
--> Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71
The object 'sp_schedule_status_reset' does not exist in database 'master'.
-
"Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71 The object 'sp_schedule_status_reset' does not exist in database 'master'"
Above error message indicates that you have error at line 71 and I don't think that is the create procedure statement. Double click the error in QA and it will point to you which statement generating this error.
-
Sorry, I'm not being clear. I ran the statements in the last posting to try and find the stored procedure after I received the errors from the create statement indicating that it already exists. I could not find it either using Query Analyzer or Enterprise Manager.
-
Would...???Hm...obviously I haven't finished my first response...and now it's too late.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It's not too late -- I'll take any suggestions. (But I'm leaving now for the day, so you have until tomorrow to think of more.)
-
Ok. Paste your script into QA, Run it, get error, click the error and it will tell you which statement is wrong. Post that statement here.
-
From last line of first post, error is:
There is already an object named 'sp_schedule_status_reset' in the database.
My problem is that I can't find it to get rid of it to create the new one.
-
Post that statement which give you this error message here.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply