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

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

Post by jc.cheni » Wed, 20 Nov 2002 01:12:03



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

 
 
 

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

Post by Ana Isabel Can » Wed, 20 Nov 2002 20:13:47


We have exactly the same problem.

After installing SP2 of SQL Server 2000 an Execute SQL Task wich runs an
parametrized stored procedure began to fail without reporting it.

It used a CURSOR for deleting orphaned rows. When i ran it on Query Analyzer
it worked fine, but not within the DTS package.

I changed the Stored procedure to use just a DELETE statement (wrapped into
the Stored Procedure) but it also failed inside DTS, so it is not a problem
of cursors.

Finally I wrote the SQL statement directly into the SQL task, since then it
started to work fine.

The curious part of the story is that when the number of records to delete
were 10 it worked fine, when it was bigger, about 200 it failed.

I use Transactions in the DTS package, the Execute SQL Task is configured to
join the existing transaction and to fail the whole package if the step
fails.



> 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
> - launch several other sp one after the other (each transaction handle an

> - 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


 
 
 

1. DTS Transform Data Task fails in job where DTS Execute SQL Task does not

I have experienced the following issue in several DTS packages.  I use
Transform Data Task to transfer data from a table on one SQL Server to a
table on another SQL Server.  The package executes successfully when run
manually and fails with the error shown below when run from a job.

However, if I use an insert statement in a Execute SQL Task to accomplish
the same task,  the package executes successfully when run manually and when
run from a job.

Can anyone explain why jobs running with Transform Data Task sometimes fail
as shown below?

 DTSRun:  Executing...   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnStart:
DTSStep_DTSDataPumpTask_1   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1;
1000 Rows have been transformed or copied.; PercentComplete = 0;
ProgressCount = 1000   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 2000
Rows have been transformed or copied.; PercentComplete = 0; ProgressCount =
2000   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 3000 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 3000   DTSRun
OnProgress:  DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 4000   DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 5000   DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 6000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 6000   ...  Process Exit Code 1.  The
step failed.

2. Unserialize

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

4. Using Parameters in Filter Stored Procedure For Replication

5. DTS SQL Task: How to Log Errors Without Complete Failure

6. pgsql/src/interfaces/libpq fe-connect.c

7. Execute DTS from asp.Net failure because that package contain execute SQL task

8. Silverrun CASE

9. Returning an error from a DTS Execute SQL Task calling a Stored P rocedure

10. Error handling in DTS Execute Sql Task

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

12. documenting TSQL code from DTS exec SQL tasks?

13. Stopping SQL Server without stopping SQL Agent