August 12, 2011 at 10:47 am
I am setting up transactional replication to replicate one table from SQL Server 2008 R2 to Oracle 11g. I created the table in Oracle schema. There are some dependencies on this table and we cannot allow SQL Server to Drop and Re-Create the table in oracle, so I set the option "Keep existing object unchanged" in the "article->destination object->Action if name is in use" property in the publication setup.
When we apply the snapshot, it try to create the object on Oracle Database and gives below error.
Command attempted:
CREATE TABLE UCS_USER(
XXXXXXXXXX
(Transaction sequence number: 0x0000006800001B0800C800000000, Command ID: 5)
Error messages:
• ORA-00955: name is already used by an existing object (Source: MSSQL_REPL_ORACLE, Error number: 955)
Get help: http://help/955
• ORA-00955: name is already used by an existing object (Source: MSSQL_REPL_ORACLE, Error number: 955)
Get help: http://help/955
If I set the Option “Drop existing object and create a new one” It works.
Any help please. I want to keep the existing object unchanged.
August 12, 2011 at 12:35 pm
tajinder42-663112 (8/12/2011)
CREATE TABLE UCS_USER...Error messages:
• ORA-00955: name is already used by an existing object (Source: MSSQL_REPL_ORACLE, Error number: 955)
From the Oracle point of view the operation is attempting to create table UCS_USER which already exists then you get ORA-00955 - Can't create an object that already exists.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 12, 2011 at 3:26 pm
UCS_User table is already created in the Oracle Database. We don’t want SQL Server to drop and re-create this table when it apply the initial snapshot to synch both tables. For this requirement, I selected the Option in SQL Server publication “KEEP EXISTING OBJECT UNCHANGED” and “CREATE SCHEMAS AT SUBSCRIBER” FALSE AS PER SQL Server Books online but still it try to recreate the table and this error occurs ORA-0095.
August 14, 2011 at 4:15 pm
tajinder42-663112 (8/12/2011)
UCS_User table is already created in the Oracle Database. We don’t want SQL Server to drop and re-create this table when it apply the initial snapshot to synch both tables. For this requirement, I selected the Option in SQL Server publication “KEEP EXISTING OBJECT UNCHANGED” and “CREATE SCHEMAS AT SUBSCRIBER” FALSE AS PER SQL Server Books online but still it try to recreate the table and this error occurs ORA-0095.
Yes. Process is issuing a "CREATE TABLE UCS_USER..." statement on a database/schema where such object already exist. I'm sorry if I wasn't clear on my first post.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 15, 2011 at 8:08 am
Thanks a lot! paul
But i am looking for the work around that SQL Server does not issue the Create table statement to Oracle when I selected the Option in SQL Server publication “Keep Existing object unchanged” and “Create schema at subscriber “ as False.
August 18, 2011 at 9:15 am
Have you checked the table_name on casesensivity? tblUser<->TBLUSER.
It is possible to select from tblUser?
*Can't view the actual tablename in "reply"
December 12, 2011 at 5:40 am
Hi Jo Pattyn,
I have the same problem/error (955). The only difference is that in my case i have SQL Server 2008 (not R2) and the suscriber is Oracle 9i database.
Do you know any workaround to solve this error?
Many thanks.
December 12, 2011 at 6:27 am
I'm not familiar with crossdatabaseplatform replication. What ora-... error do you get?
December 12, 2011 at 7:37 am
Hi Jo Pattyn,
I receive the same error that 'tajinder42-663112' user: 'ORA-00955: name is already used by an existing object'.
I can create the Oracle suscription correctly, but when i want to reinitialize the suscription i receive the mentioned error message. I don't understand why the Sql Server replication system attempts to recreate the table when i have defined de article property 'Action if name in use with 'Delete data. If article has a row filter, delete only data that matches the filter.' value.
Any idea how i can solve this problem?
Many thanks in advance.
December 12, 2011 at 8:25 pm
I've found an older reference of the problem. You might check the creation script on mixed case.
Otherwise try to setup a trace on the oracleside to see what sql server tries do and failing to detect your table.
Another reference is regarding to manual mode
December 12, 2011 at 9:05 pm
I could not find any work around for this but just did a trick. When ever i need to re-initialize the subscription, i stop the distributor agent and then create the snapshot. Once the snapshot is created, i update the snapshot scripts by removing the create table statement and then save it, Start the distributor agent , it apply the snapshot without creating the table in oracle database.
December 13, 2011 at 1:31 am
Thanks for sharing the workaround
December 15, 2011 at 9:26 am
Thanks both for sharing your workaround.
I find other workaround. After the initial suscription creation i changed the property 'schema_option',
"0x01 --> Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on)." with 0 value for all articles existing in my publication executing sp_changearticle stored procedure (see 'http://msdn.microsoft.com/en-us/library/ms175980.aspx' for more information). With this configuration you can see that in the created new snapshot scripts there is not object creation sentence, and with this form all the next reinitialize are doing successfully (because i only want to delete article table rows after a reinitialization).
I hope it helps.
December 19, 2011 at 12:01 pm
Thanks a lot! for sharing workaround.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply