July 12, 2013 at 2:30 pm
We are having some connection issues with several items, but I am going to post just one and hope that it puts me on the right path.
I am running a query that pulls data from a couple of linked servers. It has always ran fine but is now returning this:
OLE DB provider "SQLNCLI10" for linked server "mainserver" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI10" for linked server "mainserver" returned message "Communication link failure".
Msg 65535, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI10" for linked server "mainserver" returned message "Communication link failure".
Msg 65535, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI10" for linked server "mainserver" 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.
I started with saying that there is a network error, but the network people say that the network is fine (of course) and that it is an authentication problem. I don't see how. I am trying to find something solid to take back to the network people.
Any help is appreciated.
July 12, 2013 at 2:46 pm
Quick and dirty.
Setup a continual ping to the servers.
Run the query
Do you see network packets dropping? If so, that is good proof that there is something wrong. Not definitive, but something to go on.
On the other side of the coin, run a query using the link server for something minimal and show them that it isn't an authentication problem.
Fraggle
July 12, 2013 at 3:13 pm
Googling your error message it sounds like LOTS of people have had this issue. It seems to be predominantly happening with broadcom NICs. The solution in those cases seems to be consistent too, disable tcp chimney offloading.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2013 at 3:15 pm
Thanks. Ya I found those posts and checked those settings. They were not the problem.
July 12, 2013 at 3:17 pm
OK. That was a great idea. I started the continuous pings and ran the query. I watched the pings get slower and slower. Then I saw one packet drop on each ping string. Then the pings got good and the query finished. Now all other problems are gone as well....
I don't know if the timing was coincidental or what...????
July 12, 2013 at 3:17 pm
robert.wiglesworth (7/12/2013)
Thanks. Ya I found those posts and checked those settings. They were not the problem.
It does seem to be something with connectivity. Can you setup a link server to the same remote server from another machine? Does that one work correctly?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2013 at 3:21 pm
Well, right now things are working again. The phantom error fixed itself situation... I hate that. No one will admit they changed something.
If it starts up again, I will try this. thanks.
July 12, 2013 at 5:34 pm
There maybe useful info in the SQL and Windows error logs on the remote server.
it may give you an idea of where in the stack the issues was.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply