How to get invalid stored procedures

Hi, All,
Is there any script or command that can get all invalid stored procedures in
SQL Server 7.0/2000. Thanks
JieWhat do you mean by invalid ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"J Gao" <jie.gao@.tequilasoftware.com> wrote in message
news:%23GbjhauSDHA.2196@.TK2MSFTNGP12.phx.gbl...
Hi, All,
Is there any script or command that can get all invalid stored procedures in
SQL Server 7.0/2000. Thanks
Jie|||Invalid means there is syntax error in the stored procedures. I would like
to have query to get the all stored procedures that have syntax error with
it instead of going to every stored procedure to check sybtax. Thanks
Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OJO%23aJvSDHA.1552@.TK2MSFTNGP10.phx.gbl...
> What do you mean by invalid ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "J Gao" <jie.gao@.tequilasoftware.com> wrote in message
> news:%23GbjhauSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> Hi, All,
> Is there any script or command that can get all invalid stored procedures
in
> SQL Server 7.0/2000. Thanks
>
> Jie
>
>|||A stored procedure cannot be submitted to SQL Server if it is invalid, with
the exception of delayed name resolution. That could be a problem. Or, if
objects that the procedure uses are dropped, then that would invalidate,
although not delete, the procedure.
There is not a way to test this directly that I know of.
What you could do is:
1. Script out all of the stored procedures using DROP / CREATE.
2. Run that script from OSQL using an output file.
3. Examine the output file for errors.
Any procedures with serious syntax errors will not recreate and will now be
missing from your database. You can examine the errors to see what is wrong
should any of them need fixing.
The downside of this is that you don't want to do it during a productive
work period.
Russell Fields
"J Gao" <jie.gao@.tequilasoftware.com> wrote in message
news:uNzatTvSDHA.3188@.tk2msftngp13.phx.gbl...
> Invalid means there is syntax error in the stored procedures. I would like
> to have query to get the all stored procedures that have syntax error with
> it instead of going to every stored procedure to check sybtax. Thanks
> Jasper.
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OJO%23aJvSDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > What do you mean by invalid ?
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "J Gao" <jie.gao@.tequilasoftware.com> wrote in message
> > news:%23GbjhauSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> > Hi, All,
> > Is there any script or command that can get all invalid stored
procedures
> in
> > SQL Server 7.0/2000. Thanks
> >
> >
> > Jie
> >
> >
> >
>|||the only way you can do this is to build a new database
using the scripts created by scripting out your database.
Then you have to worry about dependencies. DB Ghost can do
all of this for you, displaying any syntax errors that may
exist in your database code. It will also show you
dependancy errors that may exist in your code. Both syntax
and dependancy errors can exist in the database code
simply because of inadequate change management. For
example a developer changes a column definition on a table
using an alter statement on that table. However there is a
stored procedure that relies on the original specification
of that column. You now have an error which will only
manifest itself when the stored procedure is next used.
This happens all the time, that's why we use DB Ghost for
our change management as it guarentees the source and the
deployement. Check out www.dbghost.com - it makes a good
read and the app comes with a full help manual.
Mark Baekdal
>--Original Message--
>Hi, All,
>Is there any script or command that can get all invalid
stored procedures in
>SQL Server 7.0/2000. Thanks
>
>Jie
>
>.
>