Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unhappy Unanswered: Select from one DB - Update in another

    Hi there.
    I have two databases, one called PWALive and one called Portal-Live.
    what I want to do is create an extract from Portal-Live of e-mail addresses and then use this date to update a table in PWALive.

    Here are the relevant tables

    Portal-Live
    tblMUD(MUD_ID, MUD_clock, MUD_email ... )

    PWALive
    employee(employee_number, email_address ... )

    Relationship: MUD_clock = employee_number

    My sql statement for the extract from Portal-Live.tblMUD looks like this:
    Code:
    SELECT	MUD_clock,
    	MUD_email
    FROM   	tblMUD
    WHERE	MUD_email IS NOT NULL
    AND 	MUD_clock IS NOT NULL
    I want to then update the correct employee in PWALive.employee:
    Code:
    UPDATE employee
    SET ... --Not entirely sure what to do here!
    WHERE employee_number = MUD_clock
    How can I do this in one sql sequence/is it possible?

    As I understand it I can use CONNECT TO but Icant get that to work, let alone know how to link the update with the first sql statement.

    Big thank you in advance

    -GeorgeV
    Last edited by gvee; 01-25-07 at 10:26.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    something like this:

    Code:
    update PWALive.dbo.employee
    set email_address = m.MUD_email 
    from PWALive.dbo.employee e
    join [Portal-Live].dbo.tblMUD m
    on e.employee_number = m.MUD_clock
    Note: this is untested. I don't write update...from's too often - always seem to forget the syntax. It's something like the above though...

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I ran this code as a test (didnt want to run a modify query until I'm 100% sure) while I was connected to the Portal-Live server

    Code:
    SELECT   employee_numebr
    FROM	 PWALive.dbo.employee e
    	 JOIN [Portal-Live].dbo.tblMUD m
    		ON e.employee_number = m.MUD_clock
    which gave me this error
    Code:
    Invalid object name 'PWALive.dbo.employee'.
    I'm sorry I can't be more use in my reply... I am completely lost on how to do this one.

    I've probably done something silly like getting the DB name wrong.

    -GeorgeV
    George
    Home | Blog

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    the query I wrote assumes you have these two databases on one server:

    Portal-Live
    PWALive

    do these both live on the same server? if not, you could use a linked server call to do the join.

  5. #5
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280

    The generation game

    @georgev
    If this is something you want to do one time or every few weeks or months by hand you can also generate the UPDATEs:
    Code:
    SELECT 'UPDATE employee SET email = ''' + MUD_email + ''' WHERE employee_number = ' + CAST(MUD_clock AS VARCHAR(15))
    FROM   	tblMUD
    WHERE	MUD_email IS NOT NULL
    AND 	MUD_clock IS NOT NULL
    Run this query from QA on Portal-Live, copy and paste the result in a new query window on PWALive. It's simple and you don't have to mess around with linked servers (handy when you're not sure how it works or you're not allowed to).

    @jezemine
    A tip on writing UPDATEs with a FROM-clause: I always use the alias from the FROM-clause after the UPDATE-keyword. This way it's clear which table you're updating
    Code:
    update e
    set email_address = m.MUD_email 
    from PWALive.dbo.employee e
       join [Portal-Live].dbo.tblMUD m on e.employee_number = m.MUD_clock

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Lexiflex
    @jezemine
    A tip on writing UPDATEs with a FROM-clause: I always use the alias from the FROM-clause after the UPDATE-keyword. This way it's clear which table you're updating
    thanks - good tip.

  7. #7
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by jezemine
    thanks - good tip.
    You're welcome! Also, I'm lazy and it saves me some typing

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by jezemine
    the query I wrote assumes you have these two databases on one server:

    Portal-Live
    PWALive

    do these both live on the same server? if not, you could use a linked server call to do the join.

    2 different servers I'ma afraid
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Lexiflex
    Run this query from QA on Portal-Live, copy and paste the result in a new query window on PWALive. It's simple and you don't have to mess around with linked servers (handy when you're not sure how it works or you're not allowed to).
    If only it was that simple
    This needs to be a complete process without user intervention.
    It's an overnight DTS so it needs to be fully automated and therefore as I understand it: all has to be done in one query.


    Thanks for all the suggestions though!

    -GeorgeV

    -------------------------------------------------
    EDIT:

    Code:
    SELECT	MUD_clock,
    	MUD_email
    FROM   	tblMUD
    WHERE	MUD_email IS NOT NULL
    AND 	MUD_clock IS NOT NULL
    Is there a way I could run this in the PWALive server if I changed the FROM clause to include a complete reference to the table?
    E.g. FROM servername.databasename.tablename

    Something like that?
    Last edited by gvee; 01-26-07 at 07:11.
    George
    Home | Blog

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by georgev
    Is there a way I could run this in the PWALive server if I changed the FROM clause to include a complete reference to the table?
    E.g. FROM servername.databasename.tablename

    Something like that?
    Yes, you need to create a linked server on PWALive to the machine where tblMUD lives.

    See BOL on linked servers: http://msdn2.microsoft.com/en-us/library/ms190479.aspx

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by jezemine
    Yes, you need to create a linked server on PWALive to the machine where tblMUD lives.

    See BOL on linked servers: http://msdn2.microsoft.com/en-us/library/ms190479.aspx
    Thank you for the suggestion but I cannot get this to work. When I parse the code "The command(s) completed successfully." but when I run it I get:
    User does not have permission to perform this action.
    I use this code:
    Code:
    EXEC sp_addlinkedserver   
       @server = 'IIS-LIVE-IT046', 
       @srvproduct = '',
       @provider = 'SQLNCLI', 
       @datasrc = 'Portal-Live'
    I'm afraid I just don't follow what to do
    Sorry for moaning.
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, I have found a solution to my problem without doing the sp_addlinkedserver thingumy...

    Select >> update temp table >> update from temp table to live >> POW.

    It works, which is the main thing - but I'm still too into this linkedserver problem to let it drop, so come on SQLers - what's the answer! :P
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2002
    Location
    Warsaw, PL/Oslo, NO
    Posts
    2
    If you can run the sp_addlinkedserver ok, and the

    User does not have permission to perform this action.

    message comes from running the update xxx query, then it is "simply" a question of log-on credentials to the remote server.

    You can see the credentials used and options in EM, go to <MyServer>\security\Linked Servers\<MyLinkedServer> and, if you are not using integrated security, verify that the account used have proper access on the remote server.

    HTH

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by f_eriksen
    You can see the credentials used and options in EM, go to <MyServer>\security\Linked Servers\<MyLinkedServer> and, if you are not using integrated security, verify that the account used have proper access on the remote server.
    Please see attached image.
    We use windows authorisation as the log on credentials.
    This problem seems to be bigger than it should be - all I want is to perform a select from one database on one server and update the details from the select into the other.

    Thanks for all the replies

    -GeorgeV
    Attached Thumbnails Attached Thumbnails EM.bmp  
    George
    Home | Blog

  15. #15
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by georgev
    but I'm still too into this linkedserver problem to let it drop, so come on SQLers - what's the answer! :P
    Have you tried the examples from the BOL:

    This example creates a linked server named SEATTLESales that uses the Microsoft OLE DB Provider for SQL Server.
    Code:
    USE master
    GO
    EXEC sp_addlinkedserver 
        'SEATTLESales',
        N'SQL Server'
    GO
    This example creates a linked server S1_instance1 on an instance of SQL Server, using the OLE DB Provider for SQL Server.
    Code:
    EXEC    sp_addlinkedserver    @server='S1_instance1', @srvproduct='',
                                    @provider='SQLOLEDB', @datasrc='S1\instance'

Posting Permissions

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