When I was at a customer recently, I found a strange error in the Event Viewer:
Event 5586:
Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below.
Could not find stored procedure “proc_UpdateStatisticsNVP”.
This problem is caused by updating SharePoint 2010 to SP1. This update will try to execute this stored procedure, but it’s not there. If you go to location: “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\SQL” you’ll see a file called “STOREUP.SQL”. This file will call this stored procedure. For one or another reason, the creation of the stored procedure fails when installing SP1. If you look at the file called “STORE.SQL”, you will see that there is a create statement for this stored procedure. Strange..
Ok, now that we know what the error is, we have to fix it. The first thing you have to do is to find out which databases do not have this stored procedure. You can do this by executing the following statement:
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 return you the databases without the “proc_UpdateStatisticsNVP” procedure. The next step is to create this stored procedure by using the following script:
That’s all you have to do! Be sure that you execute this script on the right database. Use the drop down in SQL Management Studio, or start the script with “USE [DBName]”.
