A transport-level error has occurred when sending the request to the server.

I have 3-4 VB2005.NET applications in beta testing where I use SQL Express as my database.  One of those applications has 4-5 steady users on a MS Small Business server where the SQL Express is installed.

I have ONE connection string I use for all users as shown below where MYID & MYPASSWORD are replace with a user id & password I have embedded:

connectionString = SQLLocationString & "\SQLEXPRESS;Initial Catalog=" & glDBString & ";Connection Timeout=360;User ID=MYID;Password=MYPASSWORD;"

I get this transport-level error sporatically from some of the users:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I'm wondering if it has to do with my users leaving the application open for any length of time and then attempting to perform a function thereafter.  I don't think it occurs if they continue to use it because of the jpg image my errorhandler emails me when they get an error.

Is my way of logging in with a single ID & Password improper?  Or should I be accounting for something else in my code to eliminate the possibility of this occuring.

Phil Tate
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Do you have auto-close enabled for the database? If so, try turning this off. This option basically shuts down the database after the last connection disappears. It does start up again but cause cause these timeouts in the process of doing so.

To find out do select is_auto_close_on from sys.databases where name='<your_db_name>'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TSFLLCAuthor Commented:
Yes...  ALL of my databases are set that way.  It apparently defaults to TRUE in the creation process.  I have not changed any of the default options on any of my databases.

I have just begun to get my feet wet in terms of SQL Management even though I have several fairly elaborate databases I use now.

If it shuts down the database, wouldn't ALL of my users be unable to connection at a specific given time?  And also, I think all of them keep the VB frontend up and running pretty much all day.
Well I haven't use the setting myself but I know it's designed for machines with very low memory or memory shared with other apps. It's definitely one to turn off though

No - it doesn't offline the database it just shuts down all connections. New ones will start up ok but if lots of new ones try at this point at the same time may be that causes the error.

Try turning it on and see what happens. It shouldn't hurt anything.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

In the app are you trying to leave a connection open the whole time the app is running?  I would open and close every time you need to talk to the database.
I have been experiencing these issues recently and my research shows that these errors occur when an open connection gets dissconnected(for any reason and it may be while you are still using the connection; loosing the network connectivity is one reason) and you try to use it.
TSFLLCAuthor Commented:
Having investigated this MUCH further in the last few days, the cause of it is apparent, but a resolution from Microsoft is long overdue.

I made the change to the registry last week immediately after you posted it.  I have not received another error associated with it so far, but it has only ben a cuple of days.

I'm going to 'play it by ear' for the next week and we'll see what the outcome is.

Anthony PerkinsCommented:
>>Having investigated this MUCH further in the last few days, the cause of it is apparent, but a resolution from Microsoft is long overdue.<<
I hate to break it to you, but I would not hold your breath:  If the network is dropped all MS should do is report the error.  What would you expect MS to do?  Re-establish the connection automatically using the same credentials?  Surely you do not expect that.  That would be a security flaw to say the least.
I very much doubt that they will change it (and quite frankly I hope they do not), but if you feel strongly about it you should contact them through their Feedback website and make your case.  Who knows you may be lucky.

Incidentally you can easily duplicate the problem from SSMS by connecting to SQL Server,  after the connection is lost try and run a query.
TSFLLCAuthor Commented:

>>If the network is dropped all MS should do is report the error.<<  Why should the network connection be dropped in the first place?  Being intimately involved with this customer previously as a network manager and now as a software vendor, knowing the quality of their network, and also knowing that they do not have problems with mapped drives dropping, word/excel documents being open (and unedited) for hours...unlike my application being open in a similar fashion.... Why would VB.NET & SQL Server pose such a problem?

My issue is that that it should re-connect with the same credentials (security would be an issue without saying).  My issue is why it occurs in the first place when this is an extremely stable TCP network.
Anthony PerkinsCommented:
>>My issue is why it occurs in the first place when this is an extremely stable TCP network.<<
That is a very good question and one that you should investigate further.  I suspect you can purchase a good network tool that should provide the answer to that.
TSFLLCAuthor Commented:
I haven't received any more errors from this one client since changing the setting, but I'm holding my breath.  Because of that I will go ahead and gives points instead of leaving open for an extended period of time.
TSFLLCAuthor Commented:
Although this posting is closed, further investigation shows that ALL of my clients losing connection sometimes daily, sometimes not.  Regardless, I know that ALL of my clients don't have bad TCP networks.  This is totally a MS issue.

And, unfortunately, from reading 40+ subsequent postings of an original posting on another site MS only acknowledged an issue and that engineering of this piece had changed between .NET 1 & .NET 2.

They didn't however come up with a fix........
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.