Blank values Replicating to Oracle

Replicating from SQL Server 2005 to Oracle 9i Release 2. The source table has NOT NULL specified for all columns. The table contains multiple rows with blank values in one of the NOT NULL columns. When I attempt to replicate this table to Oracle, I'm unable to replicate the rows containing the blank values.

I've followed the directions in the Oracle Subscribers page in Books Online: http://msdn2.microsoft.com/en-us/library/ms151738.aspx

Modify the generated create table script, removing the NOT NULL attribute from any character columns that may have associated empty strings, and supply the modified script as a custom create script for the article using the @.creation_script parameter of sp_addarticle

Here is the segment of the Publication Script referencing sp_addarticle:

exec sp_addarticle
@.publication = N'mike_hayes_test_replication2',
@.article = N'mike_hayes_test_replication',
@.source_owner = N'bmssa',
@.source_object = N'mike_hayes_test_replication',
@.type = N'logbased',
@.description = N'',
@.creation_script = 'CREATE TABLE [bmssa].[mike_hayes_test_replication2]( [EXCELCOLUMN] [int] NULL, [TABLENAME] [varchar](40) NULL, [FIELDNUM] [int] NULL , [REFTABLEID] [int] NULL , [MODULETYPE] [int] NULL , [DATAAREAID] [varchar](3) NULL, [RECVERSION] [int] NULL, [RECID] [int] NULL, [BRAND] [varchar](20) NULL )',
@.pre_creation_cmd = N'drop',
@.schema_option = 0x00,
@.identityrangemanagementoption = N'manual',
@.destination_table = N'mike_hayes_test_replication2',
@.destination_owner = N'bmssa',
@.vertical_partition = N'false'
GO

I added the @.creation_script value, and changed the schema_option to 0x00.

The problem is that when replication occurs, the table that is created has NOT NULL specified for all columns. I expected that the @.creation_script would be executed, (as per the instructions), but that doesn't appear to be the case.

Any ideas?

Thanks for your help.

Mike Hayes

Hi Mike, you should specify the path to the file containing the create table statement for the @.creation_script parameter instead of just the "create table" statement.

Hope that helps,

-Raymond

|||Thanks for the reply. So this should be an Oracle script? Should I refer to it on the server (i.e. c:\scripts, where c:\ refers to the SQL Server drive)?

Thanks,

Mike Hayes|||

The syntax can arguably be anything compatible with Oracle although the safer thing to do is to just modify what the snapshot agent would have generated otherwise. The path should be something that is accessible(and\or relative) to the snapshot agent process since your file will then be copied by the snapshot agent to the snapshot folder. I am actually a bit surprised that the snapshot agent didn't die a horrible death trying to copy the "weird path" so something may be amiss here.

Come to think of it, would it be easier to just modify the script generated by the snapshot agent?

-Raymond