I'm having a problem with a couple of Execute SQL Tasks in my packages,
with multiple DELETE statements.

If I put a GO statement anywhere, between any of the statements, I get
a syntax error. "Deferred prepare could not be completed. Statement(s)
could not be prepared. Incorrect syntax near GO" when I click the
parameters button.

Essentially i'm trying to do:

DELETE FROM TABLE1
DELETE FROM TABLE2 WHERE [ID] = ?
DELETE FROM TABLE3 WHERE [ID] = ?

Passing in 2 parameters, which happen to be the same ID number.

I can run this step fine by setting the param, then right clicking the
step and choosing execute, but thats not how I want to use it.

I'm calling this package from VB, and when this step starts, (Its
actually a step to do rollback if there's been an error) the VB hangs,
and never returns. If I only have one statement in my Execute SQL Task,
all is fine.

I've seen similar problems in other posts, but nothing with a
suggestion or solution, so i'm stumped.

-Doobs

Doobs,

Why can't you break it down onto three steps? Another way of dealing with it
would be to have an ActiveX and Dynamic Properties tasks composing the
statement with parameters prior to Execute SQL. Yet another way would be to
have error handling and rollback in the task itself. Yet another...

Ilya

Ilya

<mattdeubert (AT) hotmail (DOT) com> wrote

I have tracked down my problem, and it was nothing to do with multiple
statements in an Execute SQL Task.

What was actually happening was to do with transactions and locking.

My package had been set to use transactions, and an early step in the
package would commit the transaction on success. At some point I had
removed the "Commit on Success" flag, and stopped all steps from using
the transaction, but I had not unset the flag telling the package to
use transactions.

For some reason, although no steps had joined it, this transaction was
holding a lock, preventing me from deleting from a particular table in
a later step.

What still puzzles me is that another path through the package was
deleting from the same table with no trouble, but I'm not going to
spend any more time tracking that one down at the moment.

-Doobs