Nested transactions across databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Nested transactions across databases

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-11 : 13:48:52

copy and paste time

Create trigger CBP_Well_Resource_Comments_TD
on well_resource_comments
for delete
as
begin
select * into #temp_well_resource_comments_delete from deleted
delete from [dev-01].CBP.dbo.well_resource_comments where resource_comment_type in (select resource_comment_type from #temp_well_resource_comments_delete) and UWI in (select UWI from #temp_well_resource_comments_delete)
drop table #temp_well_resource_comments_delete
end

now, it no likey nested transactions

Server: Msg 7395, Level 16, State 2, Procedure CBP_Well_Resource_Comments_TD, Line 8
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]

Although I don't understand it.. I tried setting xact_abort on

Server: Msg 7306, Level 16, State 2, Procedure CBP_Well_Resource_Comments_TD, Line 9
Could not open table '"CBP_IDB"."dbo"."well_resource_comments"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

any clue on what I'd be doing wrong.. kinda perplexing me here.

-----------------------
Take my advice, I dare ya

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-11 : 16:11:32

Does well_resource_comments have a unique index ?

HTH
Jasper Smith

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-11 : 17:35:06

resource_comment_type and uwi combine to make the unique index

-----------------------
Take my advice, I dare ya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources