Invalid stored procedures [How to check?]

I am on a development project where we are using SQL Server 2000 and using
almost exclusively stored procedures.
Because the team is all over the country we have run into a problem.
Because we are in the development/design phase things are changing as new
requirements are thrust upon us by managment.
Sometimes the datatype of table changes or a column is deleted. This makes
the stored procedure invalid. However, you don't know its invalid until you
access the stored procedure.
Is there a way to know that a stored procedure has become invalid or is
there a way to force a recompile so that you can see which stored procedure
now have problems?
Thanks!
EnzoPrior to posting my original message I read the portion of the BOL that you
pasted to this message.
The problem with sp_recompile is that it does not actually do the recompile!
It only marks it to be recompiled. What I want is way to actually
recompile all my stored procs without essentially dropping and recreating
them to find which ones are now invalid.
In Oracle and other DBMS system a recompile is an actuall recompile and not
simply marking a flag somewhere in the DD.
I guess the only way to do this is how we are doing it now which is to drop
and recreate every single stored proc.
"ilovesql" <ilovesql@.hotmail.com> wrote in message
news:ufC3E#kRDHA.2460@.TK2MSFTNGP10.phx.gbl...
> BOL:
> Recompiling a Stored Procedure
> As a database is changed by such actions as adding indexes or changing
data
> in indexed columns, the original query plans used to access its tables
> should be optimized again by recompiling them. This optimization happens
> automatically the first time a stored procedure is run after Microsoft®
SQL
> ServerT 2000 is restarted. It also occurs if an underlying table used by
the
> stored procedure changes. But if a new index is added from which the
stored
> procedure might benefit, optimization does not automatically happen (until
> the next time the stored procedure is run after SQL Server is restarted).
> SQL Server provides three ways to recompile a stored procedure:
> a.. The sp_recompile system stored procedure forces a recompile of a
> stored procedure the next time it is run.
>
> b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> in its definition indicates that SQL Server does not cache a plan for this
> stored procedure; the stored procedure is recompiled each time it is
> executed. Use the WITH RECOMPILE option when stored procedures take
> parameters whose values differ widely between executions of the stored
> procedure, resulting in different execution plans to be created each time.
> Use of this option is uncommon, and causes the stored procedure to execute
> more slowly because the stored procedure must be recompiled each time it
is
> executed.
>
> c.. You can force the stored procedure to be recompiled by specifying
the
> WITH RECOMPILE option when you execute the stored procedure. Use this
option
> only if the parameter you are supplying is atypical or if the data has
> significantly changed since the stored procedure was created.
>
> Note If an object referenced by a stored procedure is deleted or renamed,
> an error is returned when the stored procedure is executed. If, however,
an
> object referenced in a stored procedure is replaced with an object of the
> same name, the stored procedure executes without having to be recompiled.
>
> To recompile a stored procedure next time it is run
> Transact-SQL
>
> See Also
> Creating a Stored Procedure
> Deferred Name Resolution and Compilation
> Executing a Stored Procedure
> Programming Stored Procedures
> "Enzo" <touringcarclub@.hotmail.com> wrote in message
> news:e$JHDbjRDHA.2148@.TK2MSFTNGP11.phx.gbl...
> > I am on a development project where we are using SQL Server 2000 and
using
> > almost exclusively stored procedures.
> >
> > Because the team is all over the country we have run into a problem.
> >
> > Because we are in the development/design phase things are changing as
new
> > requirements are thrust upon us by managment.
> >
> > Sometimes the datatype of table changes or a column is deleted. This
> makes
> > the stored procedure invalid. However, you don't know its invalid until
> you
> > access the stored procedure.
> >
> > Is there a way to know that a stored procedure has become invalid or is
> > there a way to force a recompile so that you can see which stored
> procedure
> > now have problems?
> >
> > Thanks!
> > Enzo
> >
> >
>
>

Invalid connection string attribute

We have a development team that is having intermittant problems with
connecting to a server. They are receiving the following error:
Microsoft OLE DB Provider for SQL Server ... Error Message: Invalid
connection string attribute
for the following connection string:
Provider=SQLOLEDB;Data Source=SNLSQLDBD01;Initial
Catalog=WINTER;Trusted_Connection=Yes;DateFormat=m dy
I'd appreciate any insight - as I said the error is intermittant (love
those). I can't find any examples of DateFormat being used in a SQLOLEDB
provider so I am wondering about that.
thanks
Peter
We now think that the message was misleading - there may be something in the
conection string that it didn't like (DateFormat=mdy ?) but was able to
cope with. The job ran fine when scheduled to start at a different time when
there was lower activity.
"Peter Feakins" wrote:

> We have a development team that is having intermittant problems with
> connecting to a server. They are receiving the following error:
> Microsoft OLE DB Provider for SQL Server ... Error Message: Invalid
> connection string attribute
> for the following connection string:
> Provider=SQLOLEDB;Data Source=SNLSQLDBD01;Initial
> Catalog=WINTER;Trusted_Connection=Yes;DateFormat=m dy
> I'd appreciate any insight - as I said the error is intermittant (love
> those). I can't find any examples of DateFormat being used in a SQLOLEDB
> provider so I am wondering about that.
> thanks
> Peter
>
>
>
>
|||I couldn't find any reference in the docs on the provider
for the attribute DateFormat. I found some posts were a few
others were using it but they had the value in double quotes
and used formats such as "YYYY-MM-DD". DateFormat = mdy
looks more like a T-SQL thing. You may want to just execute
a set dateformat SQL statement if that's what you want to
set the format for the client entering datetime data.
-Sue
On Thu, 9 Sep 2004 14:59:13 -0700, "Peter Feakins"
<PeterFeakins@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>We now think that the message was misleading - there may be something in the
>conection string that it didn't like (DateFormat=mdy ?) but was able to
>cope with. The job ran fine when scheduled to start at a different time when
>there was lower activity.
>"Peter Feakins" wrote: