Hi all,
I am trying to update a table with data from a database held in a different
server group. I am using a trigger and it works fine for a database that
is in the same server group but when the database is outside the grou I am
getting an error saying that the objects in the from clause are invalid and
the folowing error comes up in the results when I run it in query analyser
without the trigger bit: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Here is the code I am using and any help at all will be really appreciated:

CREATE TRIGGER sysdba.OPPLOOKUP ON sysdba.OPPORTUNITY
AFTER INSERT, UPDATE AS
declare @pms_data Table(
oppid varchar(12),
Pms_id varchar(30),
Project_ID varchar(30),
pname varchar(50),
pDescription varchar(254),
Methodology varchar(100),
CaseOfficer varchar(50),
ActualStart datetime,
RequiredEnd datetime,
ActualEnd datetime,
location varchar(30),
division varchar(30)
)

Insert into @pms_data
Select

op.OPPORTUNITYID,
cp.pms_ID,
p.accountcode,
p.name,
p.description,
m.name,
ui.userid,
p.actualstart,
p.requiredend,
p.actualend,
la.stringvalue,
d.stringvalue
From
pms.dbo.tr_request p,
pms.dbo.tr_method m,
pms.dbo.tr_role r,
pms.dbo.tr_requestaccess ra,
pms.dbo.tr_resource co,
pms.dbo.tr_charac c,
pms.dbo.tr_requestcharac rc,
pms.dbo.tr_characvalue la,
pms.dbo.tr_charac dc,
pms.dbo.tr_requestcharac drc,
pms.dbo.tr_characvalue d,
sysdba.OPPORTUNITY op,
sysdba.USERINFO ui,
sysdba.C_PMS_Project cp,
inserted newrec
where
p.methodid = m.methodid
and ra.requestid = p.requestid
and ra.roleid = r.roleid
and ra.resourceid = co.resourceid
and rc.requestid = p.requestid
and rc.characvalueid = la.characvalueid
and la.characid = c.characid
and drc.requestid = p.requestid
and drc.characvalueid = d.characvalueid
and d.characid = dc.characid
and op.OPPORTUNITYID = newrec.OPPORTUNITYID
and cp.OPPORTUNITYID = op.OPPORTUNITYID
and CAST(ui.USERNAME AS nvarchar) LIKE CAST(co.name AS nvarchar) COLLATE
SQL_Latin1_General_CP1_CI_As
and CAST(cp.PMS_ID AS nvarchar) LIKE CAST(p.accountcode AS nvarchar)
Collate Latin1_General_CI_AS

Update sysdba.OPPORTUNITY
SET
sysdba.OPPORTUNITY.DESCRIPTION = pmsd.pname,
sysdba.OPPORTUNITY.SUMMARY = pmsd.pdescription,
sysdba.OPPORTUNITY.TYPE = pmsd.methodology,
sysdba.OPPORTUNITY.ACCOUNTMANAGERID = CAST(pmsd.caseofficer as varchar(12)),
sysdba.OPPORTUNITY.CREATEDATE = pmsd.actualstart,
sysdba.OPPORTUNITY.SUBTYPE = pmsd.division,
sysdba.OPPORTUNITY.REASON = pmsd.location,
sysdba.OPPORTUNITY.ESTIMATEDCLOSE = pmsd.requiredend,
sysdba.OPPORTUNITY.ACTUALCLOSE = pmsd.actualend
from
sysdba.OPPORTUNITY op
inner join @pms_data pmsd on op.OPPORTUNITYID = pmsd.oppid