Transactional Replication Error: Incorrect Syntax near '<<column_name>>'

• darkhelmutis

  SSCommitted

  Points: 1561

  More actions

  June 22, 2016 at 12:33 pm

  #400310

  Try to keep this simple, but it is SQL Server and it is replication so that might not be possible.

  I have a transactional replication topology setup with a single publication on SQL Server 2008 R2 to a single subscriber on SQL Server 2008 R2 with an external distributor on SQL Server 2014. The publication is supposed to only replicate clustered indexes and ignore everything else. Work on a published table that produced the vague error 102, 'Incorrect syntax near <<column_name>>'.

  There was more to the work, but the specific commands that produced the error dropped two foreign keys then re-added them after other work occurred. These foreign keys reference a table with a unique non-clustered covering index that enforces the two foreign key references. This unique non-clustered covering index also has 37 included columns which I think might be the cause of the issue, but I am not sure. Finally all of this work was executed in the READ COMMITTED transaction isolation level and wrapped in an explicit transaction with rollback handling if necessary.

  Using the xact_seqno from the error message, I was able to retrieve the problematic command (see below). Since the unique non-clustered covering index enforces the foreign keys, it looks like replication is attempting to verify the 37 included columns exist on the subscriber and they try to create the foreign key constraint and referencing all 37 columns in the constraint definition. The only problem is that it appears as if the command is cut off at the 33rd column, thus leaving an incomplete command which in turn causes the 'incorrect syntax near <<column_name>>' error message. I was able to capture this and verify it via SQL Server Profiler as well. The command is truncated at the exact same spot. Comparing this 'truncated' command to other successful commands that occurred before, it looks like it should have completed the included column list and added 'NOT FOR REPLICATION' to it.

  Luckily, all of the work done was for actions that would not replicate (dropping constraints and non-clustered indexes, altering a trigger, altering a non-clustered index, re-creating everything), so I was able to skip the transaction with the stored proc sp_setsubscriptionxactseqno and restore replication without reinitializing.

  I think I have identified the problem, but I am not sure why it happened. I thought that replication would handle long t-sql commands and successfully break them up in the MSRepl_Commands system table; there is frequent evidence of this in the table with the combination of xact_seqno and command_id. Is there an upward limit of how large a replicated command can be once split up like this? Any other ideas?

  Thanks!

  IF EXISTS ( SELECT

  si.indid

  FROM

  sys.syscolumns sc

  JOIN sys.sysindexes si

  ONsc.id = si.id

  JOIN sys.sysindexkeys sik

  ONsik.id = sc.id

  AND sik.indid = si.indid

  AND sik.colid = sc.colid

  WHERE

  sc.id = OBJECT_ID(N'<<table_name>>')

  AND sc.name IN ( <<enumerated list of 37 included columns>> )

  AND si.status & 2 = 2

  GROUP BY

  si.indid

  HAVING

  COUNT(*) = 37 )

  ALTER TABLE <<table_name>> ADD CONSTRAINT <<constraint_name>> FOREIGN KEY (<<foreign_key_column_name>>) REFERENCES <<foreign_table_name>>(<<list that is cutoff referencing 33 of the 37 included column names>>