July 29, 2010 at 10:53 pm
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
August 2, 2010 at 11:35 pm
Hi Verma,
"sys.sp_MSdrop_expired_subscription"
stored procedure will be available under mssqlsystemresource database and no need to create it on user databases.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply