Weird DTS problem - SQL Server Forums

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)

Weird DTS problem

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2004-07-07 : 17:51:16

I have the stored procedure below. When I run it in Query Analzer it works fine (runs thorugh several thoasand rows) but when I run it as part of a DTS, it only goes through 11 rows - no errors. What could possibly cause this?
=========================================================

CREATE PROCEDURE [dbo].[pmCode_compile_all_items_mfgpro] AS
DECLARE @itemVar char(5)

delete from tbl_crossPlant_PMCodes_Compiled

--for drawings
DECLARE c1 CURSOR FOR

SELECT distinct(left(item,5)) as item
FROM [ana no dups]
where item like '[0-2][0-9][0-9][0-9][0-9]-%'
and (not status = 'obsolete' or not status = 'phaseout')
--order by item

OPEN c1

FETCH NEXT FROM c1
INTO @itemVar

WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @itemVar

exec pmCode_compile_per_item
@item = @itemVar,
@itemType = 'D'

FETCH NEXT FROM c1
INTO @itemVar
END

CLOSE c1
DEALLOCATE c1
GO

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-07 : 19:15:49

The query looks fine to me. The only thing I can think of is somehow the cursor is messing up. I would have a step in your DTS package that selects the records into a table so you can monitor this. Also, try getting rid of the cursor. You shouldn't really use it anyway. :)



CREATE PROCEDURE [dbo].[pmCode_compile_all_items_mfgpro] AS

DECLARE 
	@itemVar CHAR(5),
	@min INT,
	@max INT

DELETE tbl_crossPlant_PMCodes_Compiled

--for drawings
DECLARE @items TABLE(
	ident INT IDENTITY(1,1) PRIMARY KEY,
	item VARCHAR(5))

INSERT @items(item)
	SELECT DISTINCT (LEFT(item,5)) AS item
	FROM [ana no dups]
	WHERE
		item LIKE '[0-2][0-9][0-9][0-9][0-9]-%'
		AND (status <> 'obsolete'
			AND status <> 'phaseout')

SELECT 
	@min = 1,
	@max = (SELECT MAX(ident) FROM @items)

WHILE @min <= @max
BEGIN

	SELECT @itemVar = (SELECT item FROM @items WHERE ident = @min)

	EXEC pmCode_compile_per_item @item = @itemVar, @itemType = 'D'

SELECT @min = @min + 1
END

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources