asked on

xact_abort sql server 2000.

I hope somebody is here on Saturday

alter TRIGGER [insert_into_export_table_colour] ON dbo.colour
FOR INSERT
AS

begin
set rowcount 1
insert export.dbo.colour(colour_code,colour_name)
select colour_code,colour_name from inserted
insert loomdata.looms_monitor_data.dbo.colour(colour_code,colour_name) --problem line
select colour_code,colour_name from inserted --problem line
set rowcount 0
end

<< Cannot start more transactions on this screen. Unable to start a nested
transaction for .... A nested transaction was required because xact_abort is set off>>

Here loomdata is a linked server created in the current group of sql server.
The database loom_monitor_data actually resides in another group of sql server.

I have tested running directly in query analyzer in current group
insert loomdata.looms_monitor_data.dbo.colour(colour_code,colour_name)
select 'tt','test' from inserted
It is inserting record in loomdata.loom_monitor_data

Mateen

ASKER

Althoug I have manged to solved the problem but I didnt know what it was all about.

I had to issue
set xcat_abort on
and after processing
set xcat_abort off

ASKER CERTIFIED SOLUTION

Melih SARICA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mateen

ASKER

Hi non zero:

I have changed my trigger as

alter TRIGGER [insert_into_export_table_colour] ON dbo.colour
FOR INSERT
AS

begin
set rowcount 1
insert export.dbo.colour(colour_code,colour_name)
select colour_code,colour_name from inserted
set rowcount 0
end

begin
set xact_abort on
insert loomdata.looms_monitor_data.dbo.colour(colour_code,colour_name)
select colour_code,colour_name from inserted
set xact_abort off
end

It seems to be working fine.

Please confirm me that I have done the right thing and "xact_abort on" will not have any side effect.

Melih SARICA

i guess no in this scene..