Stored procedure
(1)
Exec
(1)
Proc
(1)
Transactional
(1)
Addarticle
(1)
Replicates
(1)
Databases
(1)
Cmd
(1)

Stored Proc Execute ONly SQL 2005

Asked By Mark Price
27-Jan-10 06:54 PM
I have transactional replication running.  I have a stored procedure that
deletes a record which I would like to add to the publication via a .sql
script such that it only replicates the execution of the stored procedure.
The matching stored procedure on the subscriber has been cleared out so that
no data is deleted in the subscriber db.  I am using the following code to
add the article to the publication.

EXEC sp_addarticle @publication = 'EMOBILE_OLTP_OLAP',
@article = 'DeleteOldOrder',
@source_owner = 'dbo',
@source_object = 'DeleteOldOrder',
@type = 'proc exec',
@description = '',
@creation_script = null,
@pre_creation_cmd = 'drop',
@schema_option = 0x0000000000000001,
@destination_table = 'DeleteOldOrder',
@destination_owner = 'dbo',
@status = 16

When I test this stored procedure the deletes occur on both databases?  I
have verified that the matching stored procedure on the subscriber db is
empty.

What???s intersting is that if I add the stored procedure to the publication
manually via the Publication Properties dialog, it works just fine.

What am I missing?

I think I found my answer.

Mark Price replied to Mark Price
28-Jan-10 08:17 AM
I think I found my answer.  It looks like I need to run the following after I
add the new articles, correct?

EXEC sp_refreshsubscriptions @publication = 'EMOBILE_OLTP_OLAP'
Post Question To EggHeadCafe