Trigger help to another server.

[mclifford]

I have a trigger that executes when the first name and last name and some other fields are updated. I have tested this to the two different databases on the same machine, but in production it needs to be updated on another server. But for some reason it will not resolve my voyager.OKAppsSecurity.dbo.UserMaster server at all. Does anyone have any hints at all? I have checked to make sure msdtc service is running and it is. I can run a simple select statment to the other machine and it workes correctly in the QA.

Here is my code.



Create Trigger UserMst_InsertUpdate On UserMst For Insert, Update
As
Begin
Set NoCount On

Update OKAppsSecurity.dbo.UserMaster
Set LName = i.LName,
FName = i.FName,
JobDesc = i.JobTitle,
Status = i.Status
From Inserted i
Where i.Login = OKAppsSecurity.dbo.UserMaster.AppUserName
And (OKAppsSecurity.dbo.UserMaster.LName <> i.LName Or
OKAppsSecurity.dbo.UserMaster.FName <> i.FName Or
OKAppsSecurity.dbo.UserMaster.JobDesc <> i.JobTitle Or
OKAppsSecurity.dbo.UserMaster.Status <> i.Status)


If Exists (Select Top 1
i.LName
From Inserted i
Left Join OKAppsSecurity.dbo.UserMaster um (NoLock) On
um.AppUserName = i.Login
Where um.AppUserName Is Null)
Insert OKAppsSecurity.dbo.UserMaster
(LName,
FName,
JobDesc,
Status,
EmployeeNo,
AppPassword,
AppUserName)
Select
i.LName,
i.FName,
i.JobTitle,
i.Status,
'888888',
'password',
i.Login
From Inserted i
Left Join OKAppsSecurity.dbo.UserMaster um (NoLock) On
um.AppUserName = i.Login
Where um.AppUserName Is Null

Update OKPC.dbo.PCShared
Set Status = i.Status
From Inserted i
Where OKPC.dbo.PCShared.Login = i.Login
End

[MAK]

did you create a linked server?

[mclifford]

Yes I did create a link.

[rmiao]

Did you get any error? What's os version on sql server? You need to enable network access for dtc on win2k3.