Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    60

    Unanswered: Updating a record on a SQL 6.5 from data in SQL 2000 Server

    I need to update one row in a SQL Server 6.5 DB from a row in SQL 2000 server DB. What would be the best way to do this?


    I have my 2000 server defined as a Remote Server in 6.5, however I get the error message:

    contains more than the maximum number of prefixes. The maximum is 2.
    jaraba

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would define the SQL 6.5 server as a linked server on the SQL 2000 box then do the update from the SQL 2000 box using four part names.

    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    60
    Did that, is telling me that the SQL 6.5 is not configured for data access.
    jaraba

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Did you use sp_addlinkedserver which would allow data access, or sp_addserver which would only allow RPC calls?

    -PatP

  5. #5
    Join Date
    Oct 2003
    Posts
    60
    sp_addserver, for that matter, How do I make a an update (or insert) using an RPC call.

    note: work briefly as DBA for 6.5, thus lack of knowledge in this area.
    jaraba

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Drop the 6.5 remote server from the 2000 box, then add it back as a linked server. That will allow you to do the update using a four part name from the SQL 2000 box, making life a lot easier.

    You can do the update using RPC calls, but it is ugly. I wouldn't go there.

    -PatP

  7. #7
    Join Date
    Oct 2003
    Posts
    60
    I would do it, but we have a lot of legacy code that uses RPC Calls
    jaraba

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Painful...

    How about this....

    Write a file of "transactions" (DML statements) and place it on the 6.5 server.

    Have the 6.5 server have a job that would execute thos statements from that file every n minutes, and the delete the file....

    I HATE dealing with legacy crap code....

    PS They're still building the same crap today....just got thrown on another one...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Instead of doing direct action queries define a stored procedure(-s) on 6.5 and call it/them from 2K.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jaraba
    I would do it, but we have a lot of legacy code that uses RPC Calls
    When you get data access, you implicitly have RPC as well.

    -PatP

Posting Permissions

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