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.

Possible to secure using .Net StrongNameIdentityPermission?

Is it possible to secure a SQL Server database or schema using a technique such as the .Net StrongNameIdentityPermission attribute? The intent is that SQL Server would only permit transactions coming from assemblies which were signed with a particular .Net StrongName private key.

We are installing a 3rd party SQL Server / ASP.Net application which must run in our DMZ and we are looking for all possible measures to secure the SQL Server database.

Thanks

I don't know of any support for such a feature. I'll move this to the SQL Server Data Access forum. If they also don't know of any such method, then the answer is a definite no.

Thanks
Laurentiu