May 23, 2017 at 1:12 pm
This is occurring on one of my SQL Server 2012 Enterprise instances. I discovered it when a dashboard application that leveraging a DMV query to retrieve active sessions started to fail for this specific server. Running the DMV query on the server via SSMS returns the following error.
Msg 8181 - Text for 'MyProcedureName' is missing from the system catalog.
Despite the recommendation provided in the error message text, dropping and re-creating the stored procedure does not fix the issue.
This is the most simplified example that consistently reproduced the issue.
SELECT er.session_id
FROM sys.dm_exec_requests AS er
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
Msg 8181, Level 16, State 1, Line 1
Text for 'MyProcedureName' is missing from the system catalog. The object must be dropped and re-created before it can be used.
Doing a google search reveals very little information on this error 8181.
Any ideas on how to resolve this?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 23, 2017 at 1:55 pm
Do you see anything in the syscomments table for your procedure when running:select * from syscomments
where text like '%proc name here%'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 24, 2017 at 2:29 am
Henrico Bekker - Tuesday, May 23, 2017 1:55 PMDo you see anything in the syscomments table for your procedure when running:select * from syscomments
where text like '%proc name here%'
SELECT * FROM sys.sql_modules
WHERE object_id = OBJECT_ID('ProcedureNameHere')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply