Replication error

  • Roust_m

    SSCoach

    Points: 17364

    Hi!

    I get this error message when subscribing or reinitializing subscriptions on one of my databases.

    (I am running MS SQL 2000 Ent Server + sp3)

    JUPITER-stock-STG_Data-545 Executed as user: DEALINE.CENTER\SQL_Admin. Cannot insert the value NULL into column 'publisher', table 'tempdb.dbo.MSreplication_agent_status'; column does not allow nulls. INSERT fails. [SQLSTATE 23000] (Error 515) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.; MsgID: 3621;

    How can I correct this?

    Thanks.

     

     

  • This was removed by the editor as SPAM

  • Rodrigo Acosta

    SSC-Insane

    Points: 21261

    Did you try to rebuild the publication?

    You will need to drop all subscriptions, re create the publication and then subscribe again.

    It can take a long time, but most of the times it will work.

  • sqldba_ind

    SSC Enthusiast

    Points: 173

    Hi SSChasing Mays

    In fact I also got same Problem to day,can tell if arrived any solutions for this issue.

  • Aaron C. Sentell

    Ten Centuries

    Points: 1167

    I'm just guessing here, but you might verify that the publishing server has a record in sysservers on the distributor SQL Server.

  • Steve Cabiness

    Valued Member

    Points: 58

    The solution to this problem is really easy so don't panic. The @agent_id supplied to the “Log Reader agent startup message” job step is incorrect and needs to be changed to the correct agent id.

    Step 1.:

    In your Distribution database look up the correct agent id value for your publisher.

    Use the following code substituting your Distribution database name.

    select id, name from ~Distribution database name~.dbo.MSLogReader_agents

    The results:

    Id: ~id value~

    Name: ~Publisher SQL Server name-Publication name~

    Step2.:

    On your SQL Server that handles distribution, look for the Log Reader job for the publication that is failing. The category should be “REPL-LogReader” and the job name should be something like ~Publisher SQL Server name-Publication name~. For job step 1: “Log Reader Agent startup message”, look at the value specified for the @agent_id parameter. This value should match the value that was identified from Step 1 from the MSLogReader_agents table.

    sp_MSadd_logreader_history @perfmon_increment = 0, @agent_id = ~id value~, @runstatus = 1, @comments = 'Starting agent.'

    Corrective action: Not too sure why the value for the @agent_id parameter would change but supplying the correct id and rerunning the job corrects this issue.

    -Steve Cabiness

  • SunilDutt Nidamarthi

    Right there with Babe

    Points: 733

    The above solution did not work as the agentid = 1 in the database also.

    Cud u suggest any other.

    In the Log Reader Job.

    STP is executing successfully.

    Step 2 is unsuccessful .

    The code is

    -Publisher [YG2K8] -PublisherDB [AdventureWorks] -Distributor [YG2K8] -DistributorSecurityMode 2 -Continuous

    What shud we do now???

  • iamsuryakiran

    SSC Journeyman

    Points: 81

    Has any one found a solution yet? Please suggest !

  • jason.c.dinkins

    SSC Enthusiast

    Points: 140

    Do SELECT @@SERVERNAME at the publisher. If it is null do an sp_addserver @server = 'yourservername', @local = 'local'

    Reboot.

    Take care.

  • Olson

    SSC Rookie

    Points: 25

    Steve Cabiness - Friday, November 2, 2007 11:45 AM

    The solution to this problem is really easy so don't panic. The @agent_id supplied to the “Log Reader agent startup message†job step is incorrect and needs to be changed to the correct agent id.Step 1.: In your Distribution database look up the correct agent id value for your publisher. Use the following code substituting your Distribution database name.select id, name from ~Distribution database name~.dbo.MSLogReader_agents The results: Id: ~id value~Name: ~Publisher SQL Server name-Publication name~Step2.: On your SQL Server that handles distribution, look for the Log Reader job for the publication that is failing. The category should be “REPL-LogReader†and the job name should be something like ~Publisher SQL Server name-Publication name~. For job step 1: “Log Reader Agent startup messageâ€, look at the value specified for the @agent_id parameter. This value should match the value that was identified from Step 1 from the MSLogReader_agents table. sp_MSadd_logreader_history @perfmon_increment = 0, @agent_id = ~id value~, @runstatus = 1, @comments = 'Starting agent.'Corrective action: Not too sure why the value for the @agent_id parameter would change but supplying the correct id and rerunning the job corrects this issue.-Steve Cabiness

    This did the job for me although my issue was on the snapshot job, the problem was the same. Many thanks for posting all be it 10 yrs ago...

  • mnadirwk

    Old Hand

    Points: 310

    make the publication property  allow_anonymous and immediate_sync false, reinitialize publication with a new snapshot.

     

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

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