Dear All,

In short:
Once one task within a package has joined a transaction in a linear progression
on a single thread, it appears that *all* succeeding tasks are forced to join
the transaction (whether or not they are configured to).

This creates major havoc when a task's script contains commands that *can't* be
part of a transaction.

In short, all such steps must be discluded from the transaction, and put to the
beginning of the execution path, with those scripts that require
"transactability" going to the end.

Can anyone confirm this behaviour?


In detail:

I have a simple DTS package that is made up of a single, linear progression: one
SQL Script Task after another, each executing upon the successful completion of
the last. They all use the main thread.

If all items are made to join the DTS transaction (MSDTC), it works perfectly,
including rollback, thanks to MSSQL server.

My problem arises when I need to include in my SQL script tasks, some of those
database management, database server or database property commands that
*won't* run within a transaction.

I'm happy *not to transact* them, and just let them run it order, but that
doesn't seem to work.

Put simply, for any Exec SQL Task that contains a command that "can't exist in a
transaction", the package ONLY works provided none of it's *predecessors* have
joined the transaction.

For example, if I run a package that looks (conceptually) like this:

Task1: Create a database "foo" ++ (join transaction? Yes, but I'm asking
for trouble!)
Task2: create a table or two (join transaction? Yes: )
Task3: update some data, etc... (join transaction? Yes: )

I get this error: Task1 Failed - "CREATE DATABASE statement not allowed within
multi-statement transaction", because a script containing a statement like
"create database" isn't going to run in a muli-statement
transaction.

So, I compromise, leaving Task1 out of the transaction, and make my package
look like this instead...


Task1: Create a database "foo" ++ (join transaction? *NO*: I don't
need to anyway)
Task2: create a table or two (join transaction? Yes: Is rollback
working? Yes)
Task3: update some data, etc... (join transaction? Yes: Is rollback
working? Yes)


No problems! It works! Ok, the create db isn't in the "rollback plan, but
that's ok by me, I can make that step
repeatable anyway with "if exists"

But what if I add another "transaction unfriendly step", further down the line?

Task1: Create a database "foo" ++ (join transaction? No: I don't need
to anyway)
Task2: create a table or two (join transaction? Yes: Is rollback
working? Yes)
Task3: update some data, etc... (join transaction? Yes: Is rollback
working? Yes)
Task4: alter a db property... ++ (join transaction? No thanks... )


Task4 gives me the error (that it would if it was made to join the
transaction):

"<Some command in Task4> statement not allowed within multi-statement
transaction"

and Task4 have worked!

But...

If I move Task4 to *position* 2 in the linear order of my Tasks, what
happens?

Task1: Create a database "foo" ++ (join transaction? No; I don't
need to anyway)
Task4: alter a db property... ++ (join transaction? No thanks... )
Task2: ... (join transaction? Yes)
Task3: . (join transaction? Yes)

It works!! No problems!

Do you have any idea why this is the case? Or am I ovelooking something
fundamental.
Please feel free to ask me about specifics that you believe might
be relevant.

Thanks in advance!
Tim

------------------

++ The content of these scripts is just for example. The script might
contain any one of a number
of commands that can't run within a transaction in order to cause
this problem, for example:
* alter database
* create database
* reconfigure
* drop database
* sp_password, sp_addsrvrolemember etc...

Joe,

Thanks for a detailed post, it helps eliminate guessing.

The problem is that the following is not allowed even in a simple Query
Analyser test-

BEGIN TRANSACTION
CREATE DATABASE Fred
COMMIT TRANSACTION

Server: Msg 226, Level 16, State 5, Line 2
CREATE DATABASE statement not allowed within multi-statement
transaction.


See "Transact-SQL Statements Allowed in Transactions" in SQL Server
Books Online to see which statements will cause this problem.

In your case I know you actually can do without the transaction, but it
seems that once the connection has been enlisted, and cannot become
un-enlisted on a per task basis.

One method might be to set the connection to close after each task, but
probably an easier and logically clearer solution would be to have an
additional connection for those tasks that don't need to be part of the
transaction. (Untested, but makes sense).


In message <51b881b2.0407050256.17da3b0d (AT) posting (DOT) google.com>, Joe
<shermerhorn (AT) hotmail (DOT) com> writes
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

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

Thanks Darren! This was an concise and accurate solution to my problem!

The only bad news is that I've got a bigger, nastier post

Thanks for your help,

It's on its way in a new thread.

J


Darren Green <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote