What to do when you can't retrieve content from a DB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

thewinchester - 10 Jul 2008 06:42 GMT

I've got something bugging me and as i'm not a hard core programmer
its got me beat - anyone who can point me in the right direction or
help me solve it would be my hero for at least the next five minutes.

Ok, here's the scenario - a corporate intranet, where selected pages
have parts of information that come from a database such as news
articles or an events calendar. Due to the geographic spread of the
organisation, the physical ASP pages are mirrored out to the remote
sites via DFS and the address for the intranet has a local alias which
accesses the copy from the local IIS server.

However, when the SQL server goes down or the sites lose their
internet connectivity, pages pulling dynamic content will return
errors. Ideally, when the page loads and it's unable to connect to the
DB, it would skip running the code, repeats, etc and instead return a
message in place such as "DB not available, try again later".

SQL connection strings for pages are available from both web.config
and an include file.

My questions:

1) How do I stop the SQL queries from running in the event that
communication with the SQL server is not possible.
2) How can the loss of connection be detected, so that the static
portions of the page display as normal and the dynamic data-driven
content displays an error message in place instead of trying to run
the code and causing SQL OLE DB provider errors?

And a bonus question:

3) What's the best way to mitigate for connection loss in this
environment? Should I be looking at caching the output of these parts
on a frequent basis and reading these in each time the page is loaded,
and what's the best method to do it.

Thanks in advance.

Reply to this Message

Bob Barrows [MVP] - 10 Jul 2008 14:05 GMT

> I've got something bugging me and as i'm not a hard core programmer
> its got me beat - anyone who can point me in the right direction or
[quoted text clipped - 20 lines]
> 1) How do I stop the SQL queries from running in the event that
> communication with the SQL server is not possible.

Wait a minute, you seem to be contradicting yourself. You said above that
the pages would return errors. Now you are implying that the sql queries are
attempted to be run even when the errors occur? Not possible. This sounds
like a case for simple error trapping:

on error resume next
cn.open ...
if err<> 0 then
'display connection problem message
else
'execute your sql
if err <> 0 then
...
end if

> 2) How can the loss of connection be detected, so that the static
> portions of the page display as normal and the dynamic data-driven
> content displays an error message in place instead of trying to run
> the code and causing SQL OLE DB provider errors?

See above.

You should not be attempting to "stay connected" as your "loss of
connection" phrase implies. Your procedure should be:

when a page loads, attempt to open the connection
if successful run your code
close your connection as soon as you can

If you are "losing connection" in the few ms or seconds that your
server-side code is processing, then you have real network issues that your
IT people need to take care of.

If your server-side code takes longer than a few seconds to process, then i
suggest you look at optimizing what you are doing. Consider using GetString
or GetRows to allow you to quickly pull your data into a string or array,
allowing you to close your recordset and connection immediately before
processing the data in the string or array.
http://www.aspfaq.com/show.asp?id=2467

If that is not feasible, and you need to keep your recordset open for
lengthy time periods, use client-side, disconnected recordsets:

set rs=createobject("adodb.recordset")
rs.cursorlocation=3 'adUseClient
rs.Open sql, cn,,,1
set rs.activeconnection = nothing
cn.close
'process the recordset

> And a bonus question:
>
> 3) What's the best way to mitigate for connection loss in this
> environment? Should I be looking at caching the output of these parts
> on a frequent basis and reading these in each time the page is loaded,
> and what's the best method to do it.

Caching is certainly doable. You can use either Application or Session
variables.
http://msdn2.microsoft.com/en-us/library/ms524664.aspx

Or write content to a text file on the web server, refreshing it
periodically.

It all depends on how often that content changes, and how often these
problems occur. Caching consumes server resources, so it's a balancing act
deciding how much data to cache.

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

thewinchester - 19 Aug 2008 08:36 GMT

Thanks for that response, sets me right back on track. Oh for the love
of learning through mistakes.

On Jul 10, 9:05 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:

> thewinchesterwrote:
> > I've got something bugging me and as i'm not a hard core programmer
[quoted text clipped - 93 lines]
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Reply to this Message

Webmaster Forum / ASP / Database Access / August 2008

What to do when you can't retrieve content from a DB