469,602 Members | 1,765 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

Classic ASP connection to SQL Server 2005

3
We are using a dedicated Windows 2003 machine running IIS 6.0 and SQL Server 2005 (on the same machine). 1 CPU, 2GB RAM. SQL Server set to 1GB max memory. Our code is Classic ASP and recently our server became unresponsive during busy hours where only about 50 requests would execute (with long delay) and the rest would time out. For the site visitors the experience is a hung service with no response. We are new on this dedicated server (and used to be on a shared environment with IIS 6.0 and an older version of SQL Server 2000 on a separate shared machine)

We want to make sure that there is no memory leak driven by our code and we've checked line by line to make sure that we are closing all connections and destroying objects. However, we also would like to validate the method we connect to the SQL database and would like the help of the community here. Below is the code we are using to connect and would appreciate if someone can skim over it and tell us we we should be doing something differently:

Expand|Select|Wrap|Line Numbers
  1. <%
  2.  Function glpCloseRst(objRst)
  3.   If IsObject(objRst) Then
  4.    If objRst.State = 1 Then
  5.     objRst.Close
  6.     Set objRst = Nothing
  7.    End If
  8.   End If
  9.  End Function
  10.  
  11.  Function glpCloseDBConn (objDBConn)
  12.   If IsObject(objDBConn) Then
  13.    If objDBConn.State = 1 Then
  14.     objDBConn.Close
  15.     Set objDBConn = Nothing
  16.    End If
  17.   End If
  18.  End Function
  19. %>
  20.  
  21. <%
  22. ConnectionStrSQL = "Driver={SQL Native Client};Server=555.55.555.55;Database=dbname;Uid=username;Pwd=password;"
  23. set connSQL = CreateObject("ADODB.Connection") 
  24. connSQL.open ConnectionStrSQL
  25. %>
  26.  
  27. <%
  28. Dim allSQX
  29. allSQLX = "SELECT * FROM TABLE WHERE id='something';" 
  30.  
  31. Dim allRsX
  32. Set allRsX = Server.CreateObject("ADODB.Recordset")
  33. allRsX.Open allSQLX, connSQL, 3, 3
  34.  
  35. DO UNTIL allRsX.eof 
  36.  
  37.   ...somestuff...
  38.  
  39. allRsX.movenext
  40. LOOP
  41.  
  42. glpCloseRst(allRsX)
  43. glpCloseDBConn(connSQL)
  44. %>
Thank you!
Jan 11 '08 #1
1 4229
SQL Server 2005 has many editions. The free edition works on 1 CPU, max 2GB storage and limited updates/connections. I'm not sure but it is something about 50. So maybe the problem is not the sourch code, but the restrictions of your MS SQL2005 edition.

Hope that will help.
Jan 23 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by Danieltbt05 | last post: by
5 posts views Thread by Velvet | last post: by
6 posts views Thread by John | last post: by
16 posts views Thread by crbd98 | last post: by
20 posts views Thread by fniles | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.