Sp_addarticle And @ins_cmd, @del_cmd, @upd_cmd

Nov 1, 2004

I had been asked to add a new article to an established transactional replication. I modify and ran the sample provided from microsoft.

<code>
EXEC sp_addarticle
@publication = N'ACTION',
@article = N'ACTION',
@source_owner = N'dbo',
@source_object = N'ACTION',
@destination_table = N'ACTION',
@type = N'logbased',
@creation_script = null,
@description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F3,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_ACTION',
@del_cmd = N'CALL sp_MSdel_ACTION',
@upd_cmd = N'MCALL sp_MSupd_ACTION',
@filter = null,
@sync_object = null,
@auto_identity_range = N'false'
</code>

I was under the impression that once I ran the script, the insert, update and delete command will be generated by SQL Server for me. However, that's not the case. Am I missing anything? How do I add the new article so that the insert, update and delete stored procedure will be autogenerated?

Any help will be appreicated.

View 2 Replies


ADVERTISEMENT

Sp_addarticle, Proc Has Bug With No Know Fix

Jan 14, 2004

I have found a bug with the sp_addarticle procedure and have not had any success finding a fix for it. The bug only exists whenever I am adding an article that is vertically partitioned and uses a DTS package to do the processing. In this particular case, I am using snapshot repl. Has anyone experienced this issue before?

Here is the statement...

exec sp_addarticle @publication = N'PublicationName', @article = N'ArticleName', @source_owner = N'dbo', @source_object = N'ArticleName', @destination_table = N'ArticleName', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 16, @vertical_partition = N'true', @ins_cmd = N'CALL sp_MSins_ArticleName', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'

Here is the error that gets produced...
Server: Msg 156, Level 15, State 1, Procedure syncobj_0x4344414345344530, Line 1
Incorrect syntax near the keyword 'from'.


Any help would be greatly appreciated!

View 1 Replies View Related

Sp_addarticle: What's The Difference Between @source_object And @sync_object?

Nov 27, 2005

BOL says:

@source_object - Is the table or stored procedure to be published.
@sync_object - Is the name of the table or view used for producing the data file used to represent the snapshot for this article.

I get how transactional replication is tied to changes in a single table or indexed view (the "source_object"). But what is the role of the sync_object? It appears as if the sync_object can be any simple (non-indexed) view. Hilary Cotter uses it to replicate a calculated field (the number of titles associated with each author) in "Case 4" in this article: http://www.dbazine.com/sql/sql-articles/cotter1.

Does the replication pull data from the sync_object whenever it replicates data, even though BOL says that it is used only for the snapshot data? Am I lacking some fundamental understanding of what the snapshot data is? Isn't it a snapshot of all the data at a particular point in time? If the snapshot contains a calculated field, don't the subsequent replicated transactions pulled from the transaction logs also have to be able to calculate that field?

Thanks,

-=michael=-

View 4 Replies View Related

Bug In Management Studio : Scripting Of Publication Generates Wrong Value For @schema_option In Sp_addarticle Statements

Jan 17, 2007

Hi everybody,

I found the following bug in Management Studio:
I add an article with sp_addarticle and a value of 0x00 for @schema_option or via the wizard with all article properties set to false.
Then I script the publication to a file. The result contains a sp_addarticle statement with @schema_option = 0x0000000000004071, which must be wrong.
To prove that I recreated the article with the generated script and checked the article properties. As I feared 4 of the properties ('Copy clustered index', 'Copy nonclustered index', 'Copy unique key constaints' and 'Convert data types') are set to true!

Is this a known bug ?

wolfgang Kunk

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved