Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Tran Repl Stored Procedure Replication Issue

Unanswered Tran Repl Stored Procedure Replication Issue

  • Friday, January 04, 2013 8:21 PM
     
      Has Code
    Sign In to Vote
    0

    Hello everyone,  Having a bit of a challenge here.

    We recently setup transactional replication in our environment.  Right now, things are working smoothly, with a few wrinkles we are ironing out.  This is where I could use some help. 

    I added a stored procedure for replication, using the following command:

    EXEC sp_addarticle @publication = N'Publisher',
	@article = N'cs_aspx_ClaimsPerformance',
	@source_owner = N'dbo',
	@source_object = N'cs_aspx_ClaimsPerformance',
	@type = N'PROC SCHEMA ONLY',
	@description = NULL,
	@creation_script = NULL,
	@pre_creation_cmd = N'drop',
	@schema_option = null,
	@identityrangemanagementoption = N'manual',
	@destination_table = N'cs_aspx_ClaimsPerformance',
	@destination_owner = N'dbo',
	@vertical_partition = N'false',
	@ins_cmd = N'CALL sp_MSins_dbocs_aspx_ClaimsPerformance',
	@del_cmd = N'CALL sp_MSdel_dbocs_aspx_ClaimsPerformance',
	@upd_cmd = N'SCALL sp_MSupd_dbocs_aspx_ClaimsPerformance',
	@force_invalidate_snapshot = 1
	
exec sp_refreshsubscriptions @publication = 'Publisher'

    Now, the stored procedure does get replicated......in part.  This is what I get in th subscriber:

    ALTER PROCEDURE [dbo].[cs_aspx_ClaimsPerformance]
	@QueryType INT = 1,
	@StartDate DATETIME = NULL,
	@EndDate DATETIME = NULL,
	@Description NVARCHAR(100) = NULL,
	@RowCount INT = 20,
	@Origin VARCHAR(100) = NULL,
	@SectorBlockID INT = NULL,
	@LeaderBCKey INT = NULL,
	@StartPeriodDescriptionID INT = NULL,
	@EndPeriodDescriptionID INT = NULL,
	@State NVARCHAR(100) = NULL,
	@InventoryID INT = NULL,
	@ShipMethodID INT = NULL,
	@ClaimType INT = NULL,
	@InvoiceDateStart DATETIME = NULL,
	@InvoiceDateEnd DATETIME = NULL,
	@DateShippedStart DATETIME = NULL,
	@DateShippedEnd DATETIME = NULL,
	@DeliveredDateStart DATETIME = NULL,
	@DeliveredDateEnd DATETIME = NULL,
	@InventoryCategoryID INT = NULL,
	@ClaimEnteredBY NVARCHAR(MAX) = NULL,
	@OrderEnteredBY NVARCHAR(MAX) = NULL
	
/* WITH ENCRYPTION */

AS

BEGIN
-- This is a placeholder implementation of the schema object to facilitate deferred creation of the actual implementation.
raiserror(N'This is a placeholder implementation of the schema object to facilitate deferred creation of the actual implementation.',16,-1)
END

    Anyone got any ideas what is going on here?  Why am I getting this RAISERROR message vs the actual procedure code that is in the publisher database?

    Thanks,

    Nathan


     

All Replies

  • Monday, January 07, 2013 2:23 PM
    Moderator
     
     
    Sign In to Vote
    0
    Can we see the stored procedure - would it be encrypted?

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

     
  • Monday, January 07, 2013 9:25 PM
     
     
    Sign In to Vote
    0

    Hi nathen,

    i can see first one is your source code to add an article. (EXEC sp_addarticle).

    your second block is an alter statement (ALTER PROCEDURE). And i believe you scripted from SQL server. When you script any object that is depended on something or to validate input values SQL must have use it.

    "Anyone got any ideas what is going on here?  Why am I getting this RAISERROR message vs the actual procedure code that is in the publisher database?"

    If you are concern about Raiseerror message i can say SQL server have knowledge of what it suppose to do and ready to handle it.

    let me put you this way you scripted the above object with alter command what happens if you accidently or intentionally change some values that are not accepted by the procedure. It simply validates the values and if they are not suitable SQL have instruction readily to log the error.

    If this is not what you looking for please describe more about your issue.

    Thanks

    Kumar

     
  • Tuesday, January 08, 2013 3:03 PM
     
     
    Sign In to Vote
    0
    Can we see the stored procedure - would it be encrypted?

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    No, the stored procedure isn't encrypted.  As a matter of fact, if I just script out the stored procedure from SSMS as an alter, add a single line of comment, and recompile it, it replicated just fine.  So it appears to be an issue during the snapshot process.

    Thoughts?

    Nathan

     
  • Tuesday, January 08, 2013 3:05 PM
     
     
    Sign In to Vote
    0

    Kumar,

    If I goto the publisher, right click on the stored procedure, and script it out as an alter statement, add a comment to it, and the recompile the stored procedure (that is the article above), 5 seconds later, the code with the comment is replicated without issue.

    Removing the comment and recompiling works just as well.  So it appears that there is an issue during the initial snapshot presentation.

    Please note that I am able to run the stored procedure without issue after the replication.

    Natahn