-
Oracle Transparent Gateway for MSSQL
I have recently set up transparent gateway to access MsSql but we are having some issues with and I am wondering if anyone else has encountered this issue. We are running stored procedures in oracle (9.2.0.4) connecting to SQL Server 2000 through the gateway
sometimes everything runs fine and other times we get an error
ORA-02055: distributed update operation failed; rollback required
ORA-20061: STYLE insert failed, key=417709
ORA-06512: at "WLE.RFD_PLU_TRANSMIT", line 682
ORA-06512: at "WLE.RFD_PLU_TRANSMIT", line 203
ORA-06512: at line 1
ERROR:
ORA-02067: transaction or savepoint rollback required
The ORA-02055 seems to indicate a duplicate key trying to be inserted but I can't see a duplicate when I look at the data.
Weird. It's almost like some kind of lag between the two databases is trying to insert the record twice on the sql server side.
Also when I do a select * from the sql server table it takes almost 34 seconds to return 400 rows. It seems like it is servering up 10 records at a time. Any thoughts on how to improve the performance. Or should I change to hetrogenous connectivity or start using dts and drop the whole idea of using the gateway? Anyone who has hints for using the tg4msql out there?
Thanks
-
Not sure I can help you much here, but I'll give it a go.
I've hooked it up a couple times for small jobs but have been most successful using DTS to replicate the data to Oracle and let it host and serve the data.
I'm sure there's a setting to optimize the chunk size and I'm guessing that 2-phase commits are tough to support across Oracle and SQL Server.
Seems to me that the most trouble free model has been a staging table on the Oracle side, a DTS package to push records, and a trigger or job to merge the records and report issues on the Oracle side.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|