SQL Server Transaction Replication Snapshot Error SQL 2016

Question

When creating new Publication the snapshot agent returns following error:
"Exception Message: The Distributor 'SERVER-NAME' does not recognize 'PUBLICATION-NAME' as a valid publication."
I've tried multiple solutions I've found on internet, but none worked for us.

I've run SQL Profiler and identified following command was running when it raises the error:
exec sp_MShelp_snapshot_agentid @publisher_id=0,@publisher_db=N'DB-NAME',@publication=N'PUBLICATION-NAME',@Job _id=0x32DE12AEFE3CEC4EB4D1962FE389F816

Then I've checked the sp_MShelp_snapshot_agentid procedure and it gets @publisher_id from MSsnapshot_agents table, but it returns Publisher_id = 1
If I manually update the publisher_id on MSsnapshot_agents to 0 it works!

Does someone knows how to fix that?
How to make sp_MShelp_snapshot_agentid get the correct Publisher_Id?

Some infos:
Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461)
Distributor, Publisher, Subscriber are in same machine.

Thanks a lot!
Ademir

Answer 1

Hi 97432981,
Welcome to Microsoft Q&A.

exec sp_MShelp_snapshot_agentid @publisher_id=0,@publisher_db=N'DB-NAME',@publication=N'PUBLICATION-NAME',@Job _id=0x32DE12AEFE3CEC4EB4D1962FE389F816
Then I've checked the sp_MShelp_snapshot_agentid procedure and it gets @publisher_id from MSsnapshot_agents table, but it returns Publisher_id = 1

Do you mean to find that Publisher_id is 1 in MSsnapshot_agents table, but Publisher_id is 0 when executing sp_MShelp_snapshot_agentid, which causes the transactional replication to fail?
I did a test in my environment, and the transactional replication works well.
Could you please try to use a remote distribution database to see if the distribution database you using is corrupted?
Please refer to https://learn.microsoft.com/en-us/sql/relational-databases/replication/configure-publishing-and-distribution?view=sql-server-ver15 to use a remote distribution database.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.