Reserve word column name stopping replication 15.6 ESD2

[Former Member]

I upgraded from 15.6 to 15.6 ESD2 and moved to a new machine (new rep config also). I have a table that contains a reserved word [default] as column name and it is stopping my DSI to the warm standby server (no repdefs). Do I just need to alter the conection to turn on quoted identifiers? Sybase is telling me I have to create a repdef, but I never had one before and this table has been in the DB since 2010.

create table test (

[default]   char(1) NULL,

col2         char(10) NULL,

.

.

.)

Rep Server now stops DSI and after dumping transaction we see:

delete from dbo.ZipSp_new where zip='37882' and
city='TOWNS' and county='BOUT' and st='TN' and uspscty='TOWNS' and
cntyfips='409' and cntydef=' ' and default=' ' and gecode='4700970' and
inout='B'

Rep server kills DSI with syntax error.

Message from server: Message: 156, State 2, Severity 15 -- 'Incorrect syntax near the keyword 'default'.

[former_member182259 · Accepted solution]

You can try altering the connection and setting dsi_quoted_identifier to 'always' ....not sure if 'always' worked in 15.6 though....you may have to try 'on' instead, and then use repdef with quoted keyword ala:

create replication definition ....

....

(

     default     varchar(1)     quoted,

     col2          char(10),

...

)

....

Don't try:

create replication definition ....

....

(

     [default]     varchar(1),

     col2          char(10),

...

)

....

(or "default") as that just tells RS you are using an RS keyword as an identifier.     I think, the way it works is that if you do dsi_quoted_identifier always, it will always quote all identifiers from RS.  If you set dsi_quoted_identifier to on, it is like saying 'set quoted_identifier on' - in that RDB will expect quoted identifiers if they are sent but RS will not send them by default as it does with 'always'.....hence to know which ones to send as quoted identifies, you need to add the quoted keyword to the repdef.   You would only need to create repdefs for the tables with quoted identifiers.  Keep in mind that since you said you were not using repdefs in the past, this likely is a WS or MSA setup, and the repdef you create should have the "send standby all columns" clause in order for the standby connection to know to use the repdef (I prefer all columns over replication definition columns as it allows schema changes).

[Former Member]

I don't think create repdef or set dsi_quoted_identifier will fix this problem.

Event it works, still so call "work around".

I think the best way is to avoid using reserved words.

Suggest to alter the column to another name.

for example:

sp_rename 'test.default', 'col1'

go

then you can resume you connection without error.

[former_member182259]

It will work....I am not sure WHY you think it won't.  First of all, the issue actually is that you are using a reserved word for the column in ASE.   In order to even get this to work in the primary application, you have to do two things:

1) issue set quoted_identifier on for your session in ASE (primary)

2) use quoted column delimiters (either [colname] or "colname")

SRS needs to do the same - so you need to tell it to issue the set quoted_identifier on (thats what dsi_quoted_identifier does) and you need to annotate which columns (which is what the 'quoted' repdef annotation does).