asked on

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.

apresto

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

apresto

try this:

System.Diagnostics.FileVersionInfo.GetVersionInfo(Assembly.GetExecutingAssembly().Location)

apresto

Sorry, wrong question, ignore that last comment

tanveer_1476

ASKER

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

apresto

ok, you'll need to use the command object of ASP to be able to specify a time out, the syntax is as follows:...

apresto

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

apresto

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

apresto

you can extend the timeout by changing the CommandTimeout property (in seconds)

CommandTimeout = 60

ASKER CERTIFIED SOLUTION

Ashley Bryant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tanveer_1476

ASKER

when i include the above code , it just come out without doing anything (:-

tanveer_1476

ASKER

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

tanveer_1476

ASKER

Please close this call as I have resolved the error.