SQL Server Transactional Replication . Artice not created by snapshot
-
Good Day,
We are running SQL Server 2008r2 using Transactional Replication. We selected a numbe of tables to replicate . One of them however is not created by the Snapshot although it is one of the selected articles. The object owner was not changed and I confirmed that it was selected, but when I tried to select it on the subscriber , the article does not exist. I also confirmed other tables were populated . What else should I be looking for ?
-
This should not happen ideally. Try going over once again and restart. Hopefully it should be created!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
try adding article using below query.
EXEC sp_addarticle @publication = 'PublicationName', @article = 'articleName', @source_object = 'articleName', @source_owner = 'dbo', @Type = 'view schema only'
GO
EXEC sp_refreshsubscriptions 'PublicationName'
GO
Normally on a huge database it is not a good idea to generate the snapshot everytime you an article to it, why not try intilize subscription with backup option.
-
Bhaskar.Shetty (6/10/2013)
Normally on a huge database it is not a good idea to generate the snapshot everytime you an article to it, why not try intilize subscription with backup option.+1
Normally, I initialize subscription using backup option. And use the below script to add or remove articles from replication.
--==========================================================-- Add/remove an article from replication
--==========================================================
DECLARE @vAction VARCHAR(5) = 'ADD'; -- ADD=> to add an article, DROP=> to drop an article
DECLARE @vArticle VARCHAR(200) = 'tblarticle'; -- name of the article to be added/dropped
DECLARE @vPublication VARCHAR(200) = 'PublicatoinName'; -- name of the publication
DECLARE @query VARCHAR(MAX);
IF @vAction = 'ADD'
-- Add a new article for replication
BEGIN
IF EXISTS (SELECT 1 FROM sysarticles WHERE name = @vArticle)
PRINT 'WARNING: Article= ' + @vArticle + ' - already marked for replication';
ELSE
BEGIN
EXEC Sp_addarticle @publication = @vPublication, @article = @vArticle, @source_object = @vArticle;
EXEC sp_refreshsubscriptions @vPublication;
END
END
ELSE
-- Remove an article for replication
BEGIN
IF NOT EXISTS (SELECT 1 FROM sysarticles WHERE name = @vArticle)
PRINT 'WARNING: Article= ' + @vArticle + ' - not marked for replication';
ELSE
BEGIN
SELECT @query = (SELECT 'EXEC sp_dropsubscription '''+@vPublication+''','''+@vArticle+''','''+sub.srvname+''','''+dest_db+''''+CHAR(10)
FROM syssubscriptions sub
JOIN sysarticles art
ON sub.artid = art.artid
WHERE name = @vArticle
AND dest_db <> 'virtual'
FOR XML PATH (''))
EXEC (@query);
--
EXEC sp_droparticle @publication = @vPublication, @article = @vArticle;
EXEC sp_refreshsubscriptions @vPublication;
END
END
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
IF EXISTS (SELECT 1 FROM sysarticles WHERE name = @vArticle)
PRINT 'WARNING: Article= ' + @vArticle + ' - already marked for replication';
ELSE
BEGIN
EXEC Sp_addarticle @publication = @vPublication, @article = @vArticle, @source_object = @vArticle;
EXEC sp_refreshsubscriptions @vPublication;
END
Sql Server anyways fires a error when you try to add a existing marked articles again to same publication, but no harm in being pre-sure before adding it. 🙂
-
🙂
Yes correct! I developed this script for my development team, so that they can add/remove articles. And should get some custom error message instead of SQL error message.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Good Day.
Thank you for the response. The article already exists in the Publisher.
-
Thanks, everyone, the Systems Administrator added the necessary permissions to the folder and thereafter it worked
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply