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 find space used by transaction logs ?

Hello all,
how can I find space in use in transaction log file of a database
at given moment of time ? ( programmatically)
( not using dbcc sqlperf(logspace)
thank you
jagdishHere is the code :

declare @.log_size float(53)
declare @.log_space_used float(53)
declare @.total decimal(20,4)

select @.log_size = cntr_value from master.dbo.sysperfinfo where instance_name = 'pubs' and counter_name ='Log File(s) Size (KB)'
select @.log_space_used = cntr_value from master.dbo.sysperfinfo where instance_name = 'pubs' and counter_name ='Log File(s) Used Size (KB)'

set @.total = (@.log_space_used/@.log_size) * 100

select 'Using ' + convert(varchar(20),@.total) + '% (' + convert(varchar(20),@.log_space_used) + ' MB) of ' + convert(varchar(20),@.log_size) + ' MB' as 'Report'

.................
note : this is script against pubs database ,so change the name !

Cheers .
srdjan|||Thank you for your help

How to find formula column from a table programmatically?

Hi,

I'm on SQL Server 2000, say, I have a table named [orders], how I find
if there is any column which has a formula in it? In other words, how
to identify formula column programmatically? I've looked at
information_schema.columns view for clue but to no avail.

Thanks.Thanks, John, yes, I got it with a slight twist, which might have been
intended by you. In the WHERE clause I ensures that COLUMNPROPERTY(
id ,name, 'ISComputed') is true. Also, since ComputedColumn is
introduced in SQL 2000, I make sure to check server version before
using this property.

Regards,

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f1169cc$0$18750$afc38c87@.news.easynet.co.uk>...
> Hi
> Something like the following may be what you require:
> CREATE TABLE TestComputed ( Id INT IDENTITY NOT NULL, ID2 as 2*Id , Col1
> char(1) )
> SELECT OBJECT_NAME(id), Name, COLUMNPROPERTY( id ,name, 'ISComputed')
> FROM SYSCOLUMNS
> WHERE OBJECT_NAME(id) = 'TestComputed'
>
> John
>
> "Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
> news:fc254714.0307122105.31f3c396@.posting.google.c om...
> > Hi,
> > I'm on SQL Server 2000, say, I have a table named [orders], how I find
> > if there is any column which has a formula in it? In other words, how
> > to identify formula column programmatically? I've looked at
> > information_schema.columns view for clue but to no avail.
> > Thanks.|||Thanks, Erland.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93B8710A571B9Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> > Thanks, John, yes, I got it with a slight twist, which might have been
> > intended by you. In the WHERE clause I ensures that COLUMNPROPERTY(
> > id ,name, 'ISComputed') is true. Also, since ComputedColumn is
> > introduced in SQL 2000, I make sure to check server version before
> > using this property.
> As far as I recall, computed columns were introduced in SQL7.