2011-07-16

Could not find stored procedure 'proc_UpdateStatisticsNVP' June CU 2011


Issue:

Log Name:      Application
Source:        Microsoft-SharePoint Products-SharePoint Foundation
Date:          15/07/2011 12:00:31 AM
Event ID:      5586
Task Category: Database
Level:         Error
Keywords:      
User:          ray
Computer:     SPS2010
Description:
Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below.


Could not find stored procedure 'proc_UpdateStatisticsNVP'.



Cause:

After the June 2011 CU I started getting this error every night in the logs.
A quick search through \Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\SQL
showed [dbo].[proc_UpdateStatistics] was calling this proc.

So I assume where ever I find [dbo].[proc_UpdateStatistics] I should also find [dbo].[proc_UpdateStatisticsNVP]


Fix:

The fix requires that each DB that has [dbo].[proc_UpdateStatistics] also have [dbo].[proc_UpdateStatisticsNVP] the following SQL Query will help you identify the DB's:


exec sp_msforeachdb 'if exists(select name from [?].sys.objects where type = ''P'' and name like ''%proc_UpdateStatistics%'') AND not exists(select name from [?].sys.objects where type = ''P'' and name like ''%proc_UpdateStatisticsNVP%'') select ''?'''

This will give you the DB's that have [dbo].[proc_UpdateStatistics] but don't have [dbo].[proc_UpdateStatisticsNVP] then all you need to do is create the proc using an existing one...

4 comments :

  1. great script, thanks! Used it to determine which it was missing from then found a DB that had it and scripted it to the rest.

    ReplyDelete
  2. Can you explain to me how to script it to the missing databases?

    ReplyDelete
  3. Just to clarify -the UpdateStatisticsNVP stored proc only needs to be added to databases with NameValuePair tables, of which there are typically only 2 - WSS_Content and SharePoint_AdminContent. You could see this error if there is a reference to this stored proc in database where it doesn't belong. In which case, you'll see it being called near the bottom of UpdateStatistics. Remove that errant reference and you're good to go!

    ReplyDelete
  4. You should not change the database other than using the api or else Microsoft will not support you.

    ReplyDelete