Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error when updating over linked server Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 9:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:59 PM
Points: 1,376, Visits: 2,589
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].[table] AS a
WHERE NOT EXISTS(SELECT * FROM [localdb].[dbo].[table] 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
Post #1398485
Posted Wednesday, December 19, 2012 11:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:59 PM
Points: 1,376, Visits: 2,589
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
Post #1398582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse