Post reply

Distribution agent reports Invalid Object

  • November 8, 2005 at 1:14 pm

    #166884

    Hi all...

    I have a transactional publication set up with multiple subscribers and a remote distributor. I have recently added an additional subscriber to the topology. I get everything all nice and set, create and move the snaphot over and find that everything is replicating accordingly.

    After a few hours, the agent reports that a particular table in the subscriber database is invalid; as if it did not exist. I can query the table at the publisher and the subscriber and the table is owned by dbo but the agent keep showing invalid object 'table' everytime I try to restart it.

    What gives?

     

     

  • November 9, 2005 at 9:53 am

    #603063

    "Invalid" doesn't necessarily mean "does not exist". Take a look at your stored procedures that the distr agent calls:

    sp_MSins_TableName / sp_MSupd_TableName / sp_MSdel_TableName

    Try to execute yourself in Query Analyzer at the subscriber. Perhaps this will give you some insight...

    Regards,

    ChrisB MCDBA OCP MyDatabaseAdmin.com

    Chris Becker bcsdata.net

  • November 16, 2005 at 7:09 pm

    #604333

    Just curious - what was the outcome of this issue?

    ChrisB

    Chris Becker bcsdata.net

  • November 17, 2005 at 9:08 am

    #604449

    Chris,

    Thanks for the response. Prior to seeing your response, I decided to try this:

    dropped the subscription 

    executed sp_removedbreplication on the subscriber (the theory being that perhaps there were residual items of metadata that had to be cleaned up first)

    re-added the subscription

    It has been replicating with no problems ever since.

    Thanks again,

    Lorenzo

     

  • November 18, 2005 at 5:29 am

    #604602

    Lo and behold:

    I got a page last night from SQL Server indicating that this has happened again -- same table!

    Invalid object name 'Table'.

    (Source: MyServer (Data source); Error number: 208)

    I have looked at the procedure, as suggested. It inserts into the same table that the agent claims is non existent. When I tried to execute the call myself I got 'Cannot insert duplicate key'. That tells me that the insert succeeded even though the agent reports the object invalid.

    ???

     

  • November 18, 2005 at 11:16 am

    #604705

    Interesting. Perhaps no transactions were replicated to that table until last night?

    Is dbo the table owner?

    Did you restart the agent successfully? Or is it still failing?

    If still failing, run a trace on subscriber and try to capture exact stmt that fails. Should capture the 'starting' events...

    Chris

    Chris Becker bcsdata.net

  • November 18, 2005 at 12:35 pm

    #604725

    That is very possible (that the table didn't get inserted until last night).

    I had checked the table owner and it is/was dbo in both places. The agent continues to fail reporting the same error and profiler shows that the insert procedure created by replication is being executed (sp_MSins_TableName). The syntax is valid; a simple insert using parameters from the procedure.

    This server (as well as the others that are operating correctly) is SQL Server 2000 Standard with SP3a.

     

     

  • November 18, 2005 at 1:20 pm

    #604732

    Try to execute the sp_MSins call in Query Analyzer. You can delete that record after, or wrap in trans & rollback.

    Is there a trigger on that table at subscr? (longshot)

    I'm drawing blanks - perhaps drop subscription & re-create for that article only.

     

    Chris Becker bcsdata.net

Viewing 8 posts - 1 through 7 (of 7 total)

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