Problem with linked server and nested transaction

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)

Problem with linked server and nested transaction

Author

Topic

francki17
Starting Member

4 Posts

Posted - 2006-01-06 : 15:18:13

Hi, I've got a problem when I try to update a row from a trigger in my table of a linked server. This is the context:

A server named Server1 has my DB with my table to update.

Another server named Server2 has another DB (security system of the building).

Server1 is a linked server in Server2.

Each time a reader reads an acces card, a row is updated in a table of Server2.

My goal is simple: When an employee pass his acces card on a reader to enter to enter i the building, his presence status (Away, Busy, Not at office, At office, etc.) is automatically updated in my table

So, I create a trigger on this table to detect each update and update the row (corresponding to the acces card's owner) in my table on Server1.

This is the trgger trigger:
---------------------------------------------------------------
ALTER TRIGGER myTrigger
ON theTableOnServer2
AFTER UPDATE, INSERT
AS

DECLARE @PersonID int

IF UPDATE(TheFieldWhoShouldBeUpdated)
BEGIN
-- Get the PersonID of the acces card's owner
SELECT TOP 1 @noPersonne = IDPersonne FROM INSERTED
-- Update in my table the status of the acces card's owner
UPDATE Server1.MyDB1.dbo.myTable1 SET Server1.MyDB1.dbo.myTable1.statusID = 'At office' WHERE personID = @personID;
END
-----------------------------------------------------------------

When I test the trigger, an error is launch ans this message appears

Server: Msg 7395, Level 16, State 2, Procedure monTrigger, Line 14
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.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

I don't really know now what to do with this problem, so if someone has an idea or a solution, I would appreciate it.

Francois

Version Francaise/ French version -----

Bonjour, j'ai un bogue lorsque je tente de mettre à jour une ligne dans une table d'un serveur lié à partir d'un trigger. Voici la situation:

Un serveur nommé Serveur1 contient ma BD avec ma tables à mettre à jour.

Un autre serveur nommé Serveur2 contient une autre BD (système de sécurité de l'édifice).

Serveur1 est un serveur lié dans Serveur2.

À chaque fois qu'un lecteur d'entrée lit une carte, une ligne est mise à jour dans une table de Serveur2.

Mon but est simple: dès qu'un employé passe sa carte sur un lecteur d'entrée pour entrer dans le bâtiment, que son statut de présence soit automatiquement mis à "Au bureau" dans ma table.

J'ai donc mis un trigger sur cette table pour qu'à chaque fois qu'une mise à jour est faite, que la ligne (correspondant au propriétaire de la carte) dans ma table sur Serveur1 soit mise à jour.

Voici le code du trigger:
---------------------------------------------------------------
ALTER TRIGGER monTrigger
ON laTableSurServeur2
AFTER UPDATE, INSERT
AS

DECLARE @noPersonne int

IF UPDATE(LeChampQuiEstSupposéEtreMisAJourL)
BEGIN
-- Obtient l'ID du propriétaire de la carte
SELECT TOP 1 @noPersonne = IDPersonne FROM INSERTED
-- Met à jour dans ma table le statut du propriétaire de la carte
UPDATE Serveur1.MaBD1.dbo.maTable1 SET Serveur1.MaBD1.dbo.maTable1.statutNo = 'Au bureau' WHERE personneNo = @noPersonne;
END
-----------------------------------------------------------------
Lorsque je provoque une mise à jour, une erreur est lancée et ce message s'affiche:

Server: Msg 7395, Level 16, State 2, Procedure monTrigger, Line 14
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.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

Je ne sais plus vraiment quoi faire avec ce bogue alors si quelqu'un a une idée ou encore mieux, une solution, elle serait grandement appréciée.

François

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 18:28:15

Try including
set xact_abort on

The problem with what you are doing is that if the destination server is down then the update on the source will fail.
Simpler to insert into a staging table on the source server and have a job on the destination reading it and applying the update. Also means that the destination process will be self recovering on database restore (if you are careful how you code it).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources