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! Barb
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.
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? Thanks, Barb
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....
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?? Thanks, Barb
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.
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! Barb
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! [] Barb
