Update a tbl with data from a foreign sql server database

[Gill]

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

[Gill]

Hi All,
I figured that out thanks! I had to go to the security folder and create
a linked database so that in my sql I include the server name like
select *
from servername.databasename.owner.table

even though I had tried this it was because I didnt have them linked that
it didnt work!

I hope this might help someone else sometime!

thanks again

Gill
(new sql server user!)



"Gill" <maloneygillian@hotmail.com> wrote:
>
>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
>