Solved

Check whether SQL Server connection is still open

Posted on 2010-11-26
6
252 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 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