Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: Urgent: My SQL server encountered a strage problem...

    I'm trying to re-enable replication back on the server, it used to work, then we disable it for a while, then I.T. decided to change the server name, NOW, it encounters a bunch of problems when trying to configure replication....

    there are a bunch of stored procedures are missing from teh MASTER database, is there are way to restore them without trashing our current production server???

    also, when I try to create a missing stored procedure by copying the code from another server, sp_adddistributiondb, it gives me this error,
    "Ad hoc updates to system catalogs are not enabled", what do this mean and how can i enable it.

    thanks in advance with any answers/suggestions......

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    To overcome that error of adhoc updates to system catalogs is not allowed- run sp_configure 'allow updates',1 from query analyzer before running the previous statement.

    As the situation is server was renamed, its better to delete the current replication topology and re-fresh the replication using Wizard or scripts at your end.

    BOL specified:

    Issues with Remote Logins and Replication
    If the computer has any remote logins, for example, if it is a replication Publisher or Distributor, sp_dropserver may generate an error similar to this:

    Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
    There are still remote logins for the server 'SERVER1'.

    To resolve the error, you may need to drop remote logins for this server. If replication is installed, disable replication on the server before running the sp_dropserver stored procedure.

    To disable replication using the SQL Server Enterprise Manager

    Expand a server group, and then expand the Distributor (the server that contains the distribution database).


    Right-click the Replication folder, and then click Disable Publishing.


    Complete the steps in the Disable Publishing and Distribution Wizard.


    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Apr 2004
    Posts
    4
    thanks for the tips...
    now i'm seeing this..what does this means? i'm stumbled...

    also, is there a way to restore all teh stored_procedures that are missing from the master DB??
    Attached Thumbnails Attached Thumbnails untitled.jpg  

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    What is the level of service pack on SQL & OS?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Apr 2004
    Posts
    4
    win2k and service pack 2...thanks

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    How about on SQL server and test & try to upgrade to SP4 on Win2K.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Apr 2004
    Posts
    4
    will try that....but what i don't understand is how can the store procedures gone Missing....

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    If the SQL server is installed with no service pack, then few of the columns & tables are missing. As the latest service pack is tend to replace/update system tables with this type.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •