none
Replication troubleshooting with stored procedures and UDFs RRS feed

  • Question

  • I'm replicating a small DB from Server A to Server B with a snapshot agent.  I'm replicating the whole DB: table, views, stored proc, udf and I keep running into lots of issues with the stored proc and user-defined funcs. Things like "Invalid Column name" or dependency issues. The snapshot always executes successfully, it's the distribution that errors out.

    Here's one error.

                

    Agent message code 207. Invalid column name 'prjPhaseGroup'.
    Number:  
    Message: CREATE PROCEDURE [dbo].[AxAttachToClientInvoice]
       @tDate                  smalldatetime,
       @tProject               int,
       @tStatus                tinyint  OUTPUT,
       @tClientInvoice         int      OUTPUT
    AS
       -- Set billable status to defer & reset to billable if attached to an invoice
       SET @tStatus = dbo.AxTransactionStatus_Defer()

       DECLARE @prjPhaseGroup int
       SELECT @prjPhaseGroup = prjPhaseGroup
          FROM AxProject WHERE prjKey = @tProject

    The table on the publication has no column "prjPhaseGroup" and same on the subscriber. I can add the column and the error will go away but it only brings me to the next issue. During replication subsequent changes to objects are not being copied I think is the problem..

    I've tried making a publication for tables and separate publication for the views, udf, stored procs.  I've also tried changing the replication article properties of stored procs to "Execution of the stored procedure" but I get this error:

    Stored procedures containing table-value parameters cannot be published as '[serializable] proc exec' articles.

    Could it be a version problem? Should I try generating stored procedure scripts?  Will initializing subscriber with a backup instead of snapshot be helpful?

     Thanks
    Sunday, June 30, 2019 6:10 PM

All replies

  • Hi iceandjames,

     

    >>I can add the column and the error will go away but it only brings me to the next issue. During replication subsequent changes to objects are not being copied I think is the problem..

     

    When you create a procedure on the pubication, did you regenerate the snapshot and reinitialize the subscription?

     

    >>Will initializing subscriber with a backup instead of snapshot be helpful?

     

    Yes, you can try to do it. For more details, please refer to Initialize SQL Server replication using a database backup

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 2, 2019 2:11 AM
  • I am not sure what kind of replication you using exactly.

    As I understand you are using snapshot replication. How often u re-snapshoting the publication? what settings u have for snapshot delivery? (keep existing table? or drop tables on destination?)

    If you using transactional replication then most probably your replication is not set to replicated DDL which means the newly added columns on Publisher wont be replicated down to subscriber.

    Wednesday, July 3, 2019 7:13 PM
  • It sounds like this is a legacy stored procedure which references columns which no longer exist in the base tables.

    Replication is assuming that you have some form of source control/deployment which ensures that all the databases objects reference existing columns.

    You will need to ensure that all views, functions and stored procedures are not legacy, and only publish the valid ones in a publication.

    You can always script out your database and deploy it in a shell database to figure out what is legacy and broken and what is good.

    Wednesday, July 3, 2019 7:21 PM
    Moderator