error reporting from a cursor (exec sql task)

error reporting from a cursor (exec sql task)

Post by Euan Garde » Fri, 02 Mar 2001 16:23:28



If you want to use DTS for this you might be better using a Data Driven
Query Task.

-Euan


Quote:> I need to loop through every row of a source file and then insert/update
> appropriately.  One of my requirements is to log the details of every
> insert/update that fails.  I have audit job and audit task tables set up
> that I write to for every task.

> What is the recommended way of erroring out row level details from a
cursor
> that gets executed from an Execute SQL Task?

> Mike

 
 
 

1. EXEC SQL TASK - Error

Hi,
(sql2000 + sp2)
I am using an Exec Sql Task to execute a stored procedure
which returns an return code and two output parameters.
I have hooked up the parameters and return codes to output
parameters within the exec sql task.
The problem I am having is that the sp raising a sql error
back to dts.
The sp will update and insert values into a tables from

violation/pk violation. (based on the error handling
procedures discussed in bol)
When I run the same sql used in the task from within query
manager, I get the sql server fk violation error message
but I can also retrieve the return codes and parameters

The problem is that the exec sql task is aborting before
the step finishes and i do not get the return codes or
output variables.

What is the best method of doing this if I want to check
for these errors - Is there any way of surpressing the
error messages such that the task is not aborted?

SQL Task Extract:

begin





end

SQL SP:


AS
begin
-- Declare variables used in error checking.
set nocount on





-- Execute the UPDATE statement.



-- variables before they are cleared.


specified
-- the UPDATE statement returns a foreign-key violation
error #547.

BEGIN

   BEGIN
      /*print 'ERROR: Invalid ID specified for new
publisher'*/
      RETURN 1
   END
   ELSE
   BEGIN
      print 'ERROR: Unhandled error occurred'
      RETURN 2
   END
END




BEGIN
   print 'Warning: The title_id specified is not valid'
   RETURN 1
END
ELSE
BEGIN
   print 'The book has been updated with the new publisher'
   RETURN 0
END

end
go

2. Passing variables to MS Office

3. Execute SQL task (exec my_sp) pb, DTS stops without error

4. How to create three tier with VB5

5. Exec Process task error

6. VA-Tysons Corner-103724--C++-UNIX-ORACLE-Object Oriented Devel.-C++/Unix/Oracle Developer, THE MYTA CORPORATION

7. SQL Agent Task stops without completing but no error is reported

8. Hardware Requirements for Oracle 8i with Linux?

9. Backup Problems with Sched Tasks and SQL Exec

10. Changing a DTS connection data source does not propagate to SQL Exec Tasks

11. declare curW cursor for EXEC spMySproc - Trying to assign sp resultset to cursor variable

12. Backup Problems with Sched tasks and SQL Exec

13. documenting TSQL code from DTS exec SQL tasks?