Author: Chris Skorlinski [MSFT]

Distribution Agent fails with ‘Cannot update identity column ‘OrderID’.’

Chris SkorlinskiMicrosoft SQL Escalation Services I wanted to share this problem which started down a “your data is wrong” path, but ended up with “your customer scripts to create Transactional Replication need to be tweaked”. Customer reported the following error in Replication Monitor: Cannot update identity column ‘OrderID’. Looks like Distribution Agent is trying to
Read More »

Script to decrypt the @schema_option for a Transactional Replication Article

The code below helps decrypt @schema_option settings for Transaction Replication articles. Note the meaning of binary offset may change in future builds of SQL Server. Always check Microsoft docs for most accurate listing. You’ll find scheme_options listing documented at sp_addarticle. Code for this example now posted on https://github.com/ReplTalk/ReplScripts/blob/master/schema_option.sql Example: ---------------------------- --Set the schema_option value that
Read More »

ReplTip – Technique to exceed column limits for Merge Replication

Chris SkorlinskiMicrosoft SQL Server Escalation Services While working with a customer on a Merge Replication project, we encounter a potential road block. They had a published table which exceeded the number of columns allowed for Merge Replication. From this article https://technet.microsoft.com/en-us/library/ms151749(v=sql.105).aspx: If your application does not require column-level tracking, it is recommended that you use
Read More »

CDC functionality may break after upgrading to the latest CU for SQL Server 2012, 2014 and 2016

This problem was brought to our attention recently by one of our customers. CDC LogScan job fails, possible Error Message: Log Scan process failed in processing log records. Operand type clash: int is incompatible with uniqueidentifier Operand type clash: int is incompatible with datetime The Log-Scan Process failed to construct a replicated command from log
Read More »

Transaction Log won’t truncate when using Snapshot Replication to Azure SQLDB

Chris SkorlinskiMicrosoft SQL Server Escalation Services Customer encountered transaction log growth for a Published SQL Server 2016 database while nightly refreshing Azure SQLDB. We discovered the default publication setting “replicate_dll” was still enabled. SELECT [description],[name],[replicate_ddl] FROM <your published database>.[dbo].[syspublications] description name replicate_ddl —————————————————— ———- ————- Snapshot publication of database ‘AdventureWorks2014’ Customers 1 (1 row(s) affected)
Read More »