Re: Communication Link Failure Error
- From: Tahir Sultan <TahirSultan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 20 Nov 2006 07:21:02 -0800
I eventually managed to find a BETTER way around it. Here are the changes
which I made to my code:
1. Changed the connection steing to use native OLE DB as follows:
Provider=sqloledb;Data Source=AAA;Initial Catalog=BBB;User Id=CCC;Password=DDD
where AAA = SQL Server name
BBB = database name
CCC = login user name
DDD= password
2. I declare a list which holds pending SQL request.
3. hook up ADO connection object's Connectioncomplete and Disconnection
events.
4. when execution of a SQL query failes, an exception is raised---catch this
exception. check the list of pending requests. if it is empty then add this
failed request into the list and disconnect from the database. (this will
force to disconnect from an invalid connection if there is any). Do not
attempt to disconnect from the database if there is already a pending request
in the list.
5. I have a separate thread which attempts to connect to the database. once
I have got connection to the database, i suspend that thread.
6. in disconnection event handler, I check if there is an error while I
tried to disconnect from the database. If there is NO error then I resume the
thread which attempts to connect to the database.
7. in connection event handler, I check if there is an error while I tried
to connect to the database. If there is NO error then I start a timer which
checks pending request list and if there is any pending request, send it to
the SQL server and then empty the list.
Regards,
Tahir Sultan
"Tahir Sultan" wrote:
The tcp/ip protocol is at the top of the list. Enabling/disabling connection.
pooling did not make any difference anyway. I also try to use OLE DB instead
of using ODBC driver but it did not work.
But then I tried the following workaround which worked pretty well:
1. On disconnection from the network, in my code I changed the connection
string to use SQLOLEDB and sent a SQL query to SQL server which failed as
expected.
2. On reconnection to the network, in my code I changed the connection
string back to {SQL Server} and sent a SQL query to SQL Server which
succeeded.
I know it is not very good solution but I tested this many times and it
seemed to work.
Thanks a lot Bob for putting me in right direction.
Tahir
"Bob Barrows [MVP]" wrote:
Does the problem occur when you use the native OLE DB provider for SQL
Server instead of the deprecated OLE DB provider for ODBC databases?
See http://www.aspfaq.com/show.asp?id=2126
Is the DSN using named pipes or tcp/ip? If it is using named pipes, try
forcing it to use tcp/ip.
Also, it is a bad idea to disable connection pooling (or session pooling as
it is known in OLE DB parlance) except in very specific circumstances. Why
have you disabled pooling?
Tahir Sultan wrote:
I am using ADO in my application to connect and retrieve data from
Microsoft SQL Server 2000 SP4. When I start my application, it
establishes connection to the server successfully and I am able to
retrieve data from the server. here is my test scenario:
1. unplug the network cable from the client machine and put the cable
back in after some time. I disconnect from the database in my code
when the client machine disconnects from the network
2. Once the client machine comes back on the network, I try to
reconnect in my code and retrieve data from the sql server but I get
the following error message when I try to execute a sql command:
[Microsoft][ODBC SQL Server Driver]Communication Link failure
This problem occurs only with Window XP SP2. With XP SP1 it works
fine. It seems to be problem on the client side as with Windows XP
SP1 I do not get this error message at all.
Here are the various details:
1. The firewall is off on the client workstation.
2. the connection string is:
driver={SQL Server};server=AA;uid=BB;pwd=CC;database=DD
where AA = DNS name of server machine where sql server is running.
BB = user name
CC = password
DD= database name
3. The client is remote to the server.
4. I can ping the server machine when the client machine reconnects
to the network.
5. I can still telnet the server when the client machine reconnects
to the network.
6. client database provider is MDAC odbc.
7. the client and server machines are in the same domain.
8. Shared memory and TCPIP protocols are enabled and TCP is at the
top of the list.
9. No alias is being used.
10. Virus scan is not installed.
11. force encription is NOT checked.
12. The connection pooling is also disabled for SQL server odbc
driver under Connection Pooling tab of ODBC Data Source Administrator.
Any thoughts would be appreciated.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
- Prev by Date: Re: ADO corrupts date time values in where clause
- Next by Date: lengthy operation
- Previous by thread: Check out the code for filtering ADO recordset
- Next by thread: lengthy operation
- Index(es):
Relevant Pages
|
|
