[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

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
0
Mateen
Asked:
Mateen
  • 2
  • 2
1 Solution
 
MateenAuthor Commented:
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
0
 
Melih SARICACommented:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

It is required that XACT_ABORT be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions

hope this explanation helps


as u see there is an error with one ur Statements in ur proc..
0
 
MateenAuthor Commented:
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.
0
 
Melih SARICACommented:

i guess no in this scene..
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now