Stored Proc Doesn't work - Equivalent SQL does work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proc Doesn't work - Equivalent SQL does work

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-15 : 08:40:48

Bruce writes "I have a stored procedure that has been working for several weeks in production and development servers. Now the stored procedure won't work on the development server.

I let it run for five minutes in the query analyzer and it never finished. I finally stopped it. I took the SQL that's inside the stored procedure and ran it in the query analyzer. It finished in two seconds. What's up with that?

The procedure takes data from a few different places, creates some new records and updates them. It does not return data. It does create and drop one temporary table.

I dropped and recreated the stored proc. No change.

I deleted the stored proc and recreated it with a new name. No change.

I deleted the entire database and recreated it with the entire dataset from the production server. That worked for a while. I ran the proc from a vb6 app a few times. Then it failed again.
Same symptoms as before.

This is what the server is running:
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

Can you help?

P.S. Here is the SQL which works in the query analyzer. I just added the declaration of variables and gave them values. Otherwise it's the text from the store procedure.

--CREATE proc dbo.Allegiance_SplitProcess
--@InvoiceId int,
--@PreviousInvoiceId int
--as

declare @InvoiceId int
declare @PreviousInvoiceId int

set @InvoiceId = 668
set @PreviousInvoiceId = 667

select distinct LineItemId, Co, div, plant, dept, CostCenter = div+plant+dept, null as GLAccount, LIQty as SplitQty, LIAmt as SplitAmt
into #tempSplit
from
(select distinct UniqueId, s.Co, s.div, s.Plant, s.dept
from LineItem l inner join split s
on l.LineItemId = s.LineItemId
inner join (
SELECT DISTINCT Co, CostCenter, Flag
FROM GLInfo..tblGL_CostCenter
WHERE Flag = 0) cc
on s.Co = cc.Co
and s.div+s.plant+s.dept = cc.CostCenter
where InvoiceId = @PreviousInvoiceId
and UniqueId <> 'na') old
right outer join
(select distinct UniqueId, LineItemId, LIQty, LIAmt
from LineItem
where InvoiceId = @InvoiceId) new
on old.UniqueId = new.uniqueId
order by co
---------------------------------------------------------------------------
create index lid on #tempSplit(LineItemID)
create index cc on #tempSplit(CostCenter)

update #tempSplit
set GLAccount = pgl.GLAccount
from #tempSplit t
inner join LineItem l
on t.LineItemId = l.LineItemId
inner join Product_GL pgl
on l.ProdId = pgl.ProdId
inner join GLInfo.dbo.tblGL_CostCenter glcc
on pgl.GLAccount = glcc.GLAccount
and glcc.CostCenter = t.CostCenter
----------------------------------------------------------------------------
update #tempSplit
set SplitQty = SplitQty/x.Itemcount,
SplitAmt = SplitAmt/x.ItemCount
from #tempSplit inner join (
select LineItemId,count(1) as ItemCount from #tempSplit group by LineItemId having Count(1) > 1) x
on #tempSplit.LineItemId = x. LineItemId
-----------------------------------------------------------------------------
insert into Split(LineItemId, Co, Div, Plant, Dept, GLAccount, SplitQty, SplitAmt)
select LineItemId, Co, Div, Plant, Dept, GLAccount, SplitQty, SplitAmt
from #tempSplit
where GLAccount is not null
------------------------------------------------------------------------------
drop table #tempSplit
------------------------------------------------------------------------------
--Update statusId of LineItem
UPDATE LineItem
SET StatusId = 2
WHERE LineItem.InvoiceId = 712
AND LineItemId in
(
SELECT L.LineItemId
FROM LineItem L Inner join
(SELECT LineItemId, sum(splitqty) as SumOfSplitQty, sum(SplitAmt) as Su

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-04-15 : 08:40:48

And we truncated at 4,000 characters.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources