June 26, 2003 at 9:11 am
Morning,
How could I determine or trap when this might timeout?
<%
adCmdSPStoredProc=4
adParamReturnValue=4
adParamInput=1
adParamOutput=2
adInteger=3
adVarChar=200
oVal=3
set CmdSP=Server.CreateObject("ADODB.Command")
CmdSP.CommandTimeout=60
CmdSP.ActiveConnection=Session("StrConnect")
CmdSP.CommandText="sp_CreateSalesAnalysisDB"
CmdSP.CommandType=adCmdStoredProc
CmdSP.Parameters.Append CmdSP.CreateParameter
("RETURN_VALUE",adInteger,adParamReturnValue,4)
CmdSP.Parameters.Append CmdSP.CreateParameter
("@txtOriginalDatabase",adVarChar,adParamInput,255,DatabaseName)
'
CmdSP.Execute ,, ADODB.adExecuteNoRecords
%>
Calling this from an ASP page, in a nutshell it creates a backup of a database and restores it to another name so the user can do sales analysis without messing up the original. Problem is that on a large database, the backup/restore procedure might take more than a minute.
When the connection timeout is reached is there anyway to know, either by the called proc dying and being able to trap/return a msg or from the ADO side?
Not sure if this is a SQL question or an ADO programming question more so please forgive if this isn't the right forum 🙂
regards,
Chris
June 26, 2003 at 6:08 pm
It should return a trappable error. You're giving the user more power than I would. An alternative way to do this is to run a proc that sticks a row in a table, have a job that checks the table every min, if found, does the copy/restore process. Your app can then either check for the status of the inserted row to change, or periodically try the new db until its available.
Andy
June 27, 2003 at 2:09 am
I would suggest that you use the error handler, in the error handler check the err object for the ADO timeout error number, you can then set the CmdSP.CommandTimeout to a higher value and re-run the ADO Command
On Error Goto ErrorHandler
....
....
CmdSP.Execute .....
Exit Function
ErrorHandler:
If Err.Number = <ADO Timeout Val> then
CmdSP.CommandTimeout = 120
resume
else
<log other error>
end if
Exit Function
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply