Replication is renaming my Stored Procedures on Client only

I am new to replication but I have a question, I have my server which is using a transaction replication process and a client with using SQL Express with replication turned on. I have the identical DB on both nodes yet when I subsrcibe to the server from the client, data is transfer but for some reason, all of my stored procedures are renamed with sp_MSDel_DBOxxxx. How do I keep SQL from renaming these SP's? The server maintains the origial names to the SPs its just happening on my client.

Thanks

You can customize the names of these stored procs in the article properties.|||

If I understand correctly, the SPs you're seeing on client db are not the renamed copy of your original sps, those sp_MSDel_DBOxxxx sps are created and used exclusively by replication, they should not affect your existing sps in anyway. However, if you want to control how those replication sps are named you can use the approach Greg suggested.

Thanks,

Zhiqiang Feng

|||

My Server SP's are named properly, for example usp_AddActivityLog is the proper name of my custom SP. After replication occurs the name of the same SP on my client is now named sp_MSdel_dboActivityLog.

In the Article Property <Default is based on stored procedure name> is in the destination object name. And here is the code for SP:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[usp_AddActivityLog]

@.activityLogID bigint,

@.agencyofficer_id bigint,

@.lookup_id int,

@.remarks varchar(200) = '',

@.caseLinkID bigint = null,

@.startTime dateTime = null,

@.endTime datetime = null,

@.location varchar(200) = '',

@.callForServiceID bigint = 0

AS

SET NOCOUNT ON

DECLARE @.ret int

IF (@.activityLogID <> 0)

BEGIN

UPDATE ActivityLog SET

EndTime = @.endTime,

fk_CaseLinkID = @.caseLinkID,

remarks = @.remarks,

location = @.location,

fk_CallForServiceID = @.callForServiceID

WHERE pk_activityID = @.activityLogID

SET @.ret = @.@.ERROR

IF @.ret <> 0

BEGIN

INSERT INTO ErrorLog VALUES ('Update of ActivityLog failed.', GetDate())

RETURN -1

END

ELSE

RETURN @.activityLogID

END

ELSE

BEGIN

INSERT INTO ActivityLog (fk_AgencyOfficersID, fk_ActivityLookupID, StartTime, EndTime,Remarks, fk_CaseLinkID, location, fk_CallForServiceID)

VALUES (@.agencyofficer_id, @.lookup_id, @.startTime, '', @.remarks, @.caseLinkID, @.location, @.callForServiceID)

SET @.ret = @.@.ERROR

IF @.ret <> 0

BEGIN

INSERT INTO ErrorLog VALUES ('Insert into ActivityLog failed.', GetDate())

RETURN -1

END

ELSE

RETURN @.@.identity

END

Any Idea why they are being renamed?

Thanks

|||

Distribution agent doesn't rename anything, it just applies what's in the snapshot folder. Look in your snapshot folder to see what stored procedures are being generated. If the proper names are there, then the procs you see must have previously existed. If the incorrect proc names are being generated, then you need to doublecheck the article properties to see what the problem could be.

I just tried your scenario on a SQL 2005 machine with different proc names, they were created properly at the subscriber. IF there's something else i missed, please let me know.