pre-emptive locking solution

Hey all,

I've got a very long stored proc that runs intensive updates on a particular table. The locks are always escalated from Intent eXclusive to eXclusive. After some reading online, I've decided to implement this (http://support.microsoft.com/default.aspx?scid=kb;en-us;323630#kb2) . The idea is to start a transaction with another spid, and hold an incompatible lock on that table so the stored procedure that I'm running isn't able to escalate the lock. The solution works, but it unfortunately means that I've to lock this table with an update lock for the whole stored proc, which I would rather not do.

Is it possible to spawn another stored proc/function/transaction under another spid from within my stored proc ? I'm hoping the answer to my question isn't here (http://www.dbforums.com/t994076.html).

Is it maybe possible to open another connection within my stored proc ? On a similar note, would it be possible to communicate somehow between connections without using a table ?

Thanks,
-KilkaThere are many ways to do this, but Transact-SQL is a bit limited in this area. It can be done, but it is brute force and ugly at best.

Have you investigated DTS? At least in my experience, it handles this kind of processing much better than Transact-SQL can.

-PatP|||I've worked with DTS and I know the only way it could potentially help me was if I used scripting (activeX or something) to acheive the same thing.

I'm trying to keep everything in a scheduled stored proc. If at all possible, I want to do everything in T-sql for performance reasons. This is something that takes hours to run, so any little performance hit has a big impact.

Post-sp3a Patches?

Are there any patches are updates for SQL Server 2000 that are NOT included
in service pack 3a? I have a customer thinking that additional sql server
patches are required on a server but I don't believe that there are any that
are not already included in 3a which this server has.
Thanks.
MichelleI found this from July 2003:
http://www.microsoft.com/technet/se...n/MS03-031.mspx
I think that's everything.
"michelle" <michelle@.nospam.com> wrote in message
news:OL$aOsN9EHA.4004@.tk2msftngp13.phx.gbl...
> Are there any patches are updates for SQL Server 2000 that are NOT
included
> in service pack 3a? I have a customer thinking that additional sql server
> patches are required on a server but I don't believe that there are any
that
> are not already included in 3a which this server has.
> Thanks.
> Michelle
>|||Michelle,
SP4 is just going into beta. See
http://support.microsoft.com/defaul...kb;en-us;888799 for fixes
included.
Chris Wood
"michelle" <michelle@.nospam.com> wrote in message
news:%23bXn8UO9EHA.1292@.TK2MSFTNGP10.phx.gbl...
> I found this from July 2003:
> http://www.microsoft.com/technet/se...n/MS03-031.mspx
> I think that's everything.
> "michelle" <michelle@.nospam.com> wrote in message
> news:OL$aOsN9EHA.4004@.tk2msftngp13.phx.gbl...
> included
server[vbcol=seagreen]
> that
>sql