BUG: DTS Execute SQL Task Fails to Complete When Constraints are Violated (271144)



The information in this article applies to:

  • Microsoft SQL Server 7.0

This article was previously published under Q271144
BUG #: 58259 (SQLBUG_70)

SYMPTOMS

A Data Transformation Services (DTS) package that uses an Execute SQL Task to call a stored procedure may fail to complete if the stored procedure causes constraint violations. However, the Execute SQL Task status shows that the process completed successfully. If you query the destination table, there may be rows missing.

CAUSE

The DTS Execute SQL Task step is failing due to an exception. If you monitor the server by using the SQL Server Profiler while the DTS Package is running, Profiler shows that a 3617 exception error occurred. The exception error causes the procedure to stop running.

WORKAROUND

Use Execute Process Task instead of Execute SQL Task. In Execute Process Task, use either Isql.exe or Osql.exe to run the stored procedure.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.

MORE INFORMATION

Constraint violations (primary key violation and the insertion of null values into columns that do not allow null values) are non-fatal errors. Therefore, constraint violations should not cancel a batch. However, the batch is being canceled because an exception is being raised. Therefore, the stored procedure stops running, which could result in missing rows in the destination table.


Modification Type:MajorLast Reviewed:10/16/2002
Keywords:kbBug kbDSupport KB271144