?
Solved

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

Posted on 2003-03-19
23
Medium Priority
?
3,037 Views
Last Modified: 2010-03-04
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
Comment
Question by:sfotadar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 3
  • +3
23 Comments
 
LVL 7

Expert Comment

by:chisholmd
ID: 8169104
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
 

Expert Comment

by:WebDevAng
ID: 8169545
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
 

Author Comment

by:sfotadar
ID: 8169552
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
Technology Partners: 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!

 

Author Comment

by:sfotadar
ID: 8169568
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
 

Expert Comment

by:WebDevAng
ID: 8169582
Yes, my connections are all in global.asa and work perfectly.
0
 

Author Comment

by:sfotadar
ID: 8169889
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
 

Expert Comment

by:WebDevAng
ID: 8169947
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
 
LVL 4

Expert Comment

by:hanneman
ID: 8172207
>>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
 

Author Comment

by:sfotadar
ID: 8174647
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
 
LVL 4

Expert Comment

by:hanneman
ID: 8174840
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
 
LVL 9

Expert Comment

by:rherguth
ID: 8188435
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
 

Expert Comment

by:esells
ID: 8189655
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
 
LVL 9

Expert Comment

by:rherguth
ID: 8190223
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
 

Author Comment

by:sfotadar
ID: 8196041
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
 
LVL 9

Expert Comment

by:rherguth
ID: 8196421
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
 
LVL 4

Expert Comment

by:hanneman
ID: 8196533
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
 

Author Comment

by:sfotadar
ID: 8198524
Hi rherguth :
Could you send me the sample of this. I think this might solve my problem.

0
 
LVL 9

Accepted Solution

by:
rherguth earned 150 total points
ID: 8201324
<%@ 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
 
LVL 9

Expert Comment

by:rherguth
ID: 8201379
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
 

Author Comment

by:sfotadar
ID: 8205350
Thanks for your solution. For now this solution seems to solve my problem.
0
 
LVL 9

Expert Comment

by:rherguth
ID: 8206844
Why only a grade of B and 5 points?  Did the code not work the first time?
0
 

Author Comment

by:sfotadar
ID: 8211287
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
 
LVL 9

Expert Comment

by:rherguth
ID: 8211805
Ok, good luck then.
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface This article introduces an authentication and authorization system for a website.  It is understood by the author and the project contributors that there is no such thing as a "one size fits all" system.  That being said, there is a certa…
Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
The viewer will learn how to dynamically set the form action using jQuery.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question