SQL Server Performance

Could Not Find Stored Procedure

Discussion in 'Getting Started' started by BarbG, Jul 2, 2009.

  1. BarbG New Member

    Hello All,
    I am trying to create a test database on a server which I recently upgraded to SP 4 for SQL Server 2005. I can usually do this by creating a blank database and then restoring a backup of the production database to it. Now, however, I get the following message: "Create failed for Database XXXX... Could not find stored procedure 'sp_MSreplicationcompatlevel'... Microsoft SQL Server, Error: 2812". This sp exists in the production master db but is missing from my test master db. I have tried running the sp in a query in Management Studio but it doesn't work. I am new to all of this and am hoping for a relatively easy way to get that sp into my test master db; is this possible??
    Thank you in advance!
  2. FrankKalis Moderator

    Welcome to the forum!
    As far as I'm aware there is no SP4 for SQL Server 2005, There is however one for SQL Server 2000.
    Are you by any chance trying to restore a SQL Server 2005 backup on a SQL Server 2000 box? If so, this won't work. I would porbably script out the database schema, apply it to a blank database on the test server and then use SSIS to transfer the data. But actually it really would be better if both test and production environment are very similar with respect to hardware, server version and patch level.
  3. BarbG New Member

    Thank you, Frank!
    No, my mistake, it was SP 4 for SQL Server 2000; both exist on that server.
    I get the error message when I attempt to create the blank database. It's there with system tables but not much else. I have no experience with scripting database schemas or using SSIS. Where could I go for instructions?
    I don't see my name in the list of users for the blank database. Would this have anything to do with my problem?
  4. Adriaan New Member

    New db-s are based on the model system db.
  5. BarbG New Member

    Thanks, Adriaan. What would I need to do to get the missing sp in there?
  6. MohammedU New Member

    When system executes sp_dbcmptlevel procedure it calls sp_MSreplicationcompatlevel procedure...
    If it is missing means some one might be delete it...
    Try add it...and make it as system procedures....
  7. Adriaan New Member

    Script out the procedure, connect to model, and execute the script.
  8. BarbG New Member

    Hi All,
    I appreciate your suggestions, and I have tried scripting and executing the procedure but have had no luck. I get a message saying that sp_MSreplicationcompatlevel is an invalid name. I have also not been able to copy the procedure in from another location. What am I missing??
  9. satya Moderator

    Run XP_MSVER and post details here, I doubt that SP4 has been installed successfully and I think that SP is included in this package.
  10. BarbG New Member

    Hi Satya,
    Here's what I got:
    1 ProductName NULL Microsoft SQL Server
    2 ProductVersion 524288 8.00.2055
    3 Language 1033 English (United States)
    4 Platform NULL NT INTEL X86
    5 Comments NULL NT INTEL X86
    6 CompanyName NULL Microsoft Corporation
    7 FileDescription NULL SQL Server Windows NT
    8 FileVersion NULL 2000.080.2055.00 Hotfix 2280
    9 InternalName NULL SQLSERVR
    10 LegalCopyright NULL © 1988-2004 Microsoft Corp. All rights reserved.
    11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
    12 OriginalFilename NULL SQLSERVR.EXE
    13 PrivateBuild NULL NULL
    14 SpecialBuild 134676480 NULL
    15 WindowsVersion 248381957 5.2 (3790)
    16 ProcessorCount 4 4
    17 ProcessorActiveMask 15 0000000f
    18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
    19 PhysicalMemory 3071 3071 (3220455424)
    20 Product ID NULL NULL

    Also, here is the version, level and edition information:
    8.00.2055 SP4 Standard Edition
    Thank you in advance for your help!
  11. satya Moderator

    Ok search for the directly where it extracted the SP4 files for this script under .SQL files.
  12. BarbG New Member

    I have finally scripted the sp, run the script and got that file where I need it to be. Many thanks to all who helped! [:D]

Share This Page