Web Development

General discussion

Gravatar
Locked

DTS

By vinaysm ·
Hello,
Database : SQL Server 7.0.
Provider : Microsoft OLE DB Provider for Oracle.

Using DTS I am creating a package which will export data from SQL Server tables to Oracle tables. The table structures in both databases is identical.

I wish to use transactions. If any problem arises and the data from particular table cannot be inserted to oracle table, then I want the entire transaction to rollback.

I have enabled the Use Transaction option for the DTS package. Also for the stepsI have checked on Join Transaction which will join the steps to the existing transactions.

However when I execute the package I get the following error.
"Task 1 does not support distributed transactions or failed to join"

I have tried using Oracle Provider for ODBC as well as Microsoft ODBC driver for Oracle, the error in both the cases was same.

Can any one suggest what could be done to use distributed transactions when transferring data from SQL to Oracle.

In the reverse scenario, transferring data from Oracle to SQL, using the Microsoft OLE DB Provider for SQL, everything works fine without a hitch.

Thanks & Regards,
Vinay

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

DTS

by surinderx In reply to DTS

Hi Viney

I will suggest you to logon : akadia.com

The sight contains very important tips for Oracle and SQL, say a compelete solution Home.

gravatar
Collapse -

DTS

by vinaysm In reply to DTS

The question was auto-closed by TechRepublic

gravatar
Collapse -

DTS

by MooseMaster In reply to DTS

Did you try using the Oracle Provider for OLE DB? I have found that Provider to work fairly well (fast, reliable). You may want to check to see if there are any table constraints on the Oracle side.

www.sqldts.com may have some answers for you. Ihope that helps.

gravatar
Collapse -

DTS

by vinaysm In reply to DTS

The question was auto-closed by TechRepublic

gravatar
Collapse -

DTS

by maxwell edison In reply to DTS

Microsoft Knowledge Base Article - Q290212

List of Bugs Fixed in SQL Server 2000 Service Pack 1 (1 of 2)

REMOVE SPACES From the pasted URL

gravatar
Collapse -

DTS

by vinaysm In reply to DTS

The question was auto-closed by TechRepublic

gravatar
Collapse -

DTS

by vinaysm In reply to DTS

This question was auto closed due to inactivity

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Start or search

Related Discussions

Related Forums