Failover ASP DB Connections

Hi All,

My site connects to SQL2K. I the event the DB isn't available (ie-timeout), I'd like to have the site connect to another SQL2K server on another network. What I have tried so far is testing the err object as follows. In the event of an err, I change the connection string and try to set my connection object with that string to a DB I know for a fact is available.

But this doesn't seem to work. Any ideas? Please take a look at my code and tell me what you think:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Open database connection

If IsObject(objConn) = False Then

strConn = "PROVIDER=SQLOLEDB;Password=***;Persist Security Info=True;User ID=***;Initial Catalog=DB1;Data Source=DBServer1"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Provider = "SQLOLEDB.1"
objConn.CommandTimeout = 3
objConn.ConnectionString = strConn
objConn.ConnectionTimeout = 3
objConn.CursorLocation = 3

objConn.Open
If err <> 0 Then

''''''''''''''''''''''''''''''''''''''''''''''
' Lets connect to another db

objConn.Close

'Should email admin for db switch here!
strConn = "PROVIDER=SQLOLEDB;Password=***;Persist Security Info=True;User ID=***;Initial Catalog=2;Data Source=DBServer2"

objConn.Open
If err <> 0 Then
Session("SystemMsg") = "Databases not available!" &amp; err.description
Session("SystemMsg") = "Sorry, All databases failed to connect!"
response.redirect("../System-Error.asp")
response.end
End If

' Lets connect to another db
''''''''''''''''''''''''''''''''''''''''''''''
End If

End If

' Open database connection
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

Its a bit off topic but since you are using sql server and I am on access database perhaps you could advise me on how easy/hard it is to upsize and what I need to think about.

[email protected]

cheers

 

maybe you need to clear your error generated by failure of database #1 otherwise you still have error <> 0?

 

Duh, I didn't even think of that! I'll give it a shot and post the result. I can't believe I forgot something as fundamental as that!


Thanks!

 

thanks once again.


I have done some reading up but can it be as simple as:-


1. upsize to sql server using access 2003


2. send resulting file to database folder on server


3. change connection string


I was hoping to talk to someone who had actually done it because I have always found reality to be somewhat different to all the books I have read.

 

If you are depending on Access queries, they are not converted to MS SQL stored procedures
as part of the upsizing. That is something you have to do manually and involves learning some new syntax.

That is really the only problem I recall from upsizing.

 

thanks scott,

I am using the database a pure tables - all sql queries are in my asp pages so maybe I will have an easy ride. That would be nice.