January 31, 2018 at 10:03 pm
I have multiple tables with the same name but different schemas. I want to script out the drop of one of these tables from replication. I can't find where or how I can declare the schema name. It only defaults to dbo if I run below
EXEC sys.sp_dropsubscription @publication = 'SGSysTestLVSREP', @article = 'DropArticleTableName', @subscriber = 'Server11', @destination_db = 'SysTest'
EXEC sys.sp_droparticle @publication = 'SGSysTestLVSREP', @article = 'DropArticleTableName', @force_invalidate_snapshot = 0
I would like to avoid using the GUI
February 1, 2018 at 7:02 am
Fiona.Preedy - Wednesday, January 31, 2018 10:03 PMI have multiple tables with the same name but different schemas. I want to script out the drop of one of these tables from replication. I can't find where or how I can declare the schema name. It only defaults to dbo if I run below
EXEC sys.sp_dropsubscription @publication = 'SGSysTestLVSREP', @article = 'DropArticleTableName', @subscriber = 'Server11', @destination_db = 'SysTest'
EXEC sys.sp_droparticle @publication = 'SGSysTestLVSREP', @article = 'DropArticleTableName', @force_invalidate_snapshot = 0
I would like to avoid using the GUI
I'm not sure what you mean by it defaulting to dbo but you don't specify the schema. When you execute sp_droparticle, it will look up the article id from the name by querying MSarticles in the publisher database. That table has the name, object id as well as schema id. So it gets what it needs from that table.
Sue
February 1, 2018 at 3:41 pm
Microsoft has confirmed that the stored procedure will only drop the dbo table. They suggest writing my own
February 1, 2018 at 4:59 pm
Fiona.Preedy - Thursday, February 1, 2018 3:41 PMMicrosoft has confirmed that the stored procedure will only drop the dbo table. They suggest writing my own
Nope. The stored procedure absolutely does drop tables in schemas other than dbo. And if you run a trace while dropping the article using Replication Monitor it also calls the exact same stored procedures. So if that were the case that it only works with tables in the dbo schema, then it would mean you can't out of the box have anything replicated that are in schemas other than dbo. Pretty sure there would be some serious problems if that was the case and it would have been addressed way before now.
I've done them before and I just did another one (several times over) and it worked just fine. Just create another schema and table :CREATE SCHEMA Test
go
CREATE TABLE Test.Blah
(ID int identity(1,1) not null Primary Key,
SomeCol varchar(2))
Add it to whatever test publication you have. Reint and sync. Verify the table is in the subscriber database. And then just execute:EXEC sp_dropsubscription
@publication = 'PublicationName',
@article = N'blah',
@subscriber = 'SubscriberServer';
GO
EXEC sp_droparticle
@publication = 'PublicationName',
@article = N'blah',
@force_invalidate_snapshot = 1;
And after it successful runs you get a message to invalidate the previous snapshot:
Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot.
The article is no longer in the publication. Then synchronize the subscription in Rep Monitor and the article is not in the subscriber. Worked just fine. Article not in the subscriber, publication properties, not anywhere. It was in a completely different schema.
So it looks like replication supports schemas other than just dbo.
Sue
February 1, 2018 at 5:17 pm
And now I think I know what you are thinking....there is a big difference between dropping an article and dropping an object no matter how you do it - tsql or the GUI.
Drop the article and then query sysarticles. That is where you will see the articles for the publication. You can absolutely remove articles from publications no matter what schema.
And in terms of objects, it won't drop them in the destination database when removed from replication no matter what schema it is in. It will not drop an object in dbo either. All of this is documented in the following:
Add Articles to and Drop Articles from Existing Publications
Sue
February 1, 2018 at 5:29 pm
If I have 3 tables
dbo.blah
sg.blah
api.blah
How does it know what blah table I mean if I can't put @article = N'sg.blah'
February 1, 2018 at 6:05 pm
Fiona.Preedy - Thursday, February 1, 2018 5:29 PMIf I have 3 tables
dbo.blah
sg.blah
api.blah
How does it know what blah table I mean if I can't put @article = N'sg.blah'
You need to query sysarticles in the published database. Slow down and think about it for a minute. If the t-sql approach and GUI/Replication Monitor both use the same stored procedures and any article can be removed from any publication and the stored procedure just accepts the name then SQL Server must have a way to differentiate them right?
And then I earlier said that it gets the information from sysarticles then wouldn't that be what you want to check?
And what if I had never mentioned that? Then in a dev environment go ahead and do it using the GUI which running a trace or extended events session to capture how it is implemented. You can do that with a lot of things - not just replication. You can learn a lot by doing that with all sorts of things.
If they were named like your list, most likely one is blah, another is blah1, another is blah2...
Execute the following in the published database:
SELECT
dest_table as SubscriberTable,
[name] as ArticleName,
dest_owner as ArticleSchema
FROM dbo.sysarticles
Sue
February 1, 2018 at 6:30 pm
I didn't think about the numbering Thanks
I guess the Microsoft tech didn't understand my question
February 1, 2018 at 7:02 pm
Fiona.Preedy - Thursday, February 1, 2018 6:30 PMI didn't think about the numbering Thanks
I guess the Microsoft tech didn't understand my question
Go get your money back if you paid.
Sue
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply