Cannot create a Stored Procedure

Hi,
I am getting a error that the object already exists in the database when I
try to create a Stored Procedure. I have looked in sysobjects table and have
tried 5-6 different names for the Stored Procedure but nothing works. Any
help will be greatly appreciated. Thanks.Can you show the CREATE PROCEDURE code, and the exact error message?
Did you verify that you are in the right database (SELECT DB_NAME()) and
that you are checking sysobjects in the correct database?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
> Hi,
> I am getting a error that the object already exists in the database when I
> try to create a Stored Procedure. I have looked in sysobjects table and
> have
> tried 5-6 different names for the Stored Procedure but nothing works. Any
> help will be greatly appreciated. Thanks.|||This is the Create Procedure Code:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[usp_ServerFullBackup_SystemDatabases]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ServerFullBackup_SystemDatabases]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].usp_ServerFullBackup_SystemDatabases
AS
BEGIN
BACKUP DATABASE [master]
TO DISK = N'\\CurrentWeek\master.BAK'
WITH INIT , NOUNLOAD , NAME = N'master Backup', STATS = 25, FORMAT
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
and this is the error message I get on the query analyzer:
Server: Msg 2714, Level 16, State 5, Procedure
usp_ServerFullBackup_SystemDatabases, Line 5
There is already an object named 'usp_ServerFullBackup_SystemDatabases' in
the database.
and in the enterprise manager I get this message:
Microsoft SQL-DMO (ODBC SQLState: 42S01)
Error 2714: There is already an object named
'usp_ServerFullBackup_SystemDatabases' in the database
I tried querying the sysobjects table and the name of the Stored Proc is not
there. I even tried to create it with a name usp_XXX or usp_YYY and got the
same error message. Thanks.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Can you show the CREATE PROCEDURE code, and the exact error message?
> Did you verify that you are in the right database (SELECT DB_NAME()) and
> that you are checking sysobjects in the correct database?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
> > Hi,
> >
> > I am getting a error that the object already exists in the database when I
> > try to create a Stored Procedure. I have looked in sysobjects table and
> > have
> > tried 5-6 different names for the Stored Procedure but nothing works. Any
> > help will be greatly appreciated. Thanks.
>
>|||I also found out that it's only when I try to create the Stored Procedure in
the master database that I get this message. In all the other databases I am
able to create the Stored Procedure. I have tried different names but nothing
works.
"sharman" wrote:
> This is the Create Procedure Code:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[usp_ServerFullBackup_SystemDatabases]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[usp_ServerFullBackup_SystemDatabases]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE [dbo].usp_ServerFullBackup_SystemDatabases
> AS
> BEGIN
> BACKUP DATABASE [master]
> TO DISK = N'\\CurrentWeek\master.BAK'
> WITH INIT , NOUNLOAD , NAME = N'master Backup', STATS = 25, FORMAT
> END
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> and this is the error message I get on the query analyzer:
> Server: Msg 2714, Level 16, State 5, Procedure
> usp_ServerFullBackup_SystemDatabases, Line 5
> There is already an object named 'usp_ServerFullBackup_SystemDatabases' in
> the database.
> and in the enterprise manager I get this message:
> Microsoft SQL-DMO (ODBC SQLState: 42S01)
> Error 2714: There is already an object named
> 'usp_ServerFullBackup_SystemDatabases' in the database
> I tried querying the sysobjects table and the name of the Stored Proc is not
> there. I even tried to create it with a name usp_XXX or usp_YYY and got the
> same error message. Thanks.
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > Can you show the CREATE PROCEDURE code, and the exact error message?
> >
> > Did you verify that you are in the right database (SELECT DB_NAME()) and
> > that you are checking sysobjects in the correct database?
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.sqlblog.com/
> > http://www.aspfaq.com/5006
> >
> >
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
> > > Hi,
> > >
> > > I am getting a error that the object already exists in the database when I
> > > try to create a Stored Procedure. I have looked in sysobjects table and
> > > have
> > > tried 5-6 different names for the Stored Procedure but nothing works. Any
> > > help will be greatly appreciated. Thanks.
> >
> >
> >|||Could it be that it's a permissions problem? I have no problems creating
a Stored Proc in my master database.
That being said, I always prefer NOT to create any user defined objects
in my master database. I normally create an "administration" database
where I keep all my user objects. I think it too risky to mess to much
around in the Master database..:-).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
sharman wrote:
> I also found out that it's only when I try to create the Stored Procedure in
> the master database that I get this message. In all the other databases I am
> able to create the Stored Procedure. I have tried different names but nothing
> works.
> "sharman" wrote:
>> This is the Create Procedure Code:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[usp_ServerFullBackup_SystemDatabases]')
>> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> drop procedure [dbo].[usp_ServerFullBackup_SystemDatabases]
>> GO
>> SET QUOTED_IDENTIFIER OFF
>> GO
>> SET ANSI_NULLS ON
>> GO
>> CREATE PROCEDURE [dbo].usp_ServerFullBackup_SystemDatabases
>> AS
>> BEGIN
>> BACKUP DATABASE [master]
>> TO DISK = N'\\CurrentWeek\master.BAK'
>> WITH INIT , NOUNLOAD , NAME = N'master Backup', STATS = 25, FORMAT
>> END
>> GO
>> SET QUOTED_IDENTIFIER OFF
>> GO
>> SET ANSI_NULLS ON
>> GO
>> and this is the error message I get on the query analyzer:
>> Server: Msg 2714, Level 16, State 5, Procedure
>> usp_ServerFullBackup_SystemDatabases, Line 5
>> There is already an object named 'usp_ServerFullBackup_SystemDatabases' in
>> the database.
>> and in the enterprise manager I get this message:
>> Microsoft SQL-DMO (ODBC SQLState: 42S01)
>> Error 2714: There is already an object named
>> 'usp_ServerFullBackup_SystemDatabases' in the database
>> I tried querying the sysobjects table and the name of the Stored Proc is not
>> there. I even tried to create it with a name usp_XXX or usp_YYY and got the
>> same error message. Thanks.
>>
>>
>> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Can you show the CREATE PROCEDURE code, and the exact error message?
>> Did you verify that you are in the right database (SELECT DB_NAME()) and
>> that you are checking sysobjects in the correct database?
>> --
>> Aaron Bertrand
>> SQL Server MVP
>> http://www.sqlblog.com/
>> http://www.aspfaq.com/5006
>>
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
>> Hi,
>> I am getting a error that the object already exists in the database when I
>> try to create a Stored Procedure. I have looked in sysobjects table and
>> have
>> tried 5-6 different names for the Stored Procedure but nothing works. Any
>> help will be greatly appreciated. Thanks.
>>