Post reply

DTS updates

  • July 18, 2005 at 9:30 am

    #165005

    Hi all,

    I have 2 linked servers - crm & sage

    I want to update information, using a DTS from one server (sage) to the other where changes have occured.

    For instance - update crm.account set balance = sage.balance from sage.dbo.account sage where sage.customer = crm.customer and sage.balance <> crm.balance

    How is this UPDATE DTS achieved - I can easily APPEND or DROP,CREATE and IMPORT this but am finding it rather difficult to check for one value in the one server against the other, then just UPDATE the values.

    Hope this makes sense

    Mark

  • July 18, 2005 at 10:53 am

    #575252

    Since the two servers are linked, there is no need to go through DTS.

    You need to use the four part name to reference the linked server.

    So, your query will read something like this. I am assuming that you are in the server called crm.

    update crm

    set crm.balance = sage.balance

    from TableName crm

    join LinkedServerName.LinkedDatabaseName.dbo.LinkedTableName sage

    on crm.customer = sage.customer

  • July 18, 2005 at 11:25 am

    #575266

    Hi Sara,

    I understand what you say but the problem is i MUST use a DTS - the crm system i run has an OLE DB that when a DTS passes through it, it generates remote data files.

    Any other offers for strictly DTS solution for this ?

    Mark

  • July 18, 2005 at 11:33 am

    #575268

    Will this work for you?

    Create a DTS job with two steps.

    1. populate a table in CRM of the values you need to change.

    2. run a stored procedure to update your balances based on this staging table.

    3. truncate the staging table.

    I do not know what remote data files need to be generated, but they can probably get generated by this job

     

    Sara

  • July 18, 2005 at 4:12 pm

    #575349

    Have you tried using data driven task?

  • July 21, 2005 at 2:17 am

    #575991

    Thanks Sara - think I know what you're getting at - could be an answer.

    Journeyman - I've tried the data driven task but can't quite figure out the update & lookup steps to look at customer abc from server1 and UPDATE customer abc on server2

    Many thanks people for your input - will work on the Sara suggestion for now.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply