Sp_ not 'scripted' - corrupt syscomments?

I created a new procedure in the query analzyer (sql server 8.0)

It appeared to execute correctly on creation.

However, upon trying to open it within the 'stored procedure' menu I get the
following error:


'//ERROR

Error 0:[SQL-DMO]Object 'dbo.sp_adduser' was not scripted. It is possible
that the information in system table 'syscomments' about the table was
corrupt or missing

'//ERROR FINISH

Can anyone help me understand what is going on here.....Here is my Proc.
code:

Create Procedure sp_adduser
@u_name varchar(50),
@u_password varchar(12),
@u_firstname varchar(50),
@u_lastname varchar(50),
@u_email varchar(50),
@u_id int output
As
set nocount on
if not exists(select u_id from t_user where u_name=@u_name)
begin
INSERT INTO t_user(u_name, u_password, u_firstname, u_lastname,
u_email)
VALUES (@u_name, @u_password, @u_firstname, @u_lastname, @u_email)
select @u_id=@@identity
end
else
select @u_id=-1
return

Many thanks
Jason

[Bob Barrows [MVP]]

[email]jason@catamaranco.com[/email] wrote:

> I created a new procedure in the query analzyer (sql server 8.0)
>
> It appeared to execute correctly on creation.
>
> However, upon trying to open it within the 'stored procedure' menu I
> get the following error:
>
>
> '//ERROR
>
> Error 0:[SQL-DMO]Object 'dbo.sp_adduser' was not scripted. It is
> possible that the information in system table 'syscomments' about the
> table was corrupt or missing
>
> '//ERROR FINISH
>
> Can anyone help me understand what is going on here.....Here is my
> Proc. code:
>
> Create Procedure sp_adduser

Do not use the "sp_" prefix for non-system stored procedures. At best, you
sill cause a slight performance problem since the query engine will attempt
to run it as a system stored procedure, until it finds that it does not
exist in the master database. At worst, you will create a procedure with the
same name as an existing system procedure, which is what I suspect has
happened here ... yes, a quick glance in BOL reveals this:

-----------------------------------------------------
sp_adduser
Adds a security account for a new user in the current database. This
procedure is included for backward compatibility. Use sp_grantdbaccess.

Syntax
sp_adduser [ @loginame = ] 'login'
[ , [ @name_in_db = ] 'user' ]
[ , [ @grpname = ] 'group' ]

------------------------------------------------------

I suspect you will need to restore your database from a backup to undo this
damage.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Thanks - good advice...I actually created this on a remote server so I have
instructed my web host to advise on this...

Many thanks for this comeback.

- Jason
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:utwuHUzdEHA.1604@TK2MSFTNGP11.phx.gbl...

> [email]jason@catamaranco.com[/email] wrote:

> > I created a new procedure in the query analzyer (sql server 8.0)
> >
> > It appeared to execute correctly on creation.
> >
> > However, upon trying to open it within the 'stored procedure' menu I
> > get the following error:
> >
> >
> > '//ERROR
> >
> > Error 0:[SQL-DMO]Object 'dbo.sp_adduser' was not scripted. It is
> > possible that the information in system table 'syscomments' about the
> > table was corrupt or missing
> >
> > '//ERROR FINISH
> >
> > Can anyone help me understand what is going on here.....Here is my
> > Proc. code:
> >
> > Create Procedure sp_adduser

>
> Do not use the "sp_" prefix for non-system stored procedures. At best, you
> sill cause a slight performance problem since the query engine will

attempt

> to run it as a system stored procedure, until it finds that it does not
> exist in the master database. At worst, you will create a procedure with

the

> same name as an existing system procedure, which is what I suspect has
> happened here ... yes, a quick glance in BOL reveals this:
>
> -----------------------------------------------------
> sp_adduser
> Adds a security account for a new user in the current database. This
> procedure is included for backward compatibility. Use sp_grantdbaccess.
>
> Syntax
> sp_adduser [ @loginame = ] 'login'
> [ , [ @name_in_db = ] 'user' ]
> [ , [ @grpname = ] 'group' ]
>
> ------------------------------------------------------
>
> I suspect you will need to restore your database from a backup to undo

this

> damage.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

ps: Just before you posted, I re-created with another name and it
worked...so I will have to be careful about this in the future
<jason@catamaranco.com> wrote in message
news:uLG$vZzdEHA.3632@TK2MSFTNGP09.phx.gbl...

> Thanks - good advice...I actually created this on a remote server so I

have

> instructed my web host to advise on this...
>
> Many thanks for this comeback.
>
> - Jason
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:utwuHUzdEHA.1604@TK2MSFTNGP11.phx.gbl...

> > [email]jason@catamaranco.com[/email] wrote:

> > > I created a new procedure in the query analzyer (sql server 8.0)
> > >
> > > It appeared to execute correctly on creation.
> > >
> > > However, upon trying to open it within the 'stored procedure' menu I
> > > get the following error:
> > >
> > >
> > > '//ERROR
> > >
> > > Error 0:[SQL-DMO]Object 'dbo.sp_adduser' was not scripted. It is
> > > possible that the information in system table 'syscomments' about the
> > > table was corrupt or missing
> > >
> > > '//ERROR FINISH
> > >
> > > Can anyone help me understand what is going on here.....Here is my
> > > Proc. code:
> > >
> > > Create Procedure sp_adduser

> >
> > Do not use the "sp_" prefix for non-system stored procedures. At best,

you

> > sill cause a slight performance problem since the query engine will

> attempt

> > to run it as a system stored procedure, until it finds that it does not
> > exist in the master database. At worst, you will create a procedure with

> the

> > same name as an existing system procedure, which is what I suspect has
> > happened here ... yes, a quick glance in BOL reveals this:
> >
> > -----------------------------------------------------
> > sp_adduser
> > Adds a security account for a new user in the current database. This
> > procedure is included for backward compatibility. Use sp_grantdbaccess.
> >
> > Syntax
> > sp_adduser [ @loginame = ] 'login'
> > [ , [ @name_in_db = ] 'user' ]
> > [ , [ @grpname = ] 'group' ]
> >
> > ------------------------------------------------------
> >
> > I suspect you will need to restore your database from a backup to undo

> this

> > damage.
> >
> > Bob Barrows
> > --
> > Microsoft MVP - ASP/ASP.NET
> > Please reply to the newsgroup. This email account is my spam trap so I
> > don't check it very often. If you must reply off-line, then remove the
> > "NO SPAM"
> >
> >

>
>