Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
HC@ZZF
Starting Member
5 Posts |
 Posted - 2007-06-14 : 20:13:33
|
| I created a link from Oracle to SQL server.There is a trigger with insert statment in Oracle side. I got error message as follow when trigger is invoked:SQL> insert into test1 values ('zerbra','brazi');insert into test1 values ('zerbra','brazi') *ERROR at line 1:ORA-02047: cannot join the distributed transaction in progressORA-06512: at "GGWEB.TRIGGER2", line 4ORA-04088: error during execution of trigger 'GGWEB.TRIGGER2'my simple trigger is as follows:create or replaceTRIGGER TESTRI AFTER INSERT ON TEST1 FOR EACH ROW BEGIN insert into test1@sqlserver (city,state) values ('what','nine'); END; |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-14 : 20:52:00
|
| Is it Oracle issue? |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-15 : 04:11:05
|
| this is wrong........ WRONG!!!even if it worked this has the potential to slow down your system a lot.put those insert into a separate table in your oracle database and periodicaly import them to sql server._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
HC@ZZF
Starting Member
5 Posts |
Posted - 2007-06-15 : 11:44:56
|
| can you tell how to import to SQL from Oracle priodically and automatically? Thanks! -Henry |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-15 : 11:49:14
|
| one way i can think of is to export data to txt files nad import that to sql.other is to have a job on sql that connects to oracle and uses openquery to connect to oracla and imporets that.yet another is to use a SSIS package for transfer.maybe oracle has more options but i have no idea about that._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-15 : 12:38:00
|
if you do decide to implement this trigger, you should also have your trigger pop a messagebox asking the user to confirm that they indeed do want the rows moved over to sql server. 
elsasoft.org |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-18 : 04:51:26
|
| "other is to have a job on sql that connects to oracle and uses openquery to connect to oracle and imports that."This is what we do:Our client have, for example, an Oracle database; a SQL Server local to the Oracle server [i.e. on the same LAN], and a Remote SQL Server ([e.g. at their ISP].We want to transfer changes to Product and Price table (and a bunch of others too - Orders, Customers, you get the picture!)We have a Stored Procedure on Local-SQL-Server that queries Oracle-Server. Luckily the Oracle database has a "Changed Date" on all tables. So we "pull" all rows from Oracle with a change-date more recent that the most recent record we have.We store the changed rows in "Staging" tables on the Local-SQL_Server. We only store the columns we are interested in (for us this is not all the columns in the Oracle table). We only Update the Local-SQL-Server table if there is a difference in the Columns we are actually storing - so the fact that it has a new Changed Date in Oracle doesn't necessarily mean it actually causes an Update on Local-SQL-Server.We have our own Changed Date on the Staging tables on Local-SQL-Server, and we update that whenever we Update a record from Oracle. And we use that date to make the Update to the Web site.We schedule a task on Local-SQL-Server to call the Stored Procedure that "Pulls" data from Oracle, and then it calls an Sproc on Remote-SQL-Server which basically does the same thing, it pulls data from Local-SQL-Server and stored into staging tables on Remote-SQL-Server.Local-SQL-Server and Remote-SQL-Server have identical table structures for the staging tables - so we can just transfer EVERYTHING that has a newer Changed Date on Local-SQL-Server. (The Bandwidth from Local to Remote is much less than locally, so this route means that we transfer the minimum across our low-bandwidth route).The procedure at the Remote-SQL-Server Pulls the data from Local-SQL-Server, as I mentioned, into Staging tables, and then deals with the update of the actual tables on the Remote-SQL-Server. This is the stage at which we "convert" the data from the schema on the Oracle database to match our own schema.The queries between the various servers are done from the "pulling" end, and using OPENQUERY()Kristen |
|
|
|
|
|
|
|
|
insert data to MSSQL within trigger from Oracle