Results 1 to 2 of 2

Thread: advice on linked server trigger insert

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    advice on linked server trigger insert

    Hi.
    This is complicated but I'll try to explain.
    We have a table in our main server that when new data is inserted in a specific table it initiates a trigger that point to a linked server that need to also get some fields.
    Lately I see a problem when a job (every 10 minutes job) runs and an insert is pending.

    "The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "VRAPPSRV" was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server "VRAPPSRV" returned message "No transaction is active.". [SQLSTATE 01000] (Error 7412). The step failed."

    After that, on the second run after 10 minutes the step will run again and succeed.

    Now the trigger is straight forward, a simple insert into 1 row every time. I'm guessing the link server is getting old (2003 server) and the data is not THAT big on the table to justify a delay.
    Currently there is no issue because as I've said the second run will insert the data but this is starting to happen a lot so I want to prevent a total breakdown if possible.
    What is peculiar is that if the first time there is a crash, the second time it will work smoothly.
    At a first glance what I can think of is that the Distributed Transaction Coordinator is somehow sleeps and not awake the first time and the second time it's already up and works. If that is the issue I will have to check with our admins because I don't have extensive knowledge on how to keep it alert.

    Any thoughts?

    Edit: I did a restart on the service and it seems to work faster. No sure it's fixed but I guess they will be importing more data tomorrow so I will know.
    Edit2: They passed some data today so the problem continues. The first time I get the error and then it works fine. I'm thinking more of a server DTC issue than SQL issue.

    Edit3: Since no one replied I did some more research. That looks promising but an admin must check it:
    https://learn.microsoft.com/en-us/tr...saction-ms-dtc
    Last edited by sapator; Jan 13th, 2023 at 05:49 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: advice on linked server trigger insert

    Hey sapa, check you network settings/logs. I recall having issues with linked servers due to firewalls blocking.
    Are these inserts happening on every 10 minute job execution or is the job running without doing inserts for a while and then, once it tries inserting for the first time failing? It could be that some network component is shutting down a port/route for beeing unused and then the first distributed transaction attempt fails but the component 'wakes up' so that the next attempt 10 minutes later works.

Posting Permissions

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



Click Here to Expand Forum to Full Width