Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts

Tuesday, March 27, 2012

cannot execute sp/access sp properties using gui

I am not able to right click to execute a stored procedure or right click to access the stored procedure properties using Management Studio. Any ideas on what is causing this or if it is supposed to be this way?

Any help is much appreciated.

hi,

does the user you are in the database have enought permissions to do that?

regards

|||

Thanks for the response Andrea,

I login as sa.


Suppose I could mention this is on my local machine. Executing @.@.version returns:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I am the first one to be running on 2005 locally. Everyone else is still at 2000 so I have not been able to compare against other machines running express.

cannot execute sp/access sp properties using gui

I am not able to right click to execute a stored procedure or right click to access the stored procedure properties using Management Studio. Any ideas on what is causing this or if it is supposed to be this way?

Any help is much appreciated.

hi,

does the user you are in the database have enought permissions to do that?

regards

|||

Thanks for the response Andrea,

I login as sa.


Suppose I could mention this is on my local machine. Executing @.@.version returns:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I am the first one to be running on 2005 locally. Everyone else is still at 2000 so I have not been able to compare against other machines running express.

Cannot execute extended stored procedure?

I've written an extended stored procedure using VS 2005/C++. The sp can be
added and executed on my local SQL Server 2000. However, after I copied to
production server. I can sp_addextendedproc it but executing shows error:
Msg 0, Level 16, State 0, Procedure xp_calc, Line 1
Cannot load the DLL C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\XPDLLs\xp_calc.dll, or one of the DLLs it references.
Reason: 126(The specified module could not be found.)
I remember I need to copy some C++ runtime(?) dll to the server when I was
using VS 2003. Which files are needed for VS2005?
Thanks,nick (nick@.discussions.microsoft.com) writes:
> I've written an extended stored procedure using VS 2005/C++. The sp can be
> added and executed on my local SQL Server 2000. However, after I copied to
> production server. I can sp_addextendedproc it but executing shows error:
> Msg 0, Level 16, State 0, Procedure xp_calc, Line 1
> Cannot load the DLL C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\XPDLLs\xp_calc.dll, or one of the DLLs it references.
> Reason: 126(The specified module could not be found.)
> I remember I need to copy some C++ runtime(?) dll to the server when I was
> using VS 2003. Which files are needed for VS2005?
That would be MSVCR80.DLL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You can find the dependent DLLs using "Dependency Walker"
which can be downloaded from the Microsoft website
or from here
http://www.dependencywalker.com/

Sunday, March 25, 2012

Cannot Drop Server

I am receiving the following error when attempting to drop a server to rename
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?
It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>
|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:

> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>
>
|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:

Cannot Drop Server

I am receiving the following error when attempting to drop a server to renam
e
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:

> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>
>|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
>

Cannot Drop Server

I am receiving the following error when attempting to drop a server to rename
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:
> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
> >I am receiving the following error when attempting to drop a server to
> >rename
> > it
> >
> > Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> > Line
> > 39
> > Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> > replication.
> >
> > Replication is not occuring on this server.
> > When I execute sp_get_distributor the results show that replication is not
> > occuring on this server.
> >
> > Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> > My question is should I delete the record that is in this table to allow
> > renaming of the server?
> >
>
>|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
>> It is possible this server has a remote distributor. Issue a call to
>> sp_helpdistributor to get the server which is acting as the distributor's
>> name.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in
>> message
>> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>> >I am receiving the following error when attempting to drop a server to
>> >rename
>> > it
>> >
>> > Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
>> > Line
>> > 39
>> > Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
>> > replication.
>> >
>> > Replication is not occuring on this server.
>> > When I execute sp_get_distributor the results show that replication is
>> > not
>> > occuring on this server.
>> >
>> > Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
>> > My question is should I delete the record that is in this table to
>> > allow
>> > renaming of the server?
>> >
>>sql

Cannot DROP PROCEDURE 'duplicate incombolists' because it is being referenced by object

How to resolve this problem
Cannot DROP PROCEDURE 'duplicate _in_combolists' because it is being
referenced by object 'combolists_unique'.
Thanks in advance
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Presumably htis is on application of the snapshot? Before being created in
the initialization, objects are dropped. It looks like you have a dependency
which is preventing this being achieved. Please use sp_depends with
'duplicate _in_combolists' to investigate what is preventing the drop.
Removing the dependency should allow the initialization to proceed.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Tuesday, March 20, 2012

Cannot debug activated CLR Stored Procedure

Hi

I'm having a problem debugging (with VS2005,) a CLR Stored Procedure which is activated on a queue. I know it is working because it is consuming and processing messages when one appears in the queue.

I can debug it 'manually' by either right-clicking on the SP within VS2005 and selecting 'Step into Stored Procedure' or by attaching to the SQL Server Management Studio process, setting a breakpoint and executing the stored procedure from a Management Studio query window.

However, if I send a message to my queue within Management Studio, my breakpoints are NOT being hit within VS2005, but the message IS reaching my queue and it IS being processed by the SP.

Can anyone help?

Thanks

The only way to debug this is by attaching VS debugger directly to sqlservr.exe (i.e. the database engine) for managed debugging. You will need to install the Visual Studio Remote Debugger on the machine running the engine and you will need to add symbols to your assembly.

Hope that helps,

Rushi

Cannot debug activated CLR Stored Procedure

Hi

I'm having a problem debugging (with VS2005,) a CLR Stored Procedure which is activated on a queue. I know it is working because it is consuming and processing messages when one appears in the queue.

I can debug it 'manually' by either right-clicking on the SP within VS2005 and selecting 'Step into Stored Procedure' or by attaching to the SQL Server Management Studio process, setting a breakpoint and executing the stored procedure from a Management Studio query window.

However, if I send a message to my queue within Management Studio, my breakpoints are NOT being hit within VS2005, but the message IS reaching my queue and it IS being processed by the SP.

Can anyone help?

Thanks

The only way to debug this is by attaching VS debugger directly to sqlservr.exe (i.e. the database engine) for managed debugging. You will need to install the Visual Studio Remote Debugger on the machine running the engine and you will need to add symbols to your assembly.

Hope that helps,

Rushi

Sunday, March 11, 2012

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

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].& #91;usp_ServerFullBackup_SystemDatabases
]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].& #91;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...
>
>|||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 nothin
g
works.
"sharman" wrote:
[vbcol=seagreen]
> This is the Create Procedure Code:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].& #91;usp_ServerFullBackup_SystemDatabases
]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].& #91;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 n
ot
> there. I even tried to create it with a name usp_XXX or usp_YYY and got th
e
> same error message. Thanks.
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||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:[vbcol=seagreen]
> 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 noth
ing
> works.
> "sharman" wrote:
>

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...
>
>
|||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:
[vbcol=seagreen]
> 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:
|||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:[vbcol=seagreen]
> 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: