April 19, 2005 at 12:18 pm
SQL Server 2000
Two issues with Replication
Issue 1 & 2 are on different servers
Issue 1:
When you run SP_HelpArticle procedure, the value for column 'status' is the one that is set while adding the article, which is SP_addarticle.....@Status = ....
However the values that can be set for status are 0, 8, 16 & 24. Incidentally this is the value I set while adding the article, but the SP_Helparticle returns '17'. How can this happen? or what does this mean? When I try to set '17' as a value, I get an error for wrong value passed.
Issue 2:
I used sp_addarticle so many times before. Today, when I ran this proc, I observed the following
1. SP_EnumFullSubscribers returned null
2. Subscription property said only some of the articles are subscribed, I was allowed to drop the articles and the property changed to 'all'.
3. Finally I had to use the Enterprise Manager to add the articles and the replication is fine.
No idea why it happened.
Any help on these issues will be a great help. Please pass any information you know.
More investigation::
On Issue 1: I run the following or using Enterprise manager wizard
exec sp_addarticle @publication = N'ReplDB', @article = N'ReplTable', @source_owner = N'dbo',
@source_object = N'ReplTable', @destination_table = N'ReplTable', @type = N'logbased',
@creation_script = null, @description = null, @pre_creation_cmd = N'none',
@schema_option = 0x00000000000000F3, @status = 16,
@vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_ReplTable',
@del_cmd = N'CALL sp_MSdel_ReplTable', @upd_cmd = N'CALL sp_MSupd_ReplTable',
@filter = null, @sync_object = null, @auto_identity_range = N'false'
go
Then if I run the following
Exec SP_HelpArticle @Publication = 'ReplDB'
Go
I get a result set with the article details. In which the Status is displayed as 17
I checked with the various values that can be set, such as 0, 8, 16, 24. every time I query the values return as the value I set + 1.
For example
When I set @status = 0, the Return 'Status' value is 1
When I set @status = 8, the Return 'Status' value is 9
When I set @status = 16, the Return 'Status' value is 17
When I set @status = 24, the Return 'Status' value is 25.
I guess this one is killing me not knowing why this happens.
As to Issue 2: still no progress. using 'SP_AddArticle', does not add the article completely. But using the Ent Man does.
Thanks in advance
April 22, 2005 at 8:00 am
This was removed by the editor as SPAM
April 22, 2005 at 2:30 pm
Ofcourse, Any thoughts, any ideas....
April 25, 2005 at 2:44 pm
I am officially stopping to followup with this topic. Any ideas are welcome. I will check this once in a while.
Thanks in advance
July 11, 2011 at 6:44 am
the status values are additive. So 17 means the article is active and uses parameters.
March 29, 2012 at 3:02 pm
I know this is an old post but I also ran into the questions while working with replication and figured I would post my findings in order to help anyone else who comes across this post.
When setting the @status the values of 1,8, 16,24 are correct values to use. The reason why sp_helparticle returns value + 1 is because as another poster said its cumulative. When you see it plus 1 this is an indication that the article is active. If the article was not active then you would see the value of what you initially put in (8,16 or 24). The only time I have seen an article not active is when I have used sp_addarticle which brings me to your next question. You are correct in the behavior, when you add an article through the GUI it will create it and activate it. When you use sp_addarticle it does not activate it. I had to run profiler when adding through the GUI to figure out what i was missing. After adding an article using sp_addarticle you also need to run sp_refreshsubscriptions to activate it.
I ran across this link that really helped me understand.
Hope this is helpful!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply