Skip to Content

text to varchar(max) conversion

Hello,

When replicating between ASE and MS SQLServer, is it possible to replicate a text column into a varchar(max) column and vice versa ?

I know it's not possible using the standard function strings, so if it's possible, are the function classes/strings available somewhere ?

Thanks,

Luc.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 27, 2014 at 12:56 PM

    You haven't mentioned your ASE or RS versions so not sure how much of the following may apply in your situation ...

    Repserver has supported replication of varchar(max) for quite a few versions now (going back to 15.1 or 15.2).

    The documentation (RS Admin guide) states varchar(max) replication is only viable when going from MSSQL to MSSQL. However, the Admin guide (and sybase.com solved case 11670083) also states that within the repserver you define the MSSQL varchar(max) as the RS datatype text.

    sybase.com solved case (11577904) seems to imply that varchar(max)<-->text may be doable, but the verbiage is limited and confusing.


    I'd suggest trying to map varchar(max) to text in the table repdef and see what happens. Another option would be to check out the translation definition(s) and associated function strings for the varchar(max) datatype to see if you might be able to build some custom translations and/or function strings.

    -----------

    If the simple varchar(max) <--> text mapping in a table repdef doesn't work then my guess is that you can't replicate between MSSQL/varchar(max) and ASE/text if simply because they aren't stored and processed the same way by repserver. [NOTE: I have no idea how varchar(max) is stored in MSSQL or processed by repserver.]

    Review of the related translations and function strings may provide some ideas to get around this limitation ... but I wouldn't hold my breath (ie, I'd think Sybase would've provided this functionality when they introduced support for varchar(max)).

    -----------

    While not 'pretty', have you looked at replicating into a MSSQL/text column with triggers enabled? Idea being to have the trigger convert the text to varchar(max)? (Does MSSQL provide the ability to convert between varchar(max) and text?).

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Mark,

      Sorry, I didn't mention versions because most recent versions will do.

      I tested with repserver 15.7.1 SP102, ase 15.7 #4 (I don't think a higher version would make a difference).

      I don't know if mapping text to varchar(max) would help (if it would be allowed, it isn't because te types are not compatible). I already have replication definitions for MSA and previous versions had problems if you have multiple replication definitions.

      Conversion between text and varchar(max) is implicit in ms sql.

      It should be doable with function strings, but I have no experience with creating or modifying them.

      The error message is "Argument data type varchar(max) is invalid for argument 1 of textptr functi."

      I believe that trying to convert text to varchar between ASE servers would result in the same error.

      Best Regards,

      Luc.