can't replicate table with column name too long (from sql server 2005 to oracle)

cg2001 (Cristina Vancea)


I have a table in SQL Server 2005 who has a column with a name longer than 30 characters. The method I use for replication is transactional and the subscriber is an Oracle Subscriber. The error I get when trying to synchronize is an oracle error : "Identifier too long" and this appears when executing the "create table" statement on the Oracle side.

Do you have any idea how can I resolve this (I know I can take out that colum from the article but I need to replicate the data in it).

And another question : Does anyone know how can I monitor the replication without using the Replication Monitor but using stored procedures

Thx,
Cris.

Michael Hotek

Look up the sp_replmonitor* stored procedures in BOL. That most of the replication monitor functionality.

Hilary Cotter

I am not sure what you are asking

You can use a custom creation script to create a table on your oracle subscriber with a shorter name.

If you are interested in looking at the error you should be able to see this error in msrepl_errors.

I normally use sp_repltran exectued in the publication database to see how many replicated commands have to be picked up.

I use sp_replcounter in the publication database to get an idea of distribution agents health.

I also query select * from distribution.dbo.msdistribution_status to check on how a breakdown of how many commands have to go to the subscriber by article.

Other than that I query msdistribution_history for more info on the health of the agents.

cg2001

I know I can use a custom script, but the problem is that the replication will still delete that table (that was already created on the Oracle side) and try to create a new one with the same column names as in Sql Server.

And even if I have the table already created (in Oracle ) how will Sql Server know how to map the columns in Sql Server to the ones in Oracle Are there any options in the replication that control this

Michael Hotek

You can't do this through the GUI. In order to accomplish what you are trying to do, you have to use the @creation_script parameter for sp_addarticle and specify your own creation script for the subscriber. You then also need to use the @ins_cmd, @del_cmd, and @upd_cmd parameters and write your own custom replication stored procedures.

Hilary Cotter

Microsoft has already done the mapping for you. Consult

http://msdn2.microsoft.com/en-us/library/ms147856.aspx

for more information.

cg2001

Hi,
I have tried what you suggested and I have a problem. Although I used the @creation_script parameter to use a "create table" file of my own and I also used the other 3 parameters and specified my own procedures, the replication of the table still doesn't work. Instead of using my "create table" script it still uses the "create table" script generated by Sql Server replication process and I again get the error : "Identifier too long".

Am I doing something wrong This is the code for adding this article:

exec sp_addarticle
@publication = N'test_pub',
@article = N'tbl_test',
@source_owner = N'dbo',
@source_object = N'tbl_test',
@type = N'logbased',
@description = N'',
@creation_script = N'C:\Documents and Settings\aa\Desktop\replication\create_table.sql',
@pre_creation_cmd = N'none',
@schema_option = null,
@identityrangemanagementoption = N'none',
@destination_table = N'tbl_test',
@status = 0,
@vertical_partition = N'false',
@ins_cmd = N'CALL pkg_tbl_test.insert_test',
@del_cmd = N'CALL pkg_tbl_test.upd_test',
@upd_cmd = N'CALL pkg_tbl_test.del_test'

Michael Hotek

You created a table creation script on the desktop. Does the account that the distribution agent is running under have access to that directory

Hilary Cotter

you need to set the schema_option to 0x00

cg2001

I tried that too, but still there waS a problem with the creation script. I found it really odd that the script to be executed at the Subscriber (Oracle subscriber) should be written in Sql Server code and not Oracle. I also found the Sql Server is not capable of transalting any code into Oracle but only the simplest one.
Thank you very much for your advices.

Hilary Cotter

Can you post the schema of the table you are trying to replicate and what it should look like on the oracle side here, as well as the version of oracle and SQL Server you are using

cg2001 (Cristina Vancea)

Hi, the problem is fixed now, I had the table replicated . I was using Sql Server 2005 Standard Edition and Oracle 10g.
Thank you very much for your help.