Microsoft KB Archive/271144

= BUG: DTS Execute SQL Task Fails to Complete When Constraints are Violated =

Article ID: 271144

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



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.

Additional query words: DTS; stored procedure; exception; stack trace

Keywords: kbbug kbpending KB271144

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.