Timeout expire in windows service.

[archana]

Hi all,

I am executing one stored procedure in windows service.

I set sqlcommad's CommandTimeout to 432000 which means 5 days timeout.

Still after 11th hour i am getting timeout. I am not getting if i set
timeout as 5 days why i am getting timeout before that.

Please shed some light on it.

Thanks in advance.

 

[gmiley]

Have you set your servers remote query timeout option?

sp_configure 'remote query timeout', 0
-- This sets it to infinite, otherwise replace the 0 with N,
-- where N = number of seconds to wait.


More importantly I would ask why you are executing a sql command that
can take 5 days to run? There must be a better way to do what you want.

gm

 

[archana]

Hi,

thanks for your reply.

Actually my sp is long running which may take a day or 2 days.

And I read somewhere that try to prevent setting timeout to 0 means
infinite.

so i set timeout to 5 days.

But i am still facing the problem.

thanks.

 

[DaanishRumani]

Is the timeout specified in seconds or milliseconds or something else.
I suggest you to check the documentation. I am not sure if that is the
problem.

 

[chanmm]

Sounds interesting, you are sure you stored procedure run completed without
runing it using windows service? What's that stored procedure doing by the
way?

chanmm

 

[gmiley]

I just personally have never seen any reason for anything in SQL to run
for that long. I would suggest evaluating and rewriting that stored
procedure. Either just rewrite it to streamline it, or break it into
several segments. If it is a lot of repeating processing, I would
suggest recreating it to process 1 record at a time, and have a simple
application that gathers the data to be processed then executes that
stored procedure or statement on each of the records.

gm

 

[archana]

Hi chanmm,

thanks for you reply.

If i run through query analyser, it is working properly. Even through
windows services also sometimes it is working properly. and sometimes
it is failing. I am not getting why this is happening i have specified
timeout as 432000 which is equal to 5 days.

If you can shed some light on it then it will be really helpful for me.

thanks.