Write trigger to not use DTC/RPC

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 Administration (2000)

Write trigger to not use DTC/RPC

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2005-05-05 : 11:55:13

I have the following insert trigger on a table in Server1 that calls a stored procedure in Server2.

1. Why can I call this procedure on Server2 in QA while logged into Server1 without DTC, but it requires DTC when calling the sproc in a trigger?

2. Is there a way to rewrite this trigger so it will not use DTC? DTC is giving me problems.

This works fine within QA without DTC running...


USE Enovision
DECLARE @Today datetime
DECLARE @Result int
SET @Today = GETDATE()
EXECUTE [Server2].Enov_Dev.dbo.fj_create_monitor '101','EC001','This is a Test',@Today,@Result OUTPUT
PRINT @Result

Here's the trigger...


CREATE TRIGGER [Astea_Insert] ON dbo.TrackData 
AFTER INSERT 
AS
 
/* Call Stored Procedure in AsteaSQL Server - Ken Blum 5/4/2005
    Note that this trigger will only work for one row, 
    It will not work for batch inserts  */

DECLARE @Astea_Serial varchar(30)
DECLARE @Astea_Error_Code varchar(8)
DECLARE @Astea_Text varchar(254)
DECLARE @Astea_DT datetime
DECLARE @Astea_ErrorLevel int
DECLARE @Result int
DECLARE @ErrorVal int

SELECT @Astea_Serial = i.SerialNumber FROM Inserted i
SELECT @Astea_ErrorLevel = i.ErrorLevel FROM Inserted i
IF @Astea_ErrorLevel < 40 OR @Astea_Serial = 'REMTEST'
	RETURN

SELECT @Astea_Error_Code =i.ErrorCode FROM Inserted i
SELECT @Astea_Text =ErrorCodes.Description FROM Inserted i 
	LEFT JOIN MACHINES ON Machines.SerialNumber = i.SerialNumber 
	LEFT JOIN ERRORCODES ON ErrorCodes.Machine_Type = Machines.Machine_Type 
		AND ErrorCodes.ErrorCode = i.ErrorCode 
SET @Astea_Text =  ISNULL(@Astea_Text,'Unknown')
SELECT @Astea_DT = i.Time_Stamp FROM Inserted i

EXECUTE [Server2].Enov_Dev.dbo.fj_create_monitor @Astea_Serial,@Astea_Error_Code,@Astea_Text,@Astea_DT,@Result OUTPUT

Select @ErrorVal = @@ERROR

/* Record Error for Diagnostics */
IF (@ErrorVal != 0 OR @Result != 0)
	BEGIN
	INSERT INTO SysError (IDKey,IDKey2,RetVal,Msg) 
		Select i.TrackDataIDKey,i.DB2_Key,@ErrorVal,'ASTEAINS' 
		FROM inserted i
	END

TIA,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources