Solved

Check whether SQL Server connection is still open

Posted on 2010-11-26
6
254 Views
Last Modified: 2012-05-10
I am connecting to my SQL Server on my web hosting account from Excel VBA as shown below.

I need to keep the connection open because it takes about 10 secs to connect each time. (That, I hope is ok)

So I want to check whether the connection is still open when the user requests another query. I tried this ... to try and understand it

    If CBool(objConn.State And adStateOpen) Then
        MsgBox "Connection is still open"
    End If

But sometimes when it returns TRUE the connection code below still takes that 10 secs - which I interpret to mean the connection is closed!

Question: what is a good way to check whether the connection is actually closed (so I can prompt a reconnect)?
Thanks!
 
0
Comment
Question by:hindersaliva
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 3

Expert Comment

by:GSGDBA
ID: 34218776
Hi Hindersaliva,

I am confused with requirement of connection being kept open.
Would you please explain, why you are required to do so?

The reason i ask you is this, Connections should be closed after completion of a transaction.
Otherwise it have impact on your SQL Server.
0
 

Author Comment

by:hindersaliva
ID: 34218909
Hi,
When a SELECT query is run the data comes to Excel in about 1 sec (very quick). This is great. But the connection takes 10 secs each time preceding that. Therefore I thought an answer is to keep the connection open for about 20 mins through successive queries - ie. connect on startup so the long wait is only once. I tried it and it worked well (although my web hosting co may have an issue with that). If and when the connection drops (say reaches web host's limit) I need to check and prompt the user to reconnect, otherwise he/she will click the button to retrieve the data and have to wait (unexpectedly) long.

I figured it would be a bad thing from DBA viewpoint.

If there is another way of avoiding the disproportionately long time to connect that's really what I'm after, if possible.

Thanks for any help.
0
 
LVL 3

Accepted Solution

by:
GSGDBA earned 500 total points
ID: 34219238
I understand your point.

Before going into database level.
Would you please tell me, the network speed, what is the ttl time when you ping the SQL Server from Public and Private networks.

And regarding the query;
Try to open the connection before the transaction.
And Try to close the connection immediately after the transaction.

Exa:
refer the below link.
http://support.microsoft.com/kb/168336

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:hindersaliva
ID: 34221502
Thanks GSGDBA,

I see what you mean - the time to connect to the SQL Server (10 sec) maybe unusually long? I shall check this as you suggest and report back.
0
 

Author Comment

by:hindersaliva
ID: 34225312
Update ...
I have abandoned this idea.
It looks like my attempt at isolating the problem was faulty. I now know a lot more about 'tracert' and how to troubleshoot such issues - without jumping into hasty conclusions!

Thanks to my hosts (www.crystaltech.com) for their help.

Conclusion ... keeping the connection open is not necessary and is a bad idea anyway!
0
 

Author Closing Comment

by:hindersaliva
ID: 34225318
GSGDBA's answer showed me that I was on the wrong track.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question