102 and incorrect syntax near sp_MS errors when you create peer-to-peer replication in SQL Server

This article helps you resolve the problem that occurs when you create a peer-to-peer publication from SQL scripts in Microsoft SQL Server.

Original product version:   SQL Server 2017, SQL Server 2016
Original KB number:   4465517

Symptoms

When you create a peer-to-peer publication from SQL scripts in Microsoft SQL Server, the Distribution Agent in a replication fails and generates incorrect syntax near error messages.

These error messages appear in the SQL Server Agent job history, in a SQL Profiler trace, or in an Agent logging file that's generated by using the -Output parameter. For example, the following error messages are generated by running an INSERT stored procedure for the Production.product table for the AdventureWorks2017 sample database:

SQL Server Agent Error message

Error messages:
Incorrect syntax near 'sp_MSins_ProductionProduct'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102

SQL Profiler Trace

Profiler Trace failing statement with missing "." between schema and procedure name
if object_id(N'[dbo][sp_MSins_ProductionProduct]', 'P') > 0
drop proc [dbo][sp_MSins_ProductionProduct]

Agent Log error message

Time stamp Connecting to Subscriber
Time stamp Initializing
Time stamp Agent message code 102. Incorrect syntax near ''sp_MSins_ProductionProduct''.
Time stamp Category:COMMAND
Source: Failed Command
Number:
Message: if @@trancount > 0 rollback tran
Time stamp Category:NULL
Source: Microsoft SQL Server Native Client 11.0
Number: 102
Message: Incorrect syntax near ''sp_MSins_ProductionProduct''.

Cause

This problem occurs when you add articles to an existing peer-to-peer publication by using Microsoft SQL Server Management Studio 17.x.x. When you do this, the replication generates a stored procedure name that's prefixed by using the [dbo] schema. You can see this by scripting out the publication in the sp_addarticle command, as shown the following example.

 exec sp_addarticle @publication = N'Products', @article = N'Product',
 @source_owner = N'Production', @source_object = N'Product',
 ...
 @ins_cmd = N'CALL [dbo].[sp_MSins_ProductionProduct]',
 @del_cmd = N'CALL [dbo].[sp_MSdel_ProductionProduct]',
 @upd_cmd = N'SCALL [dbo].[sp_MSupd_ProductionProduct]' 
 GO

Running a replication script to rebuild the peer-to-peer environment, the sp_addarticle logic parses out a period (".") that causes an invalid CALL syntax, such as CALL [dbo][sp_MSins_ProductionProduct].

Resolution

To fix this problem, follow these steps:

1. Script out all peer-to-peer publications, as shown in the Cause section.
2. To remove the dbo schema prefix from the stored procedure calls, globally find the ALL [dbo].[ text. Then, replace this with ALL [.

The tables and data determine which option you should choose. If the broken tables represent a large part of the database, and the data includes changes, we recommend that you try to rebuild the peer-to-peer environment through a fresh backup-restore process, and that you use the peer-to-peer wizard to re-create the topology.

Corrected syntax

@ins_cmd = N'CALL [sp_MSins_ProductionProduct]',
@del_cmd = N'CALL [sp_MSdel_ProductionProduct]',
@upd_cmd = N'SCALL [sp_MSupd_ProductionProduct]'

Prevention

When you add new articles to an existing peer-to-peer publication, open the Article Properties window, and remove the [dbo]. schema prefix before you save the changes.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in Original product version at the beginning of this article.