Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Check whether SQL Server connection is still open

Posted on 2010-11-26
6
Medium Priority
?
257 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 1500 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

610 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