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 join a storeproc result set to normal select?

I have a rather complex storeped proc that does many calculations and
returns a row of data. Now I need to take that result set and use it along
with another result set by way of a select statement. A simplified example:
exec sp_GetAmounts @.ID
returns: 100, 300, 400 , 500
select * from BK where BKID = @.ID
returns: Joe Scmoe, 1234 Main Street, 90-32920, 01/01/2005
Now Ideally, I'd like to have something that 'marries' the two queries and
returns:
Joe Scmoe, 1234 Main Street, 90-32920, 01/01/2005, 100, 300, 400 , 500
My real select statement is more complex than the above example, involving
several joins. Both results sets return much more data than in the above
examples.
Is this possible?You can grab the sp resultset in a temporary or normal table, and use it to
join with the result of the select statement.
Example:
use northwind
go
create table #t (
ShippedDate datetime,
OrderID int,
Subtotal money,
[Year] int
)
insert into #t
exec dbo.[Sales by Year] @.Beginning_Date = '19960101', @.Ending_Date =
'19961231'
select
oh.orderid, oh.orderdate,
t.[year],
t.subtotal
from
orders as oh
left join
#t as t
on oh.orderid = t.orderid
and oh.orderdate >= ltrim(t.[year]) + '0101'
and oh.orderdate < ltrim(t.[year] + 1) + '0101'
drop table #t
go
AMB
"Nelson F." wrote:

> I have a rather complex storeped proc that does many calculations and
> returns a row of data. Now I need to take that result set and use it along
> with another result set by way of a select statement. A simplified example
:
> exec sp_GetAmounts @.ID
> returns: 100, 300, 400 , 500
>
> select * from BK where BKID = @.ID
> returns: Joe Scmoe, 1234 Main Street, 90-32920, 01/01/2005
>
> Now Ideally, I'd like to have something that 'marries' the two queries and
> returns:
> Joe Scmoe, 1234 Main Street, 90-32920, 01/01/2005, 100, 300, 400 , 500
>
> My real select statement is more complex than the above example, involving
> several joins. Both results sets return much more data than in the above
> examples.
> Is this possible?
>
>|||Why not use a function instead
For example
USE Northwind
GO
CREATE FUNCTION MyFunc (@.CustId varchar(5))
RETURNS TABLE
AS
RETURN (SELECT CustomerId, Count(OrderID) NoOfOrders, MAX(OrderDate) AS
LastOrdered
FROM Orders
WHERE CustomerId = @.CustId
GROUP BY CustomerId)
GO
CREATE PROC MyProc @.CustId varchar(5)
AS
SELECT CT.CustomerId, CT.CompanyName, FN.NoOfOrders, FN.LastOrdered
FROM dbo.MyFunc(@.CustId) FN INNER JOIN Customers CT
ON FN.CustomerId = CT.CustomerId
GO
EXEC MyProc 'VINET'
"Alejandro Mesa" wrote:
> You can grab the sp resultset in a temporary or normal table, and use it t
o
> join with the result of the select statement.
> Example:
> use northwind
> go
> create table #t (
> ShippedDate datetime,
> OrderID int,
> Subtotal money,
> [Year] int
> )
> insert into #t
> exec dbo.[Sales by Year] @.Beginning_Date = '19960101', @.Ending_Date =
> '19961231'
> select
> oh.orderid, oh.orderdate,
> t.[year],
> t.subtotal
> from
> orders as oh
> left join
> #t as t
> on oh.orderid = t.orderid
> and oh.orderdate >= ltrim(t.[year]) + '0101'
> and oh.orderdate < ltrim(t.[year] + 1) + '0101'
> drop table #t
> go
>
> AMB
> "Nelson F." wrote:
>|||> Why not use a function instead
I can not answer this question because I have no idea what the sp is doing.
No code was posted with the msg.
AMB
"Andy B" wrote:
> Why not use a function instead
> For example
> USE Northwind
> GO
> CREATE FUNCTION MyFunc (@.CustId varchar(5))
> RETURNS TABLE
> AS
> RETURN (SELECT CustomerId, Count(OrderID) NoOfOrders, MAX(OrderDate) AS
> LastOrdered
> FROM Orders
> WHERE CustomerId = @.CustId
> GROUP BY CustomerId)
> GO
> CREATE PROC MyProc @.CustId varchar(5)
> AS
> SELECT CT.CustomerId, CT.CompanyName, FN.NoOfOrders, FN.LastOrdered
> FROM dbo.MyFunc(@.CustId) FN INNER JOIN Customers CT
> ON FN.CustomerId = CT.CustomerId
> GO
> EXEC MyProc 'VINET'
>
> "Alejandro Mesa" wrote:
>|||Sorry AMB, i was offering Nelson an alternative solution to yours given the
ouput he had specified.
I should've replied to his message and not yours
Andy
"Alejandro Mesa" wrote:
> I can not answer this question because I have no idea what the sp is doing
.
> No code was posted with the msg.
>
> AMB
>
> "Andy B" wrote:
>|||Thanks to both of you both solutions work well!