Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Location
    none
    Posts
    1

    Unanswered: sql 6.5 triggers to write to sql 2k database

    hi sql gurus,

    i have run into this problem where i have to use triggers residing on
    ms sql 6.5 database to updqte/insert table values on sql 2k database. i tried to use remote servers(only thing that 6.5 supports), and remote
    servers can support only running remote stored procedures. Is there a
    way i can set up a linked server from 6.5 to 2000? any other suggestions
    as to how to achieve this, wil really help..

    thanks in advance.
    Thanks,
    Nikhil.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    6.5 doesn't support linked servers so I think the only method would be to creatre the remote server and an SP on the 2000 side.
    Personally I think this was a good thing as it made people think about the boundaries between servers and the consequences of interfacing between them - you get a lot of developers now thinking that you can use a linked server just like another database on the local server.

    You could create a SP on the 2000 server which receives a sql string to execute - the trigger could then just build the string to call it.

    I would do this by inserting into a staging table(s) on the 6.5 server, transferring the data to the 2000 server (by a scheduled task on the 2000 server) and actioning it there. In this way if the connection to the 2000 server gets lost the 6.5 one still works, the 2000 will catch up automatically when it comes back and you get a record in the staging tables of what has happenned.

Posting Permissions

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