MSSQL error when creating publication

Question

I'm using one MSSQL server (SQL server 2019 on Linux, to be precise, on Kubernetes) as a distributor and publisher. I tried to configure it as a transactional publication using SSMS. The problem that I'm facing is when I try to configure a new publication. I'm getting an error (as shown in the screenshot).

Here are the errors:

Messages

SQL Server Management Studio could not create article 'Country'. (New Publication Wizard)



------------------------------

ADDITIONAL INFORMATION:



Cannot create if this object represents an existing object in the server. (Microsoft.SqlServer.Rmo)

 

SQL Server Management Studio could not create article 'Currency'. (New Publication Wizard)



------------------------------

ADDITIONAL INFORMATION:



Cannot create if this object represents an existing object in the server. (Microsoft.SqlServer.Rmo)

 

SQL Server Management Studio could not create article 'Product'. (New Publication Wizard)



------------------------------

ADDITIONAL INFORMATION:



Cannot create if this object represents an existing object in the server. (Microsoft.SqlServer.Rmo)

 

SQL Server Management Studio could not create article 'Segment'. (New Publication Wizard)



------------------------------

ADDITIONAL INFORMATION:



Cannot create if this object represents an existing object in the server. (Microsoft.SqlServer.Rmo)

I have only managed to make it work by disabling the tables with names B.Country, B.Currency, B.Product, and B.Segment and only keeping the primary ones A.Country, A.Currency, A.Product, and A.Segment. But I would like to have both A.x and B.x tables.

Do you have any advice on how I can configure it to work with all tables?

I would appreciate your help!

Answer 1

Hi @iRakic ,

In SQL Server Replication, an article can only contain tables from one database, i.e. it cannot be replicated across databases. If you need to replicate data between different databases, you need to create the corresponding publication and subscription in each database.

Best regards,

Seeya

---

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".

Answer 2

I don't use Replication myself, but this situation seemed awkward to me, so I brought it up with some MVP colleagues. I got this reply from Greg Low:

In the wizard, there are ways to override names, etc. but your feeling that you should just skip the wizard and create a script is spot on, for no other reason than the wizard makes so many assumptions on your behalf, and often ones I wouldn't choose. Use the wizard to script a simple example, then edit the script. Later, they'll wish they had a script anyway.