Could not find stored procedure .... error 2812

  • Jennifer Noelle Dunklin

    SSC-Addicted

    Points: 409

    Hi There,

    I am running sql server 2000 sp4.  I am trying to do a transactional replication of a large amout of data (40GB). I have created the publication without issue.  However when I generate the the subscription, I received the following error:

    Could not find stored procedure 'sp_mins_cl_temp_variables_sql'(my_subscribername)(data source); Error 2812

    Because the data set is large, I restored the database to the subscriber and select no, the schema and data already exist at the subscriber when I created the push subscription.  This generates a no sync initalization for the snapshot.  I found that there are certain procedures ( insert, update, and delete) procedures for the publication that do not get generated on the subscriber if you do a non sync initializaiton.

    I used sp_scriptpublicationcustomprocs 'publicationname' on the publisher.  This generates text for 3 procs that need to be executed on the subscriber.  I then executed these procs on the subscriber.  It was full of errors.

    Any one  has another suggestion???

    Thanks in advance for your help!!!

    Jenn

  • noeld

    SSC Guru

    Points: 96590

    >>> It was full of errors. <<<

    Which Errors ?


    * Noel

  • Jennifer Noelle Dunklin

    SSC-Addicted

    Points: 409

    I figured it out. The column was too narrow to capture the complete script that was generated by the store procedure sp_scriptpublicationcustomprocs.  I went into Tools, Options, Results in enterprise manger and increase the column width from 256 to 5000.  The excuted the script again.  The resulting scripts worked perfectly.  Thanks

  • noeld

    SSC Guru

    Points: 96590

    Ok glad you figure it out. I have been caught by this issue before too 🙂


    * Noel

  • Justin-14543

    SSCrazy

    Points: 2017

    I had the same issue before too.

    Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

  • saratrulz

    SSC Journeyman

    Points: 98

    Hi,

    I am running into the same issue what u faced in the past, I have SQL 2000 with sp4 and when configured the Tansactional replication without doing subscription intialization, It is giving an error that it couldn't find store prcedure' '.

    So can you tell me the procedure that you fallowed to solve this issue.

    Thanks in advance for your time.

  • Roy Ernest

    SSC-Dedicated

    Points: 38707

    First set the output for the Query analyzer to the max it will allow and then run sp_scriptpublicationcustomprocs 'publicationname' on the publisher. Once that is done copy the text that is on the output pane of your query analyzer and run it in the Subscriber database.

    -Roy

  • Welsh Corgi

    SSC Guru

    Points: 116520

    Jennifer Noelle Dunklin (9/22/2007)


    I figured it out. The column was too narrow to capture the complete script that was generated by the store procedure sp_scriptpublicationcustomprocs. I went into Tools, Options, Results in enterprise manger and increase the column width from 256 to 5000. The excuted the script again. The resulting scripts worked perfectly. Thanks

    I'm having the same issue in 2014.

    I followed the steps that you took but I did not find a results option?:unsure:

    Could not delete Publication. Could not find stored procedure Error: 2812

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    @ Welsh Corgi, did you ever get a solution to your 2014 issue?  I just came across the same problem today...thought i would check here first prior to google 🙂

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • PaulieDC

    Grasshopper

    Points: 11

    I copied a database from SQL Server 2008 to 2014 and had three orphaned replication instances lingering. I kept getting the 2812 error when I tried to delete them where it says it can't find the system stored procedure '' (yes, an empty string). I tried all these options that google returned, from Stack Overflow to Microsoft itself, some of them being quite the wild goose chase.

    Here's what blows the hung orphaned replication instances away (run it all as one script once you put in your database name that was getting replicated in the past):

    DECLARE @subscriptionDB AS sysname
    SET @subscriptionDB = N'YourDatabaseName'

    -- Remove replication objects from a subscription database (if necessary)
    USE master
    EXEC sp_removedbreplication @subscriptionDB
    GO

    exec sp_cleanupdbreplication

    • This reply was modified 1 month, 2 weeks ago by  PaulieDC. Reason: Typos, font color update
    • This reply was modified 1 month, 2 weeks ago by  PaulieDC.

    "For He spake and it was done." Psalm 33

  • mnadirwk

    Old Hand

    Points: 310

    1> make the publication

    EXEC sp_changepublication

    @publication = N'Publication_Name',

    @property = N'allow_anonymous',

    @value = 'FALSE'

    GO

    EXEC sp_changepublication

    @publication = N'Publication_Name',

    @property = N'immediate_sync',

    @value = 'FALSE'

    GO

     

     

    2> reinitialize publication

     

    3> after you see in distributor to subscriber history no replication transaction are available make allow_anonymous and immediate_sync true.

     

     

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply