Occasionally slow to connect to a sql2005 database

Hi

We have a VB app talking to SQL 2000 and SQL2005 databases. Most of our clients use SQL2000, but the new ones we are deploying with SQL2005

Sometimes the app is slow in establishing a connection to the database when it is SQL2005. The provider is sqloledb. We never get this delay in SQL2000. Sometimes it takes seconds to establish a connection - this is even the case when the app is on the same machine as the database.

We are using SQL authentication - always with the same connect string.

Any ideas ?

thanks
Bruce

Hi

Any chance of a reply on this one ?

Thanks

Bruce

|||

I have the same problem, Win32::SqlServer perl-module to connect to MS Sql Server 2005 in some cgi-scripts.

They run on the same server as the DBMS.

It must be related to the authentication method -- it is slow when the username/password is set explicitly and hence SQL authentication is forced.

If I run the scripts manually with Integrated Security (default) it connects in no time (give or take a few microsec.). Unfortunately this is not possible when run through IIS which I guess is because it doesn′t have the required permissions.

So... is it possible to give permission on a file-basis (in this case a cgi-script) so that an IIS-initiated process can access MS Sql Server with integrated seciruty?

This may not be the solution to original posters problem but it might give a hint in the right direction.

|||Same here, VB app connects to SQL Express using SQL Authentication. Using an identical connection string each time I get intermittent occurence of this slow/not responding symptoms.

It can sit and wait for a simple query to execute for maybe 30 secs or more in many cases. I had thought the Visual Studio Process had been hogging resources but this has now occured in our release builds (But not as often). This problem is replicated across our development machines.

AutoClose is set to False
AutoShrink is set to False

The query has only failed to execute on one occasion, unfortunately on a customers machine where presumably the query had timed-out. In most other cases the query has executed fine but after an impossibly long wait.

Is the server idling? If so how can I best test for the idle condition or alter the parameters for idle condition.

Cheers
R