MSDE Connection Issues

[Guest]

Hi,

I am working on a C# .net 1.1 project in which there is a search box. As
the user types, it refreshes the results list based on LIKE %% query to an
MSDE database (in this case on the local machine). Initially, I implemented
it with a single-threaded, but eventually for performance reasons I decided
to add in a seperate worker thread to perform the actual search. So if a
user types a character while the worker thread is still running, I call
Abort() on the thread. The function that the worker thread catches the abort
exception and closes the OdbcConnection database connection. It then starts
a new thread. This allows the user to type quickly without having to wait
for all of the results to be displayed.

So the wierdness that I am running into only happens if the user types
quickly (presumably resulting in a lot of thread terminations/restarts).
After about 20 rapid keystrokes, the OdbcConnection will fail to connect to
the MSDE 2000 database and return the following error:

[2800][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'sa'

Any subsequent keystrokes will cause this same error, regardless of how long
I let it sit. The only way to get it working again is to close and re-open
the whole application. What's going on here? I'm pretty sure I'm cleaning
up all of my OdbcConnections and closing the OdbcDataReader. I also tried
using SqlClient instead of Odbc and I get the same thing. I am not using a
trusted connection.

Any ideas?

Thanks,

Andrew Bragdon

 

[Gary Chang[MSFT]]

Hi Andrew,

Currently, we are looking into this problem. We will update you as soon as
we get anything out.


Thanks for your understanding!

Best regards,

Gary Chang
Microsoft Community Support
--------------------
Get Secure! ¡§C www.microsoft.com/security
Register to Access MSDN Managed Newsgroups!
http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.asp
&SD=msdn

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Huang [MSFT]]

Hi

Firstly I do not recommend to abort a thread, common we make a thread exit
normally(e.g. exit a loop).
Also I think it is not a good design to open and close a db connection too
frequent.
For your scenario, I think you may try to call the GC.Collect and
GC.WaitForPendingFinalizers to make sure the gc will do the actually
resource release.
Also I think you may try to enable the trace on the sql server side about
the login failed reason and the current connections count when failed. For
how to troubleshooting in SQL server you may try to post in the group below.
microsoft.public.sqlserver.programming


Based on my experience, for your scenario, a keep running thread is enough.
The thread will keep trying to pull a keyword from certain array or
string(if there is only one keyword) and do the search, if there is no such
one, it can just sleep. In this way we can save a lot of CPU time on thread
create/destroy, connection create/destroy.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.