why stop when running sp at package???

shrikeng 2006-05-19 09:25:31

Dear all

i have a stored procedure which run in package. I find out that the stored

procedure stop at the running after 1 seconds, but the pacakage reports the

success.

this stored procedure can run at query analyzer normally.

I tried to adjust the command time-out at the execute sql task properties

object to 9999 or 0 or 1800. However, I find out it is useless to extend the

executive time.

The case happens in two sql server 2000 which is installed in win 2k server

and nt server 5.0. So, I guess it is a sql server bug.

Please give me some suggestion. whether a sql server has this bug? how can i

fix it?

thx
alex

------------------------------------------------------------
CREATE PROCEDURE usp_UpdateRGNUPC
AS
declare @upcVar varchar(13), @skuVar varchar(9), @strStore varchar(7), @tempSql varchar(1000)

declare varTempShop cursor
local
static
FOR select distinct Shop from strmst
OPEN varTempShop
fetch next from varTempShop into @strStore
while @@FETCH_STATUS = 0
begin
declare varTemp cursor
FOR SELECT r.upc, r.sku
FROM rgnupc r
INNER JOIN strmst s ON r.rgnid = s.rgnid
WHERE s.Shop = @strStore
OPEN varTemp
fetch next from varTemp into @upcVar, @skuVar
while @@FETCH_STATUS = 0
begin
set @tempSql = 'select * from ' + @strStore + '.dbo.invupc where upc = ''' + @upcVar + ''''
exec(@tempSql)

if (@@rowcount>0)
begin
set @tempSql = 'Update ' + @strStore + '.dbo.invupc set sku = ''' + @skuVar + ''' where upc = ''' + @upcVar + ''''
exec(@tempSql)
end
else
begin
set @tempSql = 'Insert into ' + @strStore + '.dbo.invupc (upc, sku) values (''' + @upcVar + ''',''' + @skuVar + ''')'
exec(@tempSql)
end

fetch next from varTemp into @upcVar, @skuVar
end
close varTemp
deallocate varTemp

fetch next from varTempShop into @strStore
end
close varTempShop
deallocate varTempShop
GO

...全文