Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    14

    Red face Unanswered: Problems updating using two databases

    I was wondering if someone can tell me what I am doing wrong in this query.
    (A lot of things, I suspect.)
    We have an HRMSInfo table in the database HEAT_External that continually gets updated data.
    When a ticket is opened the table Assignee in the database HEAT_Test looks at that information once, but if the HRMSInfo is ever updated the Assignee table never reflects the change.
    I am trying to get the Assignee table to reflect the updated HRMSInfo table but not having any luck.

    Here is what I came up with so far.
    Code:
    use HEAT_Test;
    go
    Update heat.assigneeBAK set assignee = HEAT_External.dbo.HRMSInfo.Full_Name, 
    GroupName = HEAT_external.dbo.HRMSInfo.Department,
    Phone = HEAT_external.dbo.HRMSInfo.PhoneNumber,
    Pager_Number = HEAT_external.dbo.HRMSInfo.Pager 
    where HEATLogin = HEAT_External.dbo.HRMSInfo.SWID
    When I check the query's syntax I get the message that everything is okay, but when I execute I get the following message:

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "HEAT_External.dbo.HRMSInfo.SWID" could not be bound.


    I'm testing on SQL 2005, but Production would be SQL 2000.
    Thanks for any help.

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Where's the join ?

    ie

    FROM heat.assigneeBAK prod INNER JOIN HEAT_External.dbo.HRMSInfo INFO OM prod.whatever = INFO.whatever

  3. #3
    Join Date
    May 2009
    Posts
    14
    I guess I don't know exactly how to perform the join.

    The tutorials I have seen show how to perform joins when you are just displaying data but I haven't seen anything that includes an update.

    I've tried some of it myself but I just got "incorrect syntax" errors.
    How would I include a join to create the update on assigneeBAK from the data on HRMSInfo?

    I've tried using the below code:
    Code:
    use HEAT_Test;
    go
    Update heat.assigneeBAK 
    set assignee = HEAT_External.dbo.HRMSInfo.Full_Name, 
    GroupName = HEAT_external.dbo.HRMSInfo.Department,
    Phone = HEAT_external.dbo.HRMSInfo.PhoneNumber,
    Pager_Number = HEAT_external.dbo.HRMSInfo.Pager 
    FROM heat.assigneeBAK as hlogin INNER JOIN HEAT_External.dbo.HRMSInfo as SWID
    on hlogin.HEATLogin = SWID.SWID
    But I just get the "The multi-part identifier HEAT_external.dbo.HRMSInfo.Pager" could not be bound." for each expression that includes it.

    I guess I need to declare an alias or something for each category -or some similar concept that I don't have my mind completely around yet.
    Last edited by Smasher; 06-12-09 at 18:28.

  4. #4
    Join Date
    Jun 2009
    Location
    CA, USA
    Posts
    59
    Try the following...

    Update bak
    set bak.assignee = hrm.Full_Name,
    bak.GroupName = hrm.Department,
    bak.Phone = hrm.PhoneNumber,
    bak.Pager_Number = hrm.Pager
    FROM heat.assigneeBAK prod
    INNER JOIN HEAT_External.dbo.HRMS hrm ON bak.whatever = hrm.whatever
    where bak.HEATLogin = hrm.SWID
    MohammedU
    SQL Server MVP

  5. #5
    Join Date
    Apr 2009
    Posts
    15
    Ok might require a different tack here. If you are wanting to update across servers you need to add them as linked servers.

    Im guessing this is why you are getting the bound error message...

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This doesn't appear to be across servers, merely across databases within the same server.
    If this is the case, then
    Code:
    UPDATE heat.assigneeBAK 
    SET    assignee = alias.Full_Name 
         , GroupName = alias.Department
         , Phone = alias.PhoneNumber
         , Pager_Number = alias.Pager 
    FROM   heat.assigneeBAK As hlogin
     INNER
      JOIN HEAT_External.dbo.HRMSInfo As alias
        ON hlogin.HEATLogin = alias.SWID
    George
    Home | Blog

  7. #7
    Join Date
    May 2009
    Posts
    14
    Quote Originally Posted by gvee
    This doesn't appear to be across servers, merely across databases within the same server.
    If this is the case, then
    Code:
    UPDATE heat.assigneeBAK 
    SET    assignee = alias.Full_Name 
         , GroupName = alias.Department
         , Phone = alias.PhoneNumber
         , Pager_Number = alias.Pager 
    FROM   heat.assigneeBAK As hlogin
     INNER
      JOIN HEAT_External.dbo.HRMSInfo As alias
        ON hlogin.HEATLogin = alias.SWID
    Yes, it is two databases on the same server.
    And thank you very much -this very code did the job as is.
    I went ahead and changed 'alias' for clarity but this did just what I needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •