I've had a DTS Package running for over a day now. It was 20 hours
into an "Execute SQL Task" and I canceled the package because there is
an error.

In the past, the package has taken almost a day to cancel. I fear that
SQL is trying to rollback all the SQL in that task.

Is there a way to force SQL Server to cancel everything? I don't care
if it's only partially processed.

You can find the spid the package is running as using sp_who2 and kill that
spid. using kill <spid>

"John" <john.hurrell (AT) frontiercorp (DOT) com> wrote

This will still cause a rollback of whatever has been written.

Try, if possible, calling dts package with variables, essentially batching
it
so a smaller amount of work is performed with each iteration.

HTH

"Peter Hall" <pete_ha11 (AT) hotmail (DOT) com> wrote

In article <9025d995.0309180715.6dbbfa3a (AT) posting (DOT) google.com>, John
<john.hurrell (AT) frontiercorp (DOT) com> writes
There may be a rollback in SQL, or even at the DTC level if you have set
package transactions on.

You should be able to see any rollback in the server by using sp_who2 on
the server.

You could try killing any process you see from DTS, or just restart the
SQL Server service if that fails to kill the connections.

If there are no connections from DTS to SQL Server then it is probably
hung. Restart the machine or End Task for Enterprise Manager.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org