October 14, 2009 at 2:57 am
I have package in which I use a sequence container with transaction option 'required'. Following this container is a SQL Execute Task with transaction option 'Supported'. The sequence container (with the transaction) writes data to a remote SQL Server instance, the SQL Execute task uses the same connection, but does nothing (SELECT 1). The SQL Execute task always fails with the following error:
[Execute SQL Task] Error: Executing the query "select 1" 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.
If I run this package on the machine where the SQL Sever instance is installed, everything works fine. I have read that possibly the logging provider could be responsible for that. I turned logging of, still doesn't work. The error does not occur if I set the transaction property of the SQL Execute Task to 'required', but then the next task fails.
Can anyone please help me? Thanks in advance
October 15, 2009 at 6:06 am
Please, someone help! Any hints are welcome!
October 15, 2009 at 12:30 pm
What are you trying to do here? I agree that such a simple statement as SELECT 1 should work here, but I fail to understand what your desired end result is. Can you go into detail on what the purpose of that second step is?
October 15, 2009 at 3:11 pm
I put the SELECT 1 statement in there for testing purposes. In the real case a stored procedure is supposed to write some information to a DB table. The point I wanted to make is, that regardless of what I put in, the Execute SQL Task always fails right after the transaction sequence. It only works if i put transaction required for the SQL task as well, but as I said then the subsequent tasks that doesn't require to be enlisted in a transaction fails.
I only want a transaction to be started for the sequence container.
Any ideas? The problem must be with the remote SQL server, as the package runs flawlessly when executed on the remote server.
October 15, 2009 at 3:34 pm
October 16, 2009 at 12:33 am
It's an OLE DB connection, the provider is SQLNCLI.1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply