MS SQL Replication - multiple Schemas with same tables names

[KirstyHunter]

Hi,

I am trying to set up transactional replication on a MS SQL 2008 database that has two Schemas, both schemas have some tables with the same names.

Schema A
A.Customer
A.Product
A.Invoice

Schema B
B.Customer
B.Product
B.Incoive

My first problem is actually configuring Replication on the publisher using the wizard. It fails when selecting all tables from both Schemas, even when I set it to 'Copy-file group associations'.
I get the following error for every table in Schema B that has the same name as a table in Schema A:

1. SQL Server Management Studio could not create article 'Customer' Cannot create if this object represents an existing object in the server

Does anyone know why it seems to ignore the fact that the tables with the same names are in different schemas?

I have 200+ tables in both schemas and dont want to have to select each table individualy from Schema A, then Select each table from Schema B to create two publications, everytime I want to set up replication. There doesn't appear to be a way to select all tables from a specific Schema.

Any help appreciated
Thanks in advance
Kirst

[BitBlt]

This reply won't directly answer your question, but is intended as a possible workaround to at least get you going.

First, you need to set up replication with one table (or a couple) using the Wizard and capture the generated SQL statements.

Once you have captured that set of SQL statements, use them as a template to create a query to generate statements for each table in your database. You can do this using a combination of literals and system tables. We used this technique to generate an entire group of working changed-data capture work tables and stored procs (about 3 dozen of them...saved tons of time).

Anyway, using this technique will enable you to replicate the necessary SQL so you can manually create your replication schema. Bad news: you won't be able to manage it using the wizard anymore.

If you need a more concrete example, let me know and I can send you a sample script.

[KirstyHunter]

I decided to create two separate publications one for Schema A and one for Schema B. I chose to capture the script file to create the publication in the wizard for each one so I can now run the scripts every time I want to set up replication.

It isn't a nice way of doing it especially as I have around 500 tables, the wizard lists them by table name not schema so it is time consuming and easy to miss checking/unchecking a table. However it appears to be working.

Thanks for the help