Is there a way I can change a property for all articles in my replication at the same time?
So instead of:
EXEC sp_changemergearticle
@publication = 'MyPublication',
@article = 'MyArticle',
@property = 'published_in_tran_pub',
@value = 'true';
EXEC sp_changemergearticle
@publication = 'MyPublication',
@article = 'MyArticle1',
@property = 'published_in_tran_pub',
@value = 'true';
EXEC sp_changemergearticle
@publication = 'MyPublication',
@article = 'MyArticle2',
@property = 'published_in_tran_pub',
@value = 'true';
EXEC sp_changemergearticle
@publication = 'MyPublication',
@article = 'MyArticle3',
@property = 'published_in_tran_pub',
@value = 'true';
I want to change the property @published_in_tran_pub for all articles in one query. Something like:
EXEC sp_changemergearticle
@publication = 'MyPublication',
@article = 'ALL ARTICLES',
@property = 'published_in_tran_pub',
@value = 'true';
So I want to change the @published_in_tran_pub property for ALL articles by one single statement.
Thanks in advance
Right,
I may have this wrong (again) - but seeming as I dont know anything more than you want to call a sproc a lot - you could loop through your set of articles and pass them to your sproc;
-- I presume you can get a list of distinct Articles - you dont have to use
-- a tmp table if they already exist - use that table instead
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY Article) as Row_ID,
Article
INTO #Tmp
FROM MyArticleTable -- Dont know what this is called
WHERE publication = @publication -- only pick the articles you want to use for this sproc call
DECLARE @i int = 1
DECLARE @currentArticle varchar(max)
WHILE @i <= (SELECT COUNT(*) FROM #Tmp)
BEGIN
SET @currentArticle = (SELECT Article FROM #Tmp WHERE Row_ID = @i)
EXEC sp_changemergearticle
@publication = 'MyPublication',
@article = @currentArticle,
@property = 'published_in_tran_pub',
@value = 'true'
SET @i = @i+ 1
END