• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3041
  • Last Modified:

Making an connection to database in global.asa. Whenever the SQL server is restarted the connection becomes invalid.

Hi ,
I am making a connection to SQL server database in global.asa(Application_onstart). Whenever the SQL server is restarted the connection becomes invalid and then I have to restart the webserver manually in order for the application to work. The web server and SQL server are on different system. Any ideas why is it happening and what should I do to correct this.

thanks,
suman
0
sfotadar
Asked:
sfotadar
  • 8
  • 7
  • 3
  • +3
1 Solution
 
chisholmdCommented:
I never use global.asa especially for connection objects.  I make a small asp file to set-up the connection object and then include it in the pages that need it or nested in my top include.

I am not sure but I think setting up your connection object in the global.asa is similiar (if not exactly) like setting it up in a session variable whihc is not reccomended.

dave
0
 
WebDevAngCommented:
Sfotadar,
This works for me:
Sub Session_OnStart
     
     Session("www_ConnectionString") = "Provider=SQLOLEDB.1;Password=(Enter your pswd);Persist Security Info=True;User ID=(SQL user name or service goes here);Initial Catalog=(name of db);Data Source=(server your db is on)"    
End Sub

Hope it helps.
0
 
sfotadarAuthor Commented:
I agree. I cannot make this change in my application since the application is already live and this change would require lot of maintenance effort. Is there any way to solve this problem without moving the connection object from global.asa file.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sfotadarAuthor Commented:
Thanks WebDevAng  for your comment. Are you also creating  the connection  object in global.asa? If yes is it working for you ? I am also using the same string to make connection.


0
 
WebDevAngCommented:
Yes, my connections are all in global.asa and work perfectly.
0
 
sfotadarAuthor Commented:
Is your IIS and database on the same system or on the different system. In my case IIS and Database server are on different system.
One observation ..why are you storing connection string in session object. That is the one difference I observe between ur code and my code. And also I am specifying the timeout property in the connection string.
0
 
WebDevAngCommented:
I am also specifying the timeout in the string.  I just forgot to include it.  My apologies.

My IIS and the SQL database are on separate servers.

I use session for other functions so it is most appropriate under the session for my purposes.  I am running multiple web applications with different databases.
0
 
hannemanCommented:
>>Are you also creating  the connection  object in global.asa?

WebDevAng doesnt appear to be creating the connection object in global.asa, just declaring the connection string.
0
 
sfotadarAuthor Commented:
Yeah I am creating the connection object in the global.asa. From the code WebDevAng has provided that it seems WebDevAng is storing connection in session object. Well do you have any idea why I am facing this problem ? Any suggestions...
0
 
hannemanCommented:
How many files would you have to alter to take the connection object out of global.asa? I think you will find it is the best way.

You might try limiting the scope of the connection object to the session:

------ global.asa ------

<OBJECT RUNAT=Server SCOPE=Session ID=oConn
PROGID="ADODB.Connection">
</OBJECT>

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

Many also say that putting connections into session are a bad idea, because it is a complete waste of server resources...

http://www.learnasp.com/learn/globalproblems.asp

http://www.microsoft.com/windows2000/en/server/iis/default.asp?url=/windows2000/en/server/iis/htm/asp/iiwaobu.htm
0
 
rherguthCommented:
It is a bad idea to store the connection as an *object* as either an Application or Session var because IIS then needs to queue all requests for the connection and wait for the thread the connection is on to become available.

Storing the connection string to an Application var is not a problem and will not cause an error when the DBMS is rebooted because the connections are made each time a page is generated that contains the connection.  If that is too much overhead, use a middle tier object that maintains a connection.  I have found establishing the connection once per page to be completely workable.
0
 
esellsCommented:
I think your problem is VI defaults to a Application("") variable, it only gets set when the web server on your local system gets started.
The easy way is to create a little page that you can run and copy the Application("conname") = "your connnection"
line from the global ASA so you can run it or put it at the top of your default.asp page so it resets each time someone runs the application, it's a little more overhead but will solve your problem.
Hope I get the points, it will be my first!
0
 
rherguthCommented:
esells

The connection object immediately becomes invalid *wherever* it is when the server is rebooted.  That will not happen when using the built-in VI connections because they store just a connection string, not an object.

sfotadar

The only fix I can think of for your application objects would involve more work than the two lines required to fix it the right way.

In each page:
<object id=oConn runat=server progid=ADODB.Connection></object>
<%
' ADO Initialization
     oConn.Open Application("sCRS_ADO_Conn")
%>

In Global ASA, Application_onstart:
Application("sCRS_ADO_Conn") = "Provider=SQLOLEDB;Data Source=<servername>;Persist Security Info=False;Connect Timeout=30;APP=<Your App Name>;Initial Catalog=<DBName>;User ID=<userid>;Password=<pwd>;"
0
 
sfotadarAuthor Commented:
Thanks guys for your comments. The problem I face right now is that the application is already live and I am not in position to add connection object initialization code on each page since there are more then 500 pages. So in order to fix this problem we have decided to move database and the IIS on the same system. That way whenever database is restarted IIS will also get restarted and we won't face this problem again.
0
 
rherguthCommented:
Another option would be to setup an admin page (.asp) that has the META refresh tag in it and is open on the web server all of the time.  It would check to see whether the connection is valid and if it is not, would try to redo the connection and resave the new connection to the Application object.  Let me know if you would like to see a sample of this, because I think it would work.
0
 
hannemanCommented:
How about writing a script that uses file system object to write an include statement at the top of every file?
Im not sure, but isnt having the db on the web server a nice big security hole?
0
 
sfotadarAuthor Commented:
Hi rherguth :
Could you send me the sample of this. I think this might solve my problem.

0
 
rherguthCommented:
<%@ Language=VBScript %>
<%Option Explicit%>
<html>
<head>
     <title>ADO Connection Administrative Keep-alive Test Page</title>
     <meta http-equiv="Expires" content="-1">
     <META HTTP-EQUIV="refresh" content="10;URL=TestConnection.asp">
</head>

<body>
<%
     On Error Resume Next
     Application("CRSConn").Execute "SELECT @@VERSION"
     If Err.number <> 0 Then
          Response.Write "Connection is invalid, Connection was reset."
          Err.Clear
          Set Application("CRSConn") = Server.CreateObject("ADODB.Connection")
          Application("CRSConn").Open Application("sCRS_ADO_Conn")
     End If
     On Error Goto 0
%>
This page normally refreshes every 10 seconds...<br>
</body>
</html>
0
 
rherguthCommented:
The code above worked on my test system, where the SQL Server is on a different computer than the IIS server.

Application("CRSConn") = your connection *object* defined in the Global ASA

TestConnection.asp = the name of the page

Application("CRSConn") = the connection *string* - I just happen to store it in Global ASA.  You could just substitute the connection string.

The connection timeout and the page refresh are two different intervals, however, this doesn't seem to cause any trouble because the page doesn't seem to exec the refresh until after the connection times out and is reset.  So normally, the connection is tested every ten seconds, but will take longer when the connection is no longer valid and the timeout period needs to expire.

I tried using the connection .State property and connection .Errors object, but they just didn't do the job as well as execing a bonehead query.  The query can be anything.
0
 
sfotadarAuthor Commented:
Thanks for your solution. For now this solution seems to solve my problem.
0
 
rherguthCommented:
Why only a grade of B and 5 points?  Did the code not work the first time?
0
 
sfotadarAuthor Commented:
I never assigned you 5 points . I am new to this website and I don't know how 5 points were assigned. I liked your solution and it worked but my PM does not want to go with this solution.
0
 
rherguthCommented:
Ok, good luck then.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 7
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now