N
Nigel Davis
I was gettting the timeout error too when trying to run a large bulk
insert statement within ADO.NET. I tried increasing all of the
timeouts in SQLServer (e.g. 'remote login', 'query wait' etc. and also
the connection and query command timeouts on the client within
ADO.NET.
Even after I had changed all of these settings, I was still getting
the error and I was still getting a timeout after about 30 seconds. I
looked much closer at the code and realised that we had switched to
using the SqlHelper class during development and although our code
contained SqlCommand objects as a legacy of previous development, the
SqlHelper was now being used to execute our queries and this object
does not get passed a SqlCommand object as it creates it's own.
SqlHelper does not provide any methods or constructor arguements to
specify the query timeout - it really acts as a wrapper for creating
and executing simple SqlCommand objects. Unfortunately I did not
notice until revisiting our code that the SqlHelper class was not
being passed any of our SqlCommand objects with greater timeout values
and therefore it was setting the default timeout of 30 seconds each
time.
If anyone using SqlHelper is encountering this error (or wishes to
avoid this timeout error), it is worth noting that there does not seem
to be any way of running long queries with SqlHelper as there does not
appear to be any mechanism to get access to certain important
SqlCommand properties.
insert statement within ADO.NET. I tried increasing all of the
timeouts in SQLServer (e.g. 'remote login', 'query wait' etc. and also
the connection and query command timeouts on the client within
ADO.NET.
Even after I had changed all of these settings, I was still getting
the error and I was still getting a timeout after about 30 seconds. I
looked much closer at the code and realised that we had switched to
using the SqlHelper class during development and although our code
contained SqlCommand objects as a legacy of previous development, the
SqlHelper was now being used to execute our queries and this object
does not get passed a SqlCommand object as it creates it's own.
SqlHelper does not provide any methods or constructor arguements to
specify the query timeout - it really acts as a wrapper for creating
and executing simple SqlCommand objects. Unfortunately I did not
notice until revisiting our code that the SqlHelper class was not
being passed any of our SqlCommand objects with greater timeout values
and therefore it was setting the default timeout of 30 seconds each
time.
If anyone using SqlHelper is encountering this error (or wishes to
avoid this timeout error), it is worth noting that there does not seem
to be any way of running long queries with SqlHelper as there does not
appear to be any mechanism to get access to certain important
SqlCommand properties.