"Generic SQL Error "when attempting to execute a FetchXml query

1

I have a website that is using the XRM SDK to query CRM. For the most part I'm using Query Expressions, but I'm doing an aggregate count on one page, and hence, I'm using a Fetch XML query. I've debugged the website on my local developer machine, pointing to the Dev CRM server without any issue. However, if I update my config and point to our QA CRM, I get a generic SQL error. I turned on tracing with the Diagnostic Tool, and this is the error that I see when attempting to execute the SQL generated by the fetch XML:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: 
System.IO.FileLoadException: Could not load file or assembly 'microsoft.crm.sqlclr.helper, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException: 
   at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.Load(String assemblyString)

   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.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
   at Microsoft.Crm.CrmDbConnection.InternalExecuteReader(IDbCommand command, Boolean capturePerfTrace)
   at Microsoft.Crm.CrmDbConnection.ExecuteReader(IDbCommand command, Boolean impersonate, Boolean capturePerfTrace)
   at Microsoft.Crm.BusinessEntities.BusinessProcessObject.ExecuteQuery(CrmDbConnection dbConnection, IDbCommand command, ISqlExecutionContext context, Nullable`1 commandTimeout)

I've done some research and the best thing I could find was this KB article. But it's for SQL Server 2005, and CRM is SQL Server 2008. Does anyone know how to resolve this issue?

c#
.net
sql-server-2008
dynamics-crm-2011
asked on Stack Overflow Jan 7, 2013 by Daryl

1 Answer

1

It looks like it is a permission issue with the QA database. See http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/2465ac11-735c-4362-8da4-1ed672b0e261 and http://crmpt.blogspot.de/2011/12/charts-aggregated-by-date-problem.html for reference

Start with following setting for your organization database

ALTER DATABASE FOOBAR_MSCRM SET trustworthy ON

See http://technet.microsoft.com/en-us/library/ms187861.aspx

answered on Stack Overflow Jan 8, 2013 by ccellar

User contributions licensed under CC BY-SA 3.0