I would appreciate any ideas on this. It has us really confused. The issue seems to be environmental so we are going to host this one process elsewhere, but it is driving me crazy that I don't know WHY there is an issue. The key mystery is that Select works, but Update doesn't.
- MS SQL Server 2005 inside private network.
- 2003 Server .Net 2.0 IIS server in DMZ
- Port 1433 is open between the two. (Test with all ports open created the same results.)
- Software FireWalls are both disabled.
- SQL Management console exhibits same error on connect.
- Connections from within the private network and elsewhere in the DMZ work.
- Process worked for years then stopped on the 17th. The only visible difference being a IE8 KB update.
We are receiving the Error:
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.)
Connection open works without error. Connection string is passing Server and Instance for the DB. Created a new user with same results. If the wrong user is specified an invalid user response is sent back.
With System.Data.SqlClient.SqlConnection - Select Statements Work - ExecuteScaler() and ExecuteReader() - Update Statements give above error - ExecuteNonQuery()
Using ODBC works for both Select and Update.
SQL Tracing shows select commands but nothing for the SQLConnection Update commands.
Assuming the issue was DNS related, we had also replaced Server name with IP address. Again, both Named and IP connections allow Select but not Update.
When attempting with SQL Management console from the IIS machine, the SQL Trace shows that some dialog starts then the Management console gives the same transport error. So it doesn't seem like the issue is with IIS.