I have some Views created in our SQL Server DB that use a
linked server to fetch data from Tables in an Oracle
database. I have built a very simple DTS packaged with
some Execute SQL steps to copy the Oracle data to SQL
Server tables. Each step just does an "Insert into select
*" SQL. Now when I run the package, if the second step
fails (say because of an SQL error), data from the first
step is still committed in the SQL Server table.

I have tried all combinations of package transaction
options (ie. Use transactions on and off, commit on
successful package completion on and off) all to no
avail. Data from first step is always committed. All I
can think of now is to put all the SQL statements in one
step and put explicit 'begin transaction' & 'commit
transaction' statements in. Is there no other way?

The MS DTC service is running on the server.

Thanks for any help.

Have you said

Workflow Properties for each task

"Join transaction if present"
"Rollback transaction on Failure"



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Royce Lithgo" <Royce.Lithgo (AT) sg (DOT) standardchartered.com> wrote

I put all 3 statements in one step and tried again, still
not working. Then I put 'begin transaction' at the start
of the SQL and got the following error:

The operation could not be performed because the OLE DB
provider 'MSDAORA' was unable to begin a distributed
transaction. So now I think I've found the actual cause
of the problem. I'm not sure why this is happening though.

Not all the drivers support being in a distrib transaction.
You did say MSDTC was started ?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Royce Lithgo" <royce (AT) roycelithgo (DOT) com> wrote

The MSDTC service has been started and is running.
I have applied the registry change to the server as per
Books Online (OLE DB Provider for Oracle).
I also modified the Linked Server properties as Provider
Name was not MSDAORA.

None of these changes have made any difference. I still
get the same error message.

I believe that from reading Books Online, distributed
transactions should work with Oracle.

Royce.

I have set the workflow options for joining transaction if
present. Now when I run the package I get the following
error:

The Microsoft Distributed Transaction Coordinator (MSDTC
service) is not available.

But the service is running on the server?

Is Oracle set up tp allow remote access ?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Royce Lithgo" <Royce.Lithgo (AT) sg (DOT) standardchartered.com> wrote