"Unspecified Error" in part of DTS package step

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

Old Forums

CLOSED - General SQL Server

"Unspecified Error" in part of DTS package step

Author

Topic

homebrew
Posting Yak Master

114 Posts

Posted - 2003-11-17 : 16:34:43

I'm running a DTS package that has been fine for months, but I just installed SQL 2000 SP3, and now I get this error message during part of one step (the other commands in the step are ok) :

-------------------------------------------
Error Source: Microsoft OLE DB Provider for SQL Server

Error Description: Unspecified Error
---------------------------------------------

The code I'm trying to execute is:

SET NOCOUNT ON
declare @prd_typ char (2)
DECLARE products CURSOR FOR
select * from produpd where prd_type = ' '

OPEN products

FETCH NEXT
from products
WHILE @@FETCH_STATUS = 0
BEGIN

exec ('update produpd
SET produpd.PRD_TYPE = (SELECT top 1 producttypeval.prod_type FROM producttypeval
WHERE produpd.prod_type_adv = producttypeval.prod_type_adv )
where prd_type = '' '' and @@error = 0 ')

FETCH NEXT
from products
end

CLOSE products
DEALLOCATE products
-----------------------

If I execute that code in Query Analyzer, it works fine .... there's now something screwy about the connection in my DTS, even though I haven't modified it in months.

Any ideas ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 16:41:31

Why the use of dynamic sql? Your UPDATE statement does not require this. Also, use an explicit column list instead of SELECT *. Why the use of a cursor? Can't you run the UPDATE statement by itself. You aren't using the cursor in the statement as far as I can tell.

Have you tried creating a new DTS package with just this task to see if it works there. It is possible that the step is corrupted in some way.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources