Re: What to do when you can't retrieve content from a DB
- From: thewinchester <michael.harris@xxxxxxxxxxxxx>
- Date: Tue, 19 Aug 2008 00:36:33 -0700 (PDT)
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...@xxxxxxxxxxxxxxx>
wrote:
thewinchesterwrote:
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.
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.
--
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"
.
- Prev by Date: Please advise why the following code only giving empty string no u
- Next by Date: RE: #INCLUDE VIRTUAL
- Previous by thread: Please advise why the following code only giving empty string no u
- Next by thread: Problem with Stored Procedure
- Index(es):
Relevant Pages
|
Loading
