Receiving Transport error when querying SQL Server 2008 R2 msdb.dbo.sysjobhistory from newer version of SSMS.

Question

In SSMS 2012 through latest version of SSMS, when trying to view SQL Server 2008 R2 SQL Agent job history on one specific job the SSMS SQL Agent log viewer window gives the following error:

TITLE: .Net SqlClient Data Provider

---

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

---

ADDITIONAL INFORMATION:

The specified network name is no longer available

---

Also, any query run in SSMS 2012+ on msdb.dbo.sysjobhistory that includes the 'message' column returns this similar error:

Msg 64, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

The query runs successfully from SSMS 2008 locally on the server. I'm assuming there is something in the nvarchar message column that newer versions of SSMS don't support. I've gone through all of the message values for this specific job on the 2008 server and don't see anything unusual. Have any of you heard of this happening and possibly a way to resolve it?

Many thanks in advance!
Sumac

Answer 1

It is much more likely your server is doing a stack dump rather than a problem with SSMS. Look at the SQL Server Error logs

Please make sure you have the last Service Pack installed:

https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an

Answer 2

Tom may be correct, but I do have a different hinch.

Try this: Backup the msdb database on this instance and copy it elsewhere and restore at as a plain user database on that server (which should be something more modern). Use SSMS whichever version locally on that server on the table, with the message column. Then try SSMS connected on a different machine.

So I don't know, but my guess is that it will work when you access it locally, but not when you connect remotely - at least not if you are still on the same network. Because this has the smell of a network sniffer/security program that looks for dangerous things and cuts the connection when it sees something "dirty".

Answer 3

Hi @Sumac,

Did you try to restart SQL server service if you can do that?

As Tom mentioned, suggest you apply SP3 for SQL server 2008R2. You can get it from here.

Did the network is table?

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet