ETA ETA - 1 year ago 154
SQL Question

TCP Provider: The specified network name is no longer available

I am getting the following error every time i try to update a table on one server from data on another server "TCP Provider: The specified network name is no longer available". Here is the update statement:

update a
set a.vendorID = b.SubID
from ana.[FleetPlus].[dbo].[ServiceHistory] as a inner join ppcor.[subcontractors].[dbo].[Subcontractors] as b
on a.vendorID = b.FleetID

this update query hangs for about 5-10 mins and then gives me that error message. I can run a select statement on this, so i am confused. i thought it was a problem with my linked servers and i checked the SSCM and the Named Pipes and TCP/IP are enabled on both servers. This is only updating around 10k records so its not too many. any suggestions on what is going on or what else i need to check?

Answer Source

It looks like you have a problem with linked server.
Do not know which one, but run these two selects and see where you have that error:

SELECT TOP 1 1 FROM ana.[FleetPlus].[dbo].[ServiceHistory];
SELECT TOP 1 1 FROM ppcor.[subcontractors].[dbo].[Subcontractors];

Then you'd have to troubleshoot that linked server.

BTW That is extremely bad practice to link tables from different servers. That means you have to do a Full table scan on remote server, copy these data over the network, allocate memory on your SQL Server for these data and only then do a SELECT.