I wrote a stored procedure...and executed in Query Analyser.
It took 40min to execute....ofcourse...it is something like archieving database of millions of rows along with distributed queries....I am quite ok with the stored procedure.....(even time taking)...
But, then I executed the same stored procedure in my program using the command object which within less than a min...I am returned with the following error:
TIMEOUT EXPIRED. THE TIMEOUT PERIOD ELAPSED PRIOR TO COMPLETION OF THE OPERATION OR THE SERVER IS NOT RESPONDING.
I think the connection could not sustain till the completion of store procedure.
create procedure xxx as
insert ..... select * from linkedserver.database.user.table1
insert ..... select * from linkedserver.database.user.table2
insert ..... select * from linkedserver.database.user.table3
insert ..... select * from linkedserver.database.user.table4
I think u are trying to insert from a database tables to another database tables. Have you tried executing a simple stored proc to your UI? if still time out expired then the problem is in your UI. if not, it is your database tables that have problem.
I think the cause is that the execution time of the big transaction is too long.when the connection's timeout threshold is up,the transaction is still executing.So setting the connection's timeout longer or no limitted.
According to odbc.net document,the odbcconnection's connectiontimeout property is setting waiting-time of connection's open.
And I think the problem is the execution time is too long.So I think the best solution is using asynchronization mode.
U must return the control to your client,and in back-end check the execution status if finished.