September 28, 2005 at 12:45 pm
I didn't get any response from a previous thread regarding this problem, so I'm opening a new thread with a new idea to resolve the issue. Unfortunately, the new idea isn't working and I can't determine why. The situation is that we are replicating data for five tables from a sql server database to an oracle database. Since moving the replicated database to a new server the oracle database has been having a problem with the shared pool filling up. Upon investigation it looks as if the replication is contributing to the problem as the transactions being replicated are using SQL statements that use literals instead of bind variables. For a quick fix we would like to alter the oracle session that is established during replication to use CURSOR_SHARING = 'FORCED'. That way any similar SQL statements plans will be reused instead of filling up the shared memory. In trying to accomplish this I created a procedure on both the SQL and oracle side. On the SQL side the stored procedure doesn't do anything, but on the oracle side it executes the ALTER SESSION SET CURSOR_SHARING = FORCE; statement. I can execute the procedure succesfully from oracle. Then I tried to include it in the publication with KEEP existing procedure and replicate execution every time it is executed. The problem is that when I execute the stored procedure on the SQL side, the distribution agent fails when replicating the execution with :
Syntax error or access violation Error number: 370000
Does anybody have any experience with SQL Server to oracle replication and know what the problem may be? Does anyone have any other ideas on how I can alter the oracle session created during the replication? This has been driving me crazy all morning, so any ideas will be appreciated.
TIA,
Michelle
September 29, 2005 at 1:55 am
Perhaps try a different client-side driver to connect to Oracle from SQL? Are you using the Oracle provided driver or the MS provided driver - switch to the other; maybe it uses parameterised queries where the other doesn't, or at least helps the situation in other ways.
September 29, 2005 at 6:51 am
Ian,
Thank you for your reply. When I originally setup the SQL Server to Oracle replication I tried both providers, the oracle provider resulted in the replicated data being case sensitive in oracle though, so we went with the MS driver. The good news is that I was able to resolve my problem by using the transform data task with replication. I guess in a way this also involved your suggestion since setting up the transform data task required me to change my connection to use the MS OLE DB for oracle driver instead of the MS ODBC for oracle. In setting up the transform data task package I used a VB script to transform the data instead of straight column mapping. Thanks again for your help.
Michelle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply