SQL Server - 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?
SQL Server Replication Discussions
 
Stored procedure
(1)
Exec
(1)
Proc
(1)
Transactional
(1)
Addarticle
(1)
Replicates
(1)
Databases
(1)
Cmd
(1)
  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'
Previous Discussion:  Manual Upload of Data from Subscriber to Publisher Database
Create New Account
help
Multi-Parameter Stored Procedure help SQL Server stored procedure 1: exec getcolor @color = blue. . . . this runs fine. stored procedure 2: exec getcolors @colors in('red', 'white', 'blue'). . . . . . . . .bombs code is create procedure getcolors @colors nvarchar (50
Split variable and exec stored procedure SQL Server Hi all, having a variable with values separated by ; how can I split it to execute a stored procedure for each of them? Like declare @MyVariable varchar(50) set @MyVariable = 'value1;value2;value3' and exec MyStored for each of value1, value2, value3, etc. Thanks a lot. Luigi SQL Server Programming Discussions SQL Server 2008 (1) SQL Server 2000 (1) SQL Server (1) Stored procedure (1) Declare (1) Varchar (1) Exec (1) MyVariable (1) Hi Luigi, See http: / / www.sommarskog
Return table from Stored Procedure into another stored procedure SQL Server hi, can this be done? i have stored procedure 1 that calls stored procedure 2 in the FROM clause. Stored procedure 2 is supposed to return a table. For
stored procedure within stored procedure SQL Server I have problem in stored procedure can i use the stored procedure as a table object ie i have two sp like QMENU AND QNEWMENU above two
stored procedure template SQL Server I use SSMS template to create stored procedure for SQL Server 2000. SSMS has following code to drop stored procedure exists, - - Drop stored procedure if it already exists IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'<Schema_Name, sysname