EXEC SQL TASK - Error

EXEC SQL TASK - Error

Post by <ssm.. » Sun, 20 Jan 2002 03:22:13



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

 
 
 

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

Hello,

Configuration :
- Windows 2000 SP3
- SQL Server 2000 SP2 + Analysis Services SP2
- Dts stored as .dts

Here is the situation :
- I have one main DTS, which launches several other 'small' DTS.
- Each DTS has several tasks.
- One of these 'small' DTS has an 'Execute SQL task' : exec
my_main_stored_procedure ?
(? is a parameter normal process/error correction)

The main purpose of this sp is to import datas from a denormalized table
into several normalized tables
- open a cursor for each line
- begin transaction

- get back the results of these sp
- insert a record with the results of these sp
- close transaction (if an error occured, rollback all the operations about
this line)

The lines can have errors ->  Errors are manually corrected. -> Then we
launch the sp with a different parameter indicating : error_correction

Problem :
The problems is the way we launch this sp.
- When I launch it manually  (Query Analyser),  it works fine.
- When this 'small' DTS with an 'exec my_main_sp' does, the process stops
after 7 errors (lines with errors). There is no DTS error. It is just that,
after 7 lines with errors, the other lines are not treated.
- When I use an ActiveX task  instead of the 'Execute SQL task', it works
fine

So it seems that the problems comes from the DTS layer, and the 'Execute SQL
task'

I've tried to give the more details, but i'm not sure to be clear.
Furthermore, i don't have any hints so any help will be appreciated.
Thanks in advance.

Jc

2. How to copy String() to clipboard??

3. error reporting from a cursor (exec sql task)

4. Displaying the results of a stored procedure

5. Exec Process task error

6. How do I detect col-level security prior to opening table

7. documenting TSQL code from DTS exec SQL tasks?

8. Error: ORA-01033: ORACLE initialization or shutdown in progress

9. Scheduled Tasks & SQL Exec Difficulties

10. Backup Problems with Sched Tasks and SQL Exec

11. Backup Problems with Sched tasks and SQL Exec

12. Global Variables at Exec sql Task

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