Data Transfer

[ratzp]

I hav a very serious problem using SQL Server and Oracle

I have 2 Database having same structure one in Oracle and other in MSSQL 2000

I want to transfer Data from Oracle to SQL Server

---- Using Code

con.CommandTimeout = 0
con.ConnectionTimeout = 0
con.Open "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=DatabaseSQL"
SP_ADDLINKEDSERVER 'Link','Oracle', 'MSDAORA','oracle.server' "
con.Execute "exec SP_ADDLINKEDSRvloGIN 'Link',FALSE,'sa','scott','tiger'"
con.Execute "SET XACT_ABORT ON"
con.BeginTrans
con.Execute "Delete from Table1"
strsql = " Insert into TABLE1 SELECT * FROM [Link]..scott.table1 "
con.Execute strsql
con.CommitTrans
con.Close

-----Code End

This Code gives error stating that 2 Transactions cannot be active...

Code runs properly when Transaction is not used

I need a solution using Transaction for the above code

 

[SQLSister]

Use a stored proc to do the transaction. You also need error trapping to rollback both transactions if there is a problem. This is particularly important when using a linked server because even if there are no data problems a networkproblem could cause this to stop inthe middle.


You could also do this in a DTS package and schedule it to run.


Questions about posting. See faq183-874