Replication: If sql_variant column includes a value with new datetime type, the value is replicated as base type nvarchar even if schema option 0x200000000 is not set

Problem: If sql_variant column includes a value with new datetime type, the value is replicated as base type nvarchar even if schema option 0x200000000 is not set during the application of the SNAPSHOT (only). After the snapshot has been applied, if you execute the command to verify the property of the sql_variant column: SELECT SQL_VARIANT_PROPERTY(column_name,’BaseType’),…

0

Replication: What is the difference between Database Compatibility Mode and Publication Compatibility Mode

Database Compatibility Mode: Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around versiondifferences in the behaviors that are controlled by the relevant compatibility-level setting.            In this…

0

Replication: Error "You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels" generated when altering published table columns

Problem:   Error “You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels” generated when altering published table columns in a transactional replication. Cause:   Unable to alter columns on Published database if using any isolation level besides the READ COMMITTED or REPEATABLE READ isolation level. READPAST hint is not…

0

Note to all Readers

Seems like there was an issue with the comments for this blog – I wasn’t getting notified and hence never got to respond. My apologies to all those that commented and never got a reply. If you do have a comment, please do not hesitate to e-mail me by selecting “e-mail Blog Author” Thanks!  

0

REPLICATION: My distribution agent generates a dump when replicating with my Oracle Subscriber

PROBLEM: I have a SQL Server 2005 replicating with an Oracle 11 subscriber. After installing the OLEDB driver for Oracle 11, a minidump is created every time the distributor tries to connect to Oracle: Distrib.exe crashes with STACK_OVERFLOW exception.  The issue occurs while initializing the subscriber therefore as soon as it tries to connect to…

0

Replication: Issue with setting up Transactional Replication with Updatable Subscribers and NFR Triggers

Problem: I have set up a Transactional Replication with Updatable Subscribers on my SQL 2005 server and created user triggers on the Publisher database. These triggers have been configured with “not for replication”. I update my subscriber and the changes are propagated onto the publisher. I then update my publisher and these changes are not sent…

0

Replication: Transactional Replication Over WAN

A lot of users have wondered about the performance latency transactional replication suffers over WAN. After analyzing / discussing this within the teams, here are the main points one should take into consideration when deciding / testing this type of implementation: Should you notice performance degradation when testing your transactional replication over WAN, please verify…

1