This topic is locked
[SOLVED]

 page calls stored proc, 30secs later get error

9/19/2020 2:04:28 AM
ASPRunner.NET General questions
H
heilmanmd author

Have a page that calls a stored procedure to generate some info in a table

below code applies
strWhereClause = newwherestr.ToString();

calcwhere = calcwhere.Replace("'","''");
sqlcmd = " call nyegeonet.calc_totals('" + userid + "','" + calcwhere + "',1,0); " ;
tDAL.CustomQuery(sqlcmd);
when run the stored proc with Intelli-J or AQT runs fine, exec time ~ 80 secs
wen call ( aka run ) the stored proc from the page @ ~ 30 seconds get error user interrupt
the same OBDC driver used by Intelli-J or AQT is used by the web app, so doubt is the driver timeout
using IIS 10 and session time out default is 20 minutes
doing C # in before SQL query page event, and wondering if I need to do something with script timeout? or something??
any thoughts / feedback appreciated.
Thanks

Mark

admin 9/22/2020

Can you post the complete error message here? Just trying to understand what kind of timeout is this.

H
heilmanmd author 10/11/2020

Here is the error
https://www.screencast.com/t/6mXTFjvaKNM
This happens @ ~ 30 secs...
I can run the stored proc fine, takes about 80 secs to process ~ 400K recs
the DB2 error says
https://www.ibm.com/support/pages/sql0952n-error-returned-long-running-sql-statement
but the ODBC cli driver is the same driver used to run the stored proc, so I know that's not the issue...
Anyways, hopefully above screen shot / info may help
Thanks

Mark

admin 10/12/2020

This is something that you can try.
Open file C:\Program Files (x86)\ASPRunnerNET10.4\source\connections\OLEDBConnection.cs in any test editor. Find the following function:

protected override DbCommand GetCommand()

{

return new System.Data.OleDb.OleDbCommand();

}


Try changing it the following way and see what happens.

protected override DbCommand GetCommand()

{

System.Data.OleDb.OleDbCommand command = System.Data.OleDb.OleDbCommand();

command.CommandTimeout = 120;

return command;

}


If this doesn't help do the same thing in ODBConnection.cs file.

protected override DbCommand GetCommand()

{

System.Data.Odbc.OdbcCommand() command = System.Data.Odbc.OdbcCommand();

command.CommandTimeout = 120;

return command;

}
H
heilmanmd author 10/13/2020

I Got it to work using the following code
Note: since I'm only using ODBC drivers, I only changed the ODBC file...
here's the code that works...
DbCommand command = new System.Data.Odbc.OdbcCommand();

command.CommandTimeout = 120;

return command;
Million thanks for the info on where to look / change the time out... most appreciated, million KUDO's!!!!!
Mark