Solved

Check whether SQL Server connection is still open

Posted on 2010-11-26
6
249 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:hindersaliva
Comment Utility
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
Comment Utility
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
Comment Utility
GSGDBA's answer showed me that I was on the wrong track.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

762 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

8 Experts available now in Live!

Get 1:1 Help Now