Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired .

Hello All,

I am developed a stored procedure in SQL Server which takes the CSV file coverts its rows into sql Rows. I Have an ASP page where I upload the CSV file and click on the Add button. This takes the file name and excutes the stored procedure. This works fine. A table (main table) in the backend gets created with all the SQL rows. This table has additional columns which gets the data from other tables like region (the value comes from region table). I have developed an ASP page which does the job of updating the main table with the values from transaction tables. Therefore this ASP page has many loops and has to process more than 5000 records at a time. When it starts processing more then 1000 records , it gives me an error. Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired . Please help me in resolving this issue.This is quite urgent.


tanveer_1476Asked:
Who is Participating?
 
Ashley BryantSenior Software EngineerCommented:
If all of this information is in your SQL database, then why aren't you having the SQL server do all the work that your ASP is doing?  It would save you a lot of processing time and looping within ASP.
0
 
aprestoCommented:
Hi tanveer_1476,

You will need to extend the TimeOut property of your command object - how are you executing the stored procedure in the asp?

Apresto
0
 
aprestoCommented:
try this:

System.Diagnostics.FileVersionInfo.GetVersionInfo(Assembly.GetExecutingAssembly().Location)
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
aprestoCommented:
Sorry, wrong question, ignore that last comment
0
 
tanveer_1476Author Commented:
Hello,

I am executing the stored procedure this way. When I include the objConn.timeout = 200 . it gives me an error. To me it doesn't look like a stored procedure error. As the page has many loops , problem is coming up.

sSql = "sp_main_backup_mt 'main_backup_mt', '"&strev&"'"
objConn.Execute(sSql)
objConn.timeout = 200
0
 
aprestoCommented:
ok, you'll need to use the command object of ASP to be able to specify a time out, the syntax is as follows:...
0
 
aprestoCommented:
dim cmd
set cmd = Server.CreateObject("ADODB.command")
cmd.ActiveConnection = objConn
cmd.CommandText = "sp_main_backup_mt 'main_backup_mt', '"&strev&"'"
cmd.CommandType = 04
CommandTimeout = 60 'Seconds
0
 
aprestoCommented:
you may need to include a file at the top of your asp page : the ADOVBS file

here is some info on it:

http://www.4guysfromrolla.com/webtech/faq/Beginner/faq7.shtml

you can also get it from the location above.  Its so that you can specify the commandType using 04 rather than the equivelant of &H0004

i.e

cmd.CommandType = &H0004
0
 
aprestoCommented:
you can extend the timeout by changing the CommandTimeout property (in seconds)

CommandTimeout = 60
0
 
tanveer_1476Author Commented:
when i include the above code , it just come out without doing anything (:-
0
 
tanveer_1476Author Commented:
Hi Ash,
How do I go about this . For example I have one loop


sqlMain = "Select currency,exp_date from main_backup_mt"
set rsmain1 = objConn.execute(sqlMain)
set rsConv =  Server.CreateObject("ADODB.Recordset")
set rsConv =   objConn.execute(sqlConversion)
do while not rsmain1.eof
do while not rsConv.eof

                dcdate =datepart("d",(rsConv.fields("start_date")))
                  mcdate = datepart("m",(rsConv.fields("start_date")))
                  ycdate = datepart("yyyy",(rsConv.fields("start_date")))
                  scDate= mcdate & "/" &dcdate & "/" & ycdate
                  
                  edcdate = datepart("d",(rsConv.fields("end_date")))
                  emcdate = datepart("m",(rsConv.fields("end_date")))
                  eycdate = datepart("yyyy",(rsConv.fields("end_date")))
                  ecDate= emcdate & "/" &edcdate & "/" & eycdate
                  

      sqlConvupdate = "update main_backup_mt set exchange_rate = '"&rsConv.fields("conversion_rate")&"' where currency = substring('"&rsConv.fields("currency")&"',1,3) and exp_date between '"&scDate&"' and '"&ecDate&"'"
      
      Set rsUpdate = Server.CreateObject("ADODB.Recordset")
      set rsUpdate = objconn.execute(sqlConvupdate)
rsConv.movenext
response.Flush()
loop
'response.Write(rsmain1.fields("currency")) &" from main" & "<br>"
rsmain1.movenext
response.Flush()
loop
0
 
tanveer_1476Author Commented:
Please close this call as I have resolved the error.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.