Error when updating over linked server

  • A procedure that has worked for year had just stopped working the other day and I have narrowed down the issue to an update over linked server although I have not yet found why it has stopped working.

    This is an example of the failing statement (real statement has many more joins on both remote and local dbs). Procedure works fine in development so I am not too concerned about the logic.

    UPDATE a

    SET a.enddate = GETDATE()

    FROM [SERVER].[database].[dbo].

    AS a

    WHERE NOT EXISTS(SELECT * FROM [localdb].[dbo].

    AS b

    INNER JOIN [localdb].[dbo].[table2] AS c

    ON b.id = c.id AND a.key = c.id)

    The error returned to SSMS is:

    OLE DB provider "SQLNCLI10" for linked server "SERVER" returned message "Communication link failure".

    Msg 10054, Level 16, State 1, Line 0

    TCP Provider: An existing connection was forcibly closed by the remote host.

    and the error in the log is:

    Error: 7393, Severity: 16, State: 1.

    The OLE DB provider "SQLNCLI10" for linked server "SERVER" reported an error 0x8000FFFF aborting the current transaction.

    I ran a profiler trace on the remote server while executing the statement and it seem to evaluate this part of the join a.key = c.id then stop without any wait locks or blocks. On the local server the statement is waiting on OLEDB. I have even ran a packet capture on the source without any indication of why it is failing.

    This is not executing as a distributed transaction. I thought it could be a DTC issue but DTC is not configured on the remote server.

    Is there anything else I could check or monitor to identify why it is failing?

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • I have found the problem. The remote server is a VM and the VM was recently move to a different host and it's virtual switch was changed from being a standard switch to a distributed switch. The distributed switch is what caused the issue. Similar to another problem i had posted about (http://www.sqlservercentral.com/Forums/Topic1394090-391-1.aspx)

    So it appears that Distributed switching in VMWare does not play nice with certain sql server processes.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

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