How to force immediate recompile of triggers and detect errors?

I need a way to programmatically (via JDBC) find out which triggers for a table may not compile properly, so that I can disable the bad triggers.

I can do this fine in Oracle but cannot figure out if there's a way to do this in SqlServer. (In Oracle I'd just "alter trigger... compile" and select from user_errors.)

I know how to find the triggers that exist on a table, and I know how to enable/disable individual triggers. I know about sp_recompile, but all that does is flag the trigger for recompile at the next execution.

I need to verify whether the trigger is valid without having to actually invoke it. For example, if there's a bad Update trigger, I don't want to actually execute an update on the table.

One example of what I'm dealing with is this... We have Table A and Table B. There is an update trigger on Table B that references column A.col1. Then we alter Table A to drop col1. Later we have to update Table B. At this point the update will fail because of the bad trigger. I want to find and disable the trigger before executing the update on Table B. If there are other triggers on Table B that are valid, I want to leave them alone.Shouldn't this be part of your QA process, not part of your application?

-PatP|||No, it's a database configuration application, and there may be triggers that we don't own and didn't create that we have to disable if they're going to cause a problem in our db config process. If we disable any triggers, we'd report the situation and the customer would be expected to fix the triggers before going live again.sql

How to Force all Stored Procedures to "recompile"?

Is there a way to force all stored procedures to "recompile" or create a new
execution plan? What we are trying to do is find a way to quickly identify
all stored procedures that are invalid because of schema changes like table
s or columns dropped or alt
ered.
Thanks,
BLGYou are asking two different things. AFAIK, there's no way to know which pla
ns are invalidated (a plan can be
invalidated for several reasons).
If you want a proc to recompile at next execution, you can use sp_recompile
on any of the tables that the proc
is using.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"blg" <anonymous@.discussions.microsoft.com> wrote in message
news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> Is there a way to force all stored procedures to "recompile" or create a new execu
tion plan? What we are
trying to do is find a way to quickly identify all stored procedures that ar
e invalid because of schema
changes like tables or columns dropped or altered.
> Thanks,
> BLG|||Hi,
Run the command DBCC FREEPROCCACHE to remove all compile plans from the
procedure cache.
Karl Gram, BSc, MBA
http://www.gramonline.com
"blg" <anonymous@.discussions.microsoft.com> wrote in message
news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> Is there a way to force all stored procedures to "recompile" or create a
new execution plan? What we are trying to do is find a way to quickly
identify all stored procedures that are invalid because of schema changes
like tables or columns dropped or altered.
> Thanks,
> BLG|||D'oh. Why didn't I think of that? :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message news:uqRIwlqEEHA.4080@.TK2MSFTNGP09.
phx.gbl...
> Hi,
> Run the command DBCC FREEPROCCACHE to remove all compile plans from the
> procedure cache.
> --
> Karl Gram, BSc, MBA
> http://www.gramonline.com
>
> "blg" <anonymous@.discussions.microsoft.com> wrote in message
> news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> new execution plan? What we are trying to do is find a way to quickly
> identify all stored procedures that are invalid because of schema changes
> like tables or columns dropped or altered.
>

How to force a recompile on the stored procs?

I want to recompile all the stored procs in the database. I've tried
sp_recompile on all of them, but it simply marks them to be recompiled.
ThanksThey don't actually get recompiled until the next time they get called.
That is how it always works.
--
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks|||DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks

How to force a recompile on the stored procs?

I want to recompile all the stored procs in the database. I've tried
sp_recompile on all of them, but it simply marks them to be recompiled.
Thanks
They don't actually get recompiled until the next time they get called.
That is how it always works.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks
|||DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks

How to force a recompile on the stored procs?

I want to recompile all the stored procs in the database. I've tried
sp_recompile on all of them, but it simply marks them to be recompiled.
ThanksThey don't actually get recompiled until the next time they get called.
That is how it always works.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks|||DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks