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

ASP and SQL problem with sessions - Lose server temporarily.

We have a site w/shopping cart on Verizon's MySuperpages.com (don't ask). It was on Verizon (there's a difference appearently).

I didn't write it but I gotta fix it. On the new server when you go to the main page, index.asp, sometimes it doesn't come up and sometimes it does. Don't know if has anything to do with people using the cart or not.. I don't imagine there are more than 2 or 3 (if any) people on at any one time.  Code below:

index.asp basically says

include   SQL.asp   at the very top (which is also on all of the other pages)
include   links.asp
some html bs

and the SQL.asp is as follows, i think this is where the prob is.

SQL.asp (please remember i didn't write this, but could this asp be locking up the server intermittantly?)

<%

connectionInfo = "Provider=SQLOLEDB;UID=XXXXXX;PWD=XXXXX;Data Source=XX.XXX.XXX.XXX;"
'connectioninfo = "Provider=SQLOLEDB;UID=Matt;PWD=XXXXXX;Data Source=dsorce;Initial Catalog=WEBDB"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString=connectionInfo
Conn.Open

If session("ID") = "" Then
'*******************************************************      
'This sections returns the new Session ID

      cmdText = "Select * From primary_key"
      set rs = server.CreateObject("ADODB.recordset")
      rs.open cmdText, conn
      session("ID") = rs("sessions")      
      set rs = nothing
'******************************************************
'This section updates the new ID in the primary key table

      cmdText = "Select * From primary_key "
      set rs = server.CreateObject("ADODB.recordset")
      With rs
            .ActiveConnection = conn
          .CursorType = 2 'adOpenDynamic
          .LockType = 3 'adLockOptimistic
            .Open cmdText
      End With
      rs("sessions") = session("ID") + 1
      rs.update
      set rs = nothing      
'**********************************************************
'This is where the timestamp and ID gets set in the temp_IDs

      cmdText = "Select * From temp_IDs"
      set rs = server.CreateObject("ADODB.recordset")
      With rs
            .ActiveConnection = conn
          .CursorType = 2 'adOpenDynamic
          .LockType = 3 'adLockOptimistic
            .Open cmdText
      End With
      rs.addnew
      rs("ID") = session("ID")
      rs("timestamp") = Date
      rs.update
      set rs = nothing      
'********************************************************
'Here I check to see if the session number is more than
'1 day old and if it is I will delete it from the DB.

      cmdText = "Select * From temp_IDs"
      set rs = server.CreateObject("ADODB.recordset")
      rs.open cmdText, conn
      
      Do While not rs.eof
            strDateDiff = DateDiff("d",rs("timestamp"),Date)
            If strDateDiff > 1 Then
                  cmdText = "Delete From orders Where SessionID = " & rs("ID")
                  set rsDelete = server.CreateObject("ADODB.recordset")
                  rsDelete.open cmdText, conn
                  set rsDelete = nothing

                  cmdText = "Delete From addresses Where ID = " & rs("ID")
                  set rsDelete = server.CreateObject("ADODB.recordset")
                  rsDelete.open cmdText, conn
                  set rsDelete = nothing

                  cmdText = "Delete From payment Where ID = " & rs("ID")
                  set rsDelete = server.CreateObject("ADODB.recordset")
                  rsDelete.open cmdText, conn
                  set rsDelete = nothing
                  
                  cmdText = "Delete From userinfo Where ID = " & rs("ID")
                  set rsDelete = server.CreateObject("ADODB.recordset")
                  rsDelete.open cmdText, conn
                  set rsDelete = nothing

                  cmdText = "Delete From temp_IDs Where ID = " & rs("ID")
                  set rsDelete = server.CreateObject("ADODB.recordset")
                  rsDelete.open cmdText, conn
                  set rsDelete = nothing
            End If
            rs.movenext
      Loop
      set rs = nothing


      session("REFER") = request.servervariables("HTTP_REFERER")
      session("ADDR") = request.servervariables("REMOTE_ADDR")      
      Session.Timeout = 120
      
'      cmdText = ""
'      set rs = server.CreateObject("ADODB.recordset")
'      rs.open cmdText, conn
      
      
      
End If
%>

0
HypercubeTech
Asked:
HypercubeTech
3 Solutions
 
Clever_BobCommented:
Its pretty hard to 'lock' a server to the extent that nobody else can view the site.

I'd say its more likely that the site suffers from a viewability issue - e.g. some browsers will deliver the response fine and others will crash. Are there any components that require java, cookies etc?

If you test the site with the same browser over and over is there an issue? If so, try changing the browser settings until it fails.
0
 
HypercubeTechAuthor Commented:
I haven't tried to access the site since i posted this about an hour ago.. now it times out and gives this

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

/includes/SQL.asp, line 81



hehe ok now i hit refresh and the site comes up fine..  i hit an item to add to cart, (which the link is on the secure.mysuperpages.com site)
and now it times out again.
0
 
HypercubeTechAuthor Commented:
now i hit refresh again on the page with the timeout expired error, and its added to the cart
0
Independent Software Vendors: 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!

 
Joe JenkinsCommented:
Hmm.  That error is caused by a query timing out .. How long before you get that message?  Let's see your add to cart query or segment of code.  That could be your issue.  Outdated versions of MDAC can cause this or a poor server name lookup can prolong query time on systems where the database server is not the same as a the webserver because of a faulty switch, poor cabling or bad routing tables.  You might bounce this off the Verizon Support people since they can shed some more light on the back end configuration.  Let them know this was fine before the switch.

Joe Jenkins
0
 
kssoftwareCommented:
I ran a large Govt. database and had a lot of trouble over the years with a similar problem.  Joe is right - it is often due to out of date MDAC components although no matter when we upgraded the MDAC, the problem persisted.  Usually when this happened it was due to a lock happening on one of the tables.  You might try recasting your loop into a stored procedure, as this can help overcome the problem - you have opened your recordset as dynamic and also delete one of its records while in the loop ("delete from temp_IDs where..." - bypassing the recordset).  Here's a sample stored procedure (you might want to add some error handling, etc).  You would call it like this:
     adCmdStoredProc = &H0004
     cmdText = "spSessionRemove"
     set oCMD = server.CreateObject("ADODB.command")
     oCMD.CommandText = cmdText
     oCMD.CommandType = adCmdStoredProc
     oCMD.ActiveConnection = conn
     oCMD.Execute
     set oCMD = nothing
---------------------------------------------------
Create Procedure spSessionRemove()
As
      set nocount on
      delete from orders where SessionID IN (SELECT ID from temp_IDs
            WHERE DateDiff(day, [timestamp], getdate()) > 1)

      delete from addresses where SessionID IN (SELECT ID from temp_IDs
            WHERE DateDiff(day, timestamp, getdate()) > 1)
      delete from payment where SessionID IN (SELECT ID from temp_IDs
            WHERE DateDiff(day, timestamp, getdate()) > 1)
      delete from userinfo where SessionID IN (SELECT ID from temp_IDs
            WHERE DateDiff(day, timestamp, getdate()) > 1)
      -- finally, remove the sessionids:
      delete from temp_IDs
            WHERE DateDiff(day, timestamp, getdate()) > 1
      
      return
---------------------------------------------------
0
 
HypercubeTechAuthor Commented:
i'll give it a shot, thanks!   also, we have a support ticket opened with Verizon, but they are idiots
0
 
dukestaTAICommented:
kssoftware brings up a great point with using Stored Procedures.  Also Maybe you could try using the ODBC SQL Provider instead of the OLE Provider to see if you get the same problem.  Here is an example:

connectionInfo = "Driver={SQL Server};Server=XXXXX;Database=XXXXX;Uid=XXXXX;Pwd=XXXXXX;"

Maybe this will help isolate the problem to MDAC.

0
 
Joe JenkinsCommented:
The stored procedure and OLE providers are all great suggestions actually.  Using the stored will allow for shorter db access times per transaction.  That in itself will have an affect on overall performance.  

I'm glad you didn't tell us you were using a DSN. :)

I'm not even sure it's a lock issue though because in the initial posting it was explained that there was [maybe] 2-3 people in the store at a time.  75% or higher, depending on the store, of a user's time is browsing the store and not running lookups, etc.   If you're running into a lock issue with that few users you will definitely need to review the length of your SQL calls to the db.  Visit where connections are open and closed and keep the closed as long as possible.  It may not have been an issue with the old provider, but could be showing up in this case.  Either way, the stored procedure is a good idea for performance so I would definitely implement that either way.

Part of the Verizon ticket would definitely make sure they're running the latest DB engine drivers. and DAC's.  

:) Let us know how this comes out!
Joe Jenkins
0
 
jespiresCommented:
Just a comment by looking at the code.  I don't see you closing any of the DB connections.  You set the value to values to nothing, but that does not close the connections.  I actually open and close every connection as fast as I can when accessing data to prevent deadlocks like you are explaining.  You may be opening the DB with a lock..

Here is how I close the connections and recordsets and each query.

    rs1.close
    cn1.close
    set rs1 = nothing
    set cn1 = nothing
0

Featured Post

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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now