How to rebuild SQL connection

  • Thread starter Thread starter Here There
  • Start date Start date
H

Here There

I have a program that runs without user intervention. As it monitors data, it
must request a single query from a SQL 2000 box. The problem is that the SQL
2000 box either keeps getting rebooted or the network link goes down. I have
written the entire connect-query-answer-disconnect into a single function to
try to help. It has helped but not perfect.

The outage is almost never more then 5 minutes. I wrote something the almost
works but looks awful.

How does one go about building a new function to take the outage and not error
out?

No, fixing the server or the network is not an option<G>.
 
You might go about creating a TRY-CATCH BLOCK inside a WHILE LOOP as in
the code snipplet below:

-------------------------------------------------------------------------------------------------------------------

private sub MonitorDB()

dim hasError as boolean = false

while hasError = true

try
Dim con as SQLConnection = ....
con.open
---DO WHAT EVER YOU WANT HERE----
hasError = false

catch
hasError = true

end try

end while

end sub
 
Wrap your code in try catch code and have it log errors to a file instead of
say using a window or messagebox.

Randy
 
Hi Here There,

I think neilphan's suggestion on use a Try-catch block to protect the
SqlConnection establishing code is reasonable.... You can use a limited
loop to try connecting that server for several times before you abort ....
Also, you can also set the SqlConnection.ConnectionTimeout value which
controls when to abort a connecting operation after certain period of time
(default is 15 seconds....).

Hope also helps. Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--------------------
| From: "(e-mail address removed)" <[email protected]>
| Newsgroups: microsoft.public.dotnet.languages.vb
| Subject: Re: How to rebuild SQL connection
| Date: 14 Dec 2005 10:36:10 -0800
| Organization: http://groups.google.com
| Lines: 30
| Message-ID: <[email protected]>
| References: <[email protected]>
| NNTP-Posting-Host: 63.167.91.4
| Mime-Version: 1.0
| Content-Type: text/plain; charset="iso-8859-1"
| X-Trace: posting.google.com 1134585375 32131 127.0.0.1 (14 Dec 2005
18:36:15 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Wed, 14 Dec 2005 18:36:15 +0000 (UTC)
| In-Reply-To: <[email protected]>
| User-Agent: G2/0.2
| X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
rv:1.7.5) Gecko/20041107 Firefox/1.0,gzip(gfe),gzip(gfe)
| Complaints-To: (e-mail address removed)
| Injection-Info: f14g2000cwb.googlegroups.com; posting-host=63.167.91.4;
| posting-account=9gVWQg0AAADsna_lET2GvPX35gp9RFRp
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
ne.de!border2.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.gigan
ews.com!postnews.google.com!f14g2000cwb.googlegroups.com!not-for-mail
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.languages.vb:308674
| X-Tomcat-NG: microsoft.public.dotnet.languages.vb
|
| You might go about creating a TRY-CATCH BLOCK inside a WHILE LOOP as in
| the code snipplet below:
|
|
----------------------------------------------------------------------------
---------------------------------------
|
| private sub MonitorDB()
|
| dim hasError as boolean = false
|
| while hasError = true
|
| try
| Dim con as SQLConnection = ....
| con.open
| ---DO WHAT EVER YOU WANT HERE----
| hasError = false
|
| catch
| hasError = true
|
| end try
|
| end while
|
| end sub
|
|
----------------------------------------------------------------------------
 
Back
Top