Monday, March 19, 2012

bug when running sp at package

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

my code is as follow.

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

one sql server 2000 is service pack 3. another is service pack 4.

Alex

No comments:

Post a Comment