urgent help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

urgent help needed

Author

Topic

aliyesami
Starting Member

30 Posts

Posted - 2008-10-07 : 12:44:37

why cant I add the stored procedure ? its not in the database but yet sql server think it is . i am running it under sa account.

thanks

exec sp_dropextendedproc 'xp_smtp_sendmail'

Cannot drop the procedure 'xp_smtp_sendmail', because it does not exist in the system catalog.

exec sp_addextendedproc xp_smtp_sendmail, 'c:\xpsmtp80.dll'

There is already an object named 'xp_smtp_sendmail' in the database.

There is already an object named 'xp_smtp_sendmail' in the database.

no rows returned

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-07 : 13:00:06

You probably need to restart the SQL Server service after the drop as it's probably still in memory.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

aliyesami
Starting Member

30 Posts

Posted - 2008-10-07 : 13:28:52

I restarted the sqlserver but its still saying there is an object already in the database .. grrrr this is so annoying ...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-07 : 13:41:45

It is possible you have an object of a different type in your database that has that name.

What result do you get when you run this code?

use master
exec sp_help 'xp_smtp_sendmail'

CODO ERGO SUM

aliyesami
Starting Member

30 Posts

Posted - 2008-10-07 : 14:04:47

use master
exec sp_help 'xp_smtp_sendmail'

The object 'xp_smtp_sendmail' does not exist in database 'master'.

aliyesami
Starting Member

30 Posts

Posted - 2008-10-07 : 14:58:26

ok its giving me error on any procedure I add . what could be the reason ? is the syntax wrong ?

exec sp_addextendedproc 'ABCAD', 'FADSFADFASF'

Server: Msg 2714, Level 16, State 7, Procedure sp_addextendedproc, Line 26
There is already an object named 'ABCAD' in the database.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-07 : 15:23:40

Perhaps the error is misleading. Try running SQL Profiler to see what is going on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

aliyesami
Starting Member

30 Posts

Posted - 2008-10-07 : 15:30:10

i have used sql profiler to monitor the running querries how can i use sql profiler to debug this issue? can you please elaborate

aliyesami
Starting Member

30 Posts

Posted - 2008-10-07 : 17:27:06

found the problem. . its a microsoft bug for sqlserver 2000 . ..
and there is a work around
http://support.microsoft.com/kb/827448

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-07 : 18:06:24

That error in the KB article sounds like something that would only happen when you made direct changes to the system tables.

Maybe someday MS will actually put FK constraints on the system tables.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources