Peer 2 Peer Replication fails with Incorrect syntax near ‘sp_MS…’ (Source: MSSQLServer, Error number: 102)

Chris Skorlinski [MSFT] Troubleshooting December 6, 2018

Share this Post

Symptom

Creating a Peer 2 Peer publication from SQL scripts and you observe the Distribution Agents failing with “incorrect syntax near”. These messages can be observed in SQL Server Agent job history, in SQL Profiler Trace, or in Agent -Output logging. Errors show below were raised on (insert) stored procedure for Production.Product table for AdventureWorks2017 sample database. You may also see errors for other Replication generated stored procedure such as sp_MSdel_{article} and sp_MDupd_{article}.

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

-- Profiler Trace failing statement with missing "."
if object_id(N'[dbo][sp_MSins_ProductionProduct]', 'P') GT 0
drop proc [dbo][sp_MSins_ProductionProduct]

-- Agent Log error message
2018-08-31 12:49:09.478 Connecting to Subscriber
2018-08-31 12:49:09.493 Initializing
2018-08-31 12:49:09.509 Agent message code 102. Incorrect syntax near ''sp_MSins_ProductionProduct''.
2018-08-31 12:49:09.618 Category:COMMAND
Source: Failed Command
Number:
Message: if @@trancount GT 0 rollback tran
2018-08-31 12:49:09.618 Category:NULL
Source: Microsoft SQL Server Native Client 11.0
Number: 102
Message: Incorrect syntax near ''sp_MSins_ProductionProduct''.

Cause

When adding articles to an existing P2P publication via SQL Server Management Studio 17.x.x, the replication generated stored procedure name is prefixed with “[dbo]” schema. You can see this when scripting out the publication in the sp_addarticle command as shown below.

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

Currently, if you execute a replication script to rebuild the P2P environment, the sp_addarticle logic parses out “.” resulting in an invalid CALL syntax such as “CALL [dbo][sp_MSins_ProductionProduct]”.

Resolution

After scripting out any Peer 2 Peer publication, globally search-n-replace “ALL [dbo].[” with “ALL [” removing the dbo scheme prefix from the stored procedure calls.

If you’re in the processes of setting up a new P2P environment, fastest solution might be to drop the publication, modify the script, then recreate the P2P environment. If only 1 or two tables are failing, consider dropping those articles from publication, editing the script, then adding back to the publication. Which option depends on tables and data. If the broken tables represent large portion of the database, and the data has changes, perhaps rebuilding P2P through fresh Backup\Restore along with P2P Wizard to recreate the topology is be best solution.

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 adding new articles to an existing Peer 2 Peer publication, edit the Article Properties and remove “[dbo].” schema prefix before saving changes.

We’re still exploring options to see how we might prevent this problem.

Chris Skorlinski
Microsoft SQL Server Escalation Services