March 12, 2003 at 3:58 pm
I have a web based application from which I issue a
stored procedure xp_cmdshell command to copy a file from
our Image server to web server(Does not make sense but
due to firewall issues I have to do it this way)I pass
the parameter in the stored proc for the file name and
then in my asp page I closed the Recordset and
connection. But this connection stays as sleeping in SQL
Server 7.00 SP3 for hours and after a while I have 100's
of sleeping connection through this App.
I have been struggling with this for weeks now but can't
figure out whats going on. Any help will be greatly
appriciated. Please Please Try to Help if anyone know the
answer.
Thanks a Lot
Ann
March 17, 2003 at 8:00 am
This was removed by the editor as SPAM
March 17, 2003 at 8:30 am
If you post the ASP code, we could have a look for errors/suggestions...
Thanks
March 17, 2003 at 3:41 pm
Here is the code. Sorry I am sorry I have replace the server name and userid with xxx. I am sure you understand.
But here is the code
connect="DRIVER=SQL Server;SERVER=xxxxx;UID=xxx;PWD=xx;DATABASE=xxx"
' ======================================================================
set objconn=server.CreateObject("ADODB.Connection")
objconn.Open connect
set objCmd=Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection=objconn
objCmd.CommandType=adCmdStoredProc
objCmd.CommandTimeout=1000
set RSSQL = server.CreateObject("adodb.recordset")
fromID= "\\xx\xx\xx" & "\" & RS.fields(1) & "\" & RS.Fields(2) & "\" & RS.fields(0)
ABCode=Session("ABCode") + 1
toID="\\xx.xx.xxx.xx\xxx\TifImages\" & session("LoginID") & ABCode & ".TIF"
command="copy" & " " & fromID & " " & toID
'Set up for stored procedure calls
objCmd.CommandText = "sp_ImageCopy"
objCmd.Parameters.Append objCmd.CreateParameter("command",adWChar, adParamInput,512,command)
objCmd.Parameters.Append objCmd.CreateParameter("Msg",adInteger,adParamOutput)
rssql.CursorLocation = adUseClient
rssql.LockType=3
Err.clear
' Run stored procedure to copy image from ImageServer to IIS Server
rssql.Open objCmd
' Set fso = CreateObject("Scripting.FileSystemObject")
' fso.CopyFile fromID, ToID
If (Err.Number=0 AND ImageID<>"" AND objCmd.Parameters("Msg")=1 )Then
NoPages=Cint(RS.fields(3))
'Response.Write NoPages
If NoPages > 5 then NoPages=5
' This is for credit processing
IF ( session("AT")="O" AND Session("CPV")="NO" ) Then
Session("ACT")="./xxxImageCopy.asp?imageid=" & ImageID
%>
<Script Language="Javascript">
window.location.replace('xxxx.asp?CredC=' + '<%=NoPages * 1.0 %>');
//self.close()
</SCRIPT>
<%
Response.end
End if
session("ImageID")=ImageID
session("toID3")=Session("toID2"):session("toID2")=Session("toID1")
session("toID1")= "http://xxx.xx.xxx.xx/xxx/TifImages/" & session("LoginID") & ABCode & ".TIF"
session("NoPages")=NoPages
Session("ABCode") =ABCode
response.redirect ("./xx/xxxx/xx.asp")
Else
response.write "ERROR # :" & Err.Number & "<BR> DESCRIPTION :" & Err.description
End If
RSSQL.close
set RSSQL=nothing
objconn.close
set objconn=nothing
Else
response.write "Missing Image :" & "<BR> Sorry, there is no image for this document.<BR> For more information, please contact xxx"
End if
RS.Close
set RS=nothing
%>
March 18, 2003 at 8:30 am
Hmmm, couple thoughts.
-Did you miss an "If" Or "End If" somewhere when pasting your code in? I copied
your code into VB after removing the client side JSCript, removed my Option
Explicit, and create a pseudo Session object, and it gives me the error "Else
without If" when trying to compile. This is something I frequently do to test
out VBScript for WSH, and ASP. The offending Else is located at :
quote:
Elseresponse.write "Missing Image :" & "<BR> Sorry, there is no image for this document.<BR> For more information, please contact xxx"
End If
-Also, when you set your commands connection I believe you are making a second
connection. This line :
quote:
objCmd.ActiveConnection=objconn
This line is very deceptive. The default property of a connection is the
connection string, and by you making it EQUAL to the other connection, and not
doing a SET operation, you are basically copying the connection string from one
connection to the other. The command will then create its own new connection.
-I am unsure at this point how you are harboring connections, but it may be
because the code in the ASP page may be malformed, and the "On Error Resume
Next" is hiding the issue. Because of VBScripts horrible support for error
handling, often times I see web developers masking issues when using "On Error
Resume Next". I am not saying do not use it, but when testing comment it out to
ensure that you code is well formed, and compiles correctly. From your first
post I thought you might have been storing the connection in the Session with a
long Session timeout, but I do not see you storing any of those objects in the
Session. If your cod is malformed, it may cause you logic to not work properly
and your connections to never close.
-So in summary if you have an "On Error Resume Next above in you code, remark it
out for a couple quick tests.
And use :
objCmd.ActiveConnection = connect
OR
Set objCmd.ActiveConnection = objconn
Instead Of
objCmd.ActiveConnection = objconn
Tim C.
//Will write code for food
Tim C //Will code for food
March 19, 2003 at 11:07 am
I am sorry I missed it when I did Cope and Paste. Also I need this line. The other thing I did was I changes the command time out to 100 and looks like in test server the conncetion does get released in about one minutes but in production server it does not. Any other ideas
Thanks
March 20, 2003 at 8:35 am
Looks like you are only closing your connection if you meet the first IF criteria.
Try taking out your close connection & setting to nothing from within the if block to right above your RS close statement
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply