if statement

[jacob94]

i run a job that does a simple select count(*) command to test if I have connectivity between my linked server.

could i issue a disable trigger command if i get an error on the select statement above during my job, if so how?

 

[philhege]

You could evaluate @@ERROR to see if the statement is successful.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[SQLSister]

You would not ever want to disable a trigger if you lost connectivity, you wopuld want the actions to rollbnack or else you would have data integrity problems inthe furture where refords inthe other db were not inserted or updated when they should have been. You want to use @@error and rollback if there are any errors.

Questions about posting. See faq183-874

 

[jacob94]

thanks for the responses. I am using the set --SET xact_abort ON and when the middle server goes down it affects the main servers speed of inserts on the table with the trigger. i am not sure why it slows down but I want to disable the trigger if the linked server goes down. data integrity is not an issue in this case.

i can't see to get the sql statement to run.

if @@error <> 0
begin
print 'everything is ok'
end
else
begin
begin distributed transaction
SELECT count(*) FROM linked servername.dbname.dbo.tblTable
commit tran
alter table diable trigger code
end

can anyone help?

 

[philhege]

@@ERROR = 0 indicates success.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[jacob94]

i understand but my code does not work with the begin distributed transaction...i think it has to do with the linked server or something. can you show me how to structure it?

 

[philhege]

...does not work..."

What's the error?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.