Multiple transactions not working in package

I have a package with two sequence containers, each containing two SQL tasks and a data flow task, executed in that order. I want to encapsulate the data flow task in a transaction but not the SQL tasks. I have the TransactionOption property set to 'required' on the data flow tasks and 'supported' on the SQL tasks and the sequence containers. When I run the package I get a distributed transaction error on the first SQL task of the second sequence container:

"[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE DistTransTbl2" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The only way I can get the package to succeed is to set the TransactionOption = 'required' on the sequence containers and 'supported' on all subordinate tasks. This is not what I want, however. Any ideas?

Thanks,

Eric

Eric,

Can you please send me your package?

So far I couldn’t repro the problem.

|||

Eric, have you tried using a DELETE FROM statement instead of a truncate statement. Truncate doesn't log, so it's very fast, but is probably the reason you're blocking on your second task that's attempting to insert into the table.

K

|||

Hi

I am getting the EXACT same problem, but I am not using any TRUNCATE statements or similar.

Actually, I find that the problem only seems to exist when I try to enlist a Data Flow task in a transaction.

If I have 2 sequence containers, each containing an Execute SQL Task, everything works fine. If I try to replace one of these with a Data Flow Task, then I get the same error.

Does anyone have a solution to this?

|||I could not reproduce the issues reported.

Multiple transactions not working in package

I have a package with two sequence containers, each containing two SQL tasks and a data flow task, executed in that order. I want to encapsulate the data flow task in a transaction but not the SQL tasks. I have the TransactionOption property set to 'required' on the data flow tasks and 'supported' on the SQL tasks and the sequence containers. When I run the package I get a distributed transaction error on the first SQL task of the second sequence container:

"[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE DistTransTbl2" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The only way I can get the package to succeed is to set the TransactionOption = 'required' on the sequence containers and 'supported' on all subordinate tasks. This is not what I want, however. Any ideas?

Thanks,

Eric

Eric,

Can you please send me your package?

So far I couldn’t repro the problem.

|||

Eric, have you tried using a DELETE FROM statement instead of a truncate statement. Truncate doesn't log, so it's very fast, but is probably the reason you're blocking on your second task that's attempting to insert into the table.

K

|||

Hi

I am getting the EXACT same problem, but I am not using any TRUNCATE statements or similar.

Actually, I find that the problem only seems to exist when I try to enlist a Data Flow task in a transaction.

If I have 2 sequence containers, each containing an Execute SQL Task, everything works fine. If I try to replace one of these with a Data Flow Task, then I get the same error.

Does anyone have a solution to this?

|||I could not reproduce the issues reported.

multiple stored procedures executed simultaneously

hi,
i have an application that uses a table to track the last invoice number used. there are around 50 users simultaneously trying to retrieve the invoice number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from the workstation, then send it back to the server via an UPDATE, there is the remote possibility of some users retrieving the same number before it being updated by the first user. (the UPDATE issued by the first user can sometimes be delayed due to slow network)
can i use stored procedures to solve this problem? i figured that if i make the SELECT and UPDATE into a single stored procedure, even if i have 50 users calling it all at the same time, each call (SELECT and UPDATE) must execute completely before the next user's call can be executed. there by making sure that the invoice number is incremented before the next user can retrieve it from the table. is this a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...
> hi,
> i have an application that uses a table to track the last invoice number
used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.
> the problem is when i issue a SELECT to get the number, increment it from
the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)
> can i use stored procedures to solve this problem? i figured that if i
make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?
> thanks in advance.
> bob
>

multiple stored procedures executed simultaneously

hi,
i have an application that uses a table to track the last invoice number use
d. there are around 50 users simultaneously trying to retrieve the invoice
number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from th
e workstation, then send it back to the server via an UPDATE, there is the r
emote possibility of some users retrieving the same number before it being u
pdated by the first user. (
the UPDATE issued by the first user can sometimes be delayed due to slow net
work)
can i use stored procedures to solve this problem? i figured that if i make
the SELECT and UPDATE into a single stored procedure, even if i have 50 use
rs calling it all at the same time, each call (SELECT and UPDATE) must execu
te completely before the ne
xt user's call can be executed. there by making sure that the invoice numbe
r is incremented before the next user can retrieve it from the table. is th
is a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...

quote:

---

> hi,
> i have an application that uses a table to track the last invoice number

---

used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.

quote:

---

> the problem is when i issue a SELECT to get the number, increment it from

---

the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)

quote:

---

> can i use stored procedures to solve this problem? i figured that if i

---

make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?

quote:

---

> thanks in advance.
> bob
>

---

Multiple SQL Statements in one SQLCommand

I am just wondering if it is possible using SQL Server 2000 to have multiple SQL Statements executed with one sqlComm.ExecuteNonQuery(); call?

Absolutely. You can just string them together. (Or you can separate them with semicolons for clarity.)