How to rebuild SQL connection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Here There - 14 Dec 2005 17:27 GMT

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>.

Reply to this Message

neilphan@gmail.com - 14 Dec 2005 18:36 GMT

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

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

Hope this helps.

Reply to this Message

Steven Cheng[MSFT] - 15 Dec 2005 03:44 GMT

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

Signature

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

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

| From: "neilphan@gmail.com" <neilphan@gmail.com>
| Newsgroups: microsoft.public.dotnet.languages.vb
[quoted text clipped - 8 lines]
| 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: groups-abuse@google.com
| NNTP-Posting-Date: Wed, 14 Dec 2005 18:36:15 +0000 (UTC)
| In-Reply-To: <ezpX7ONAGHA.2736@TK2MSFTNGP11.phx.gbl>
| 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: groups-abuse@google.com
| 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()
|
[quoted text clipped - 16 lines]
|
| end sub

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

| Hope this helps.

Reply to this Message

Randy Cragin - 14 Dec 2005 22:40 GMT

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

Randy

> 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
[quoted text clipped - 9 lines]
>
> No, fixing the server or the network is not an option<G>.

Reply to this Message

How to rebuild SQL connection

Free Magazines