hi all !!!

I have a DTS package that it is failing with a "timeout expired" message
when reading from SQL Server.

I suspect about locking problems when some data combination happens, because
the same package runs fine for most of the data sets I run it with, but with
some ones fails.

So the question is: can SQL Server be setup to not use locks at all ?

I know that doing so will not introduce any problems neither in the DTS
package nor in the applications using the database.

thanks again,
ant.

If you are just running selects you can use the NOLOCK hint or set the
isolation level to READ UNCOMMITTED and it will not take out any locks. But
it won't honor any either. That means you can get dirty reads. So if the
data your reading is not mostly static or the values are important you have
to use this option with extreme caution. But a Select only takes out shared
locks anyway which are compatible with other shared locks. If your getting
blocked then that means someone is changing the data your trying to read. I
would find out why the other process is holding the locks for an extended
period first.

--
Andrew J. Kelly SQL MVP


"Antonio Lopez Arredondo" <adog (AT) someplace (DOT) com> wrote

This is most likely lock escalation. Under the covers, when SQL Server
issues too many locks on a given table it simply replaces the more granular
locks with a table lock. This is standard fare with most RDBMSes, so you
should always consider this when designing a bulk load.

"Andrew J. Kelly" <sqlmvpnoooospam (AT) shadhawk (DOT) com> wrote