Solved

Check whether SQL Server connection is still open

Posted on 2010-11-26
6
250 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now