ASPX VB page losing database connection while running

Question

I have an IIS hosted web app written by a third party in VB. It connects to an SQL database using a provided DLL. While the application is running it will suddenly report an error: Missing Connection or ConnectionString

I can catch the error and fix the condition, but what would be happening that it would suddenly report this error?

What should I be monitoring to find the cause? Are their specific performance counters that might help figure out what is happening?

The vendor is at a loss but is being very helpful. There are no useful errors in the server event viewer.

I don't have the souce code for the DLL so I don't know how they connect using the provided connection string. Based on the error it looks like the DLL was written in DELPHI.

I have tried different database connection strings and providers (provider=SQLNCLI11, SQLOLEDB).

Here is the connection string that works, but occasionally errors out:

connectionString="provider=SQLNCLI11.0;server=192.168.8.66\INSTANCE;Initial Catalog=DATABASENBAME;User ID=XXXXX;Password=ZZZZZZ;Pooling=true;Max Pool Size=50;MultipleActiveResultSets=True;"

I've tried with and without pooling, with and without MARS. Always the same issue. If I remove the provier= at the beginning the application does not work. I've tried named instances and using IP address in the server name.

Answer 1

This sounds more like a timeout then a connection fail. And I doubt the connection information and pulling of data occurs from that external .dll.

I mean, open up web.config, and look at the connection's strings. Delphi at one time could generate .net CLR code, but that was changed a good many years ago.

code written in .net will ALWAYS

Open connection
    execute sql
close connection

Now, the above "sounds" like a bad performance idea, but there are two very significant reasons for above.

First up, code, variables etc for a user ONLY exists during a post-back. Once done, then the page class, users code variables etc. are disposed (removed from memory). This of course is due to the so called state-less nature of web software.

However, to re-gain performance, then IIS will cache and re-use those connection's. As a result, performance is not really impacted due to code having been written this way (always open connection, run sql, the close connection).

Also, it is doubtful that the delphi code would provide the sql provider(s) for the whole application. In other words, almost 99% for sure that the .net application going to use the .net sqlprovider. (not some 3rd party one). I mean, not even IIS security can work, and keep in mind web site security is provided by IIS and NOT the code written for the application!!!

Anyway, this sounds more like a query/sql timeout then it does like some connection being lost, since as noted, when A user is just sitting there looking at a web page? There connections are closed!!!!

They might THEN hit a shortcut to google, close hte lid on their laptop, or whatever. A web browser is thus dis-connected, and is "stand alone". It does NOT keep a live connection to the database, and as noted, the user might close the browser, browse to another page. The server DOES NOT know the user has done this.

So, each user just sitting and looking at some web page? There is ONLY one server and any of those users clicking a button sends that page to the ONE and SAME server. (the same server that caches all the connection's). All of those same users will in fact wind up use the same database connection - the ones that IIS caches for performance. And code written does NOT control this feature/issue!! (as noted, all such code will ALWAYS close the database connection after running a simple sql query).

seems to me, that increasing the connection timeout for sql server makes the most sense, and I suppose one could also modify the connection strings in web.config to see if that helps.

However, the real issue seems to be a query that takes too long to run, and that should be fixed/addressed. (not an attempt to mess or try and fix the web site connections).

so, check the web.config, and the connection strings.

You could say try setting the connection timeout in those strings.

 Connect Timeout=timeinseconds

EDIT: The above does NOT apply to command execution time.

So, to clear up confusing:

The conneciton timeout setting of a connection string is ONLY for the time (allowed) to establish a connection.

The time for a individual query? That is to be set by use of the sql command object, and that would be acheived on a query by query basis.

eg:

            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                cmdSQL.Connection.Open();
                cmdSQL.CommandTimeout = 30;

                rstData.Load(cmdSQL.ExecuteReader());
            }