Replicate Database Limitations on Microsoft SQL Server

Microsoft SQL Server supports either 28 digits or 38 digits of precision, depending on the server’s start-up options. The default precision is 28 digits.

Replication Server does not provide user-defined datatypes (UDDs) to support the default 28 digits of precision.

If you attempt to replicate numeric data to a Microsoft SQL Server database in excess of the server’s configured precision, Replication Server returns the following error:

E. 2007/12/14 11:14:58. ERROR #1028 DSI EXEC(134(1)
   dcm_gabeat70_devdb.devdb) 
   - dsiqmint.c(2888)
   Message from server: Message: 30291, State 0,
   Severity 19 --
   '[VENDORLIB] Vendor Library Error: [[Message
   Iteration=1|Data Source Name=mssql70_devdb|
   SQLState=22003|Native Error=1007|Message=
   [Microsoft] [ODBC SQL Server Driver][SQL
   Server]The number
   '9999999999999999999.9999999999999999999' is out
   of the range for numeric representation (maximum
   precision 28).
   [Message Iteration=2|SQLState=22003|Native
   Error=|Message=[Microsoft][ODBC SQL Server
   Driver][SQL Server]The number
   '0.99999999999999999999999999999999999999' is out
   of the range for numeric representation (maximum
   precision 28).] <DCA>'

Microsoft SQL Server supports identity columns in the same manner as Adaptive Server Enterprise, so the Replication Server function strings that set identity insert off and on work correctly with Microsoft SQL Server. However, to support 28-digit numeric precision, the Sybase native numeric datatype must be translated to the rs_msss_numeric datatype, and as a result of this translation, the identity characteristic is lost.

If you choose to use the numeric to rs_msss_numeric datatype translation to support 28-digit precision in a Microsoft SQL Server replicate database, the replicate table cannot declare the numeric column receiving that data as an identity.

If a replicate Microsoft SQL Server table declares a numeric column receiving translated data as an identity, Replication Server returns the following error:

E. 2007/12/14 12:05:39. ERROR #1028 DSI EXEC(134(1)
   dcm_gabeat70_devdb.devdb)
   - dsiqmint.c(2888)
   Message from server: Message: 30291, State 0,
   Severity 19 --
   '[VENDORLIB] Vendor Library Error: [[Message
   Iteration=1|Data Source Name=mssql70_devdb|SQL
   Function=INSERT|SQLState=23000|Native
   Error=544|Message=[Microsoft][ODBC SQL Server
   Driver][SQL Server]Cannot insert explicit value
   for identity column in table 'ase_alltypes' when
   IDENTITY_INSERT is set to OFF.] <DCA>'