replication [sys] schema stored procedure missing

• Anam Verma

  SSCertifiable

  Points: 5365

  More actions

  July 29, 2010 at 10:53 pm

  #227354

  I have inherited this database and one of the job is failing. on looking at the error message, i found that the agent job is throwing an error on the publication database (yes its replicated)

  the stored procedure throwing error is "sys.sp_MSdrop_expired_subscription". this stored is called from "sys.sp_MSdrop_expired_subscription".

  on further investigation, i found that the stored proc "sys.sp_MSdrop_expired_subscription" is missing from the database. on googling i found the procedure definition at http://www.g-productions.nl/mssql_procedure.php?name=sp_MSdrop_expired_subscription

  now the problem is when i try to create this stored procedure, sql server throws me error msg

  Msg 2760, Level 16, State 1, Procedure sp_MSdrop_expired_subscription, Line 188

  The specified schema name "sys" either does not exist or you do not have permission to use it.

  I am sysadmin on the database server and i assume i should be allowed to create this stored procedure but i was wrong 🙁

  I am on SQL SERVER 2008 R2 (10.50.1600)

  Any help is appreciated

• Ramesh @ SQLCentral

  SSC Veteran

  Points: 246

  More actions

  August 2, 2010 at 11:35 pm

  #1201357

  Hi Verma,

  "sys.sp_MSdrop_expired_subscription"

  stored procedure will be available under mssqlsystemresource database and no need to create it on user databases.