Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error when updating over linked server


Error when updating over linked server

Author
Message
Robert klimes
Robert klimes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1847 Visits: 3408
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
Robert klimes
Robert klimes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1847 Visits: 3408
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search