Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    9

    Unanswered: Linked server insert problem

    Hi,

    I've just upgraded our main server to Windows 2003 Server / SQL 2000
    from NT4/SQL2000.

    We have a secondary server, running NT4/SQL7, which does a remote update to the primary server using this kind of query:

    INSERT [MainServer].Database.dbo.UpdateTable (Product_ID, Branch_ID, Qty) VALUES (@a, @b, @c)

    This query works fine when you run it from Query Analyer, but as soon as i place this query in a begin/commit transaction block, as soon as i run it, the query hangs, and the only way i can get it to stop is by resetting SQL server.

    I have tried doing this update from/to a different combination of OS's and SQL server versions, but it only seems to hang when the query is done to a W2003S/SQL2000 machine.

    Does anyone have any suggestions? (Besides rolling back to W2K server)

    Cheers,

    Josh

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    I have found issues where if you run from QA all runs fine, but from a job etc it dies.

    Are you using linked servers & if so how is the link configured?

    How do you communicate to the remote server?

    Is the Distributed Transaction Service running on the remote server?

    Cheers,

    SG

  3. #3
    Join Date
    Jul 2002
    Posts
    9
    I've found the problem.

    The only way i could find it was installing another W2003S/SQL2000 machine, and running the query from there to the primary server.

    It actually gave me an error message, which after investigating, found that DTC needs to be enabled for network access under W2003S. Why this doesn't have this enabled by default i don't know, but it's workin now!

    Cheers,

    Josh

Posting Permissions

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