Update a tbl with data from a foreign sql server database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Update a tbl with data from a foreign sql server database

  1. #1
    Gill Guest

    Update a tbl with data from a foreign sql server database


    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


  2. #2
    Gill Guest

    Re: Update a tbl with data from a foreign sql server database


    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
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center