A SQL Server error occurs when importing a solution or modifying customizations in Microsoft Dynamics CRM 2011

Symptoms

A SQL Server error occurs when importing a solution or modifying customizations in Microsoft Dynamics CRM 2011.

When platform tracing is enabled, one of the following errors appears in the call stack:

Inner Exception: Microsoft.Crm.Tools.ImportExportPublish.ImportGenericException: Import failed
   at Microsoft.Crm.Tools.ImportExportPublish.ImportXml.RunImport(String[] ImportEntities)
   at Microsoft.Crm.Tools.ImportExportPublish.ImportXml.RunImport()
   at Microsoft.Crm.WebServices.ImportXmlService.ImportSolution(Boolean overwriteUnmanagedCustomizations, Boolean publishWorkflows, Byte[] customizationFile, Guid importJobId, Boolean convertToManaged, ExecutionContext context)
Inner Exception: System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'sp_MS_marksystemobject', database 'mssqlsystemresource', schema 'sys'.

>Crm Exception: Message: Import failed, ErrorCode: -2147188706, InnerException: Microsoft.Crm.Tools.ImportExportPublish.ImportGenericException: Import failed ---> System.Data.SqlClient.SqlException: Could not find stored procedure 'p_FreeProcCache'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
   at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command, Boolean capturePerfTrace)
   at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate, Boolean capturePerfTrace)
   at Microsoft.Crm.Metadata.CascadingSprocGenerator.ProcessDelete(Boolean alter)
   at Microsoft.Crm.Metadata.CascadingSpGenerator.Process(ISqlExecutionContext context)
   at Microsoft.Crm.Tools.ImportExportPublish.RootImportHandler.RunImport(String[] ImportEntities)

↑ Back to the top

Cause

This issue occurs when the dbo.p_FreeProcCache procedure is missing from the System Stored Procedures in the affected organization database. To validate the cause, the following query can be ran against the affected database which will return no results:

select * from sys.objects where object_id = object_id(N'[dbo].[p_FreeProcCache]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1

↑ Back to the top

Resolution

To resolve the issue, the p_FreeProcCache procedure must be created as a system object by a SQL Server system administrator. The following SQL statement can be ran on the affected database to resolve the issue:

if exists (select 1 from sys.objects where object_id = object_id(N'[dbo].[p_FreeProcCache]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1) drop proc [dbo].[p_FreeProcCache] go CREATE procedure [dbo].[p_FreeProcCache] AS begin declare @i int select @i = db_id(); DBCC FLUSHPROCINDB(@i); end go exec sp_MS_marksystemobject N'p_freeProcCache'

↑ Back to the top

Keywords: kbmbspartner, kbmbsmigrate, kbsurveynew, kb

↑ Back to the top

Article Info

Article ID	:	2863545
Revision	:	1
Created on	:	1/7/2017
Published on	:	6/20/2013
Exists online	:	False
Views	:	82

Microsoft KB Archive Search

A SQL Server error occurs when importing a solution or modifying customizations in Microsoft Dynamics CRM 2011

Symptoms

Cause

Resolution