I came across a very strange problem recently while creating a publication. I had objects with the same name but in different schemas. For example, "dbo.Invoices", "Billing.Invoices", "AR.Invoices" and "AP.Invoices". In my case, the first object was a TABLE and the other 3 were VIEWS which selects data from the invoice table. I added "dbo.Invoices" to the publication and then tried to add the other 3 views. That is where the trouble started.
When I tried to add "Billing.Invoices", I got an error which said "Article 'Invoices' already exists in the publication". It looked like the UI (Property Page of Publication) does not consider the schema of the object being added.
I got the problem resolved by adding the article using TSQL instead of the UI page. I used the following code to add the new article to the publication.
exec sp_addarticle
@publication = N'MyPublicationName',
@article = N'Billing_Invoices',
--I gave a different name to the article
@source_owner = N'Billing',
@source_object = N'Invoics',
@type = N'view schema only',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x0000000008000001,
@destination_table = N'Invoices',
@destination_owner = N'Billing',
@status = 16




0 comments:
Post a Comment