Oracle 9i -> SQL Server 2005: Schema_option parameter when adding an article to a publication

First of all; My Oracle publication works fine when I don't explicit specify the shema_option parameter for the articles I'm adding to the publication. The reason why I then want to explicit specify the parameter is as following.

I'm developing a replication solution to get data from our production server (Oracle) to our Data Warehouse (SQL Server). The SQL Server (and the Data Warehouse code) uses the SQL_Latin1_General_CP1_CI_AS collation. When I don't explicit specify the schema_option, the nvarchar columns of the replicated tables are created using the SQL_Latin1_General_CP1_CS_AS collation and this results in some comparison errors, when for instance a select statement is trying to compare two nvarchar strings using different collations.

I've tried to specify the schema_option parameter as "@.schema_option = 0x80" (Replicates primary key constraints.) to avoid the use of the SQL_Latin1_General_CP1_CS_AS collation when creating the destination tables - I'm not sure it's enough? No matter what, I'm getting an error when I'm doing it (see below).

Message
2006-07-13 12:00:15.529 Applied script 'ITEMTRANSLATION_2.sch'
2006-07-13 12:00:15.544 Bulk copying data into table 'ITEMTRANSLATION'
2006-07-13 12:00:15.544 Agent message code 20037. The process could not bulk copy into table '"ITEMTRANSLATION"'.
2006-07-13 12:00:15.591 Category:NULL
Source: Microsoft SQL Native Client
Number: 208
Message: Invalid object name 'ITEMTRANSLATION'.
2006-07-13 12:00:15.591 Category:NULL
Source:
Number: 20253

The questions are now whether I actually have a schema_option alternative for Oracle Publishing? If so, what is the solution, and eventually how can I avoid the error stated above?

If I'm not able to avoid the article columns getting created with the "wrong" collation, is there then any other obviously solution to the problem?

Thanks!
Best regards,

JB

Ok, now I've found out how to use more of those schema options together.

The default value for Oracle Publications (according to BOL) is 0x050D3. I've taken that value and subtracted the value 0x1000 (Replicates column-level collation.) which I think is causing the problem - that gives me a value of 0x40D2 which I've tried to use. Though I get the same error as stated above, so actually I'm not that very further...

There is a note to the "0x1000 Replicates column-level collation" schema option saying that "This option should be set for Oracle Publishers to enable case-sensitive comparisons.". Does that mean it's a required option (and is therefore causing the problem)? Because then I think I have a serious challenge here?

Jeppe

|||

Hi JB,

You can start trouble-shooting by checking the following:

1) Is the ITEMTRANSLATION table created at the subscriber?

2) Is there anything obviously amiss with the "CREATE TABLE" statement in ITEMTRANSLATION_2.sch? (Would be great if you can post it here so we can have a look.)

-Raymond

|||

Argh... me not paying enough attention before the morning caffeine kicks in :) You should change your schema option from 0x40D2 to 0x40D3 otherwise the create table statement will not be scripted.

-Raymond

|||

Thank you Raymond!

I think that solved the main part of the problem :) I still got a problem with the table itemtranslation, but now it's a primary key violation problem, and I think it's caused by a nvarchar column in the primary key constraint. I'll have to look deeper into that, before I'll bug you with that problem too :)

Jeppe