Generic SQL Error while using Query Expression

Generic SQL Error while using Query Expression

Check in the query expression "Contains" operator used or not. If it then have to replace it as "Like" operator.
 Contains not working in 365 because generic SQL error occurred because of  full text not enabled in the table. It is only enabled in KBArticleEntity.
So we have used like operator but like we have to manually add % infront of value otherwise data not coming.
https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/gg327700(v=crm.8)#members

Dynamics CRM Attachment table in SQL

In the CRM attachment are classified in to two.

• Notes-where your attachment can save
• Email- Adding your attachment on the email

Notes:

select DocumentBody,FileName, * from AnnotationBase

DocumentBody- base64 string where your attachment details contains

Email:

select * from ActivityMimeAttachmentBase ->Table

select Body, * from ActivityAttachment ->View

Attachment are save in the attachment table

select Body, * from AttachmentTable ->Table

Trace/Track CRM SQL Queries using SQL PROFILER

When we use to debug the CRM query using SQL profilers, the followings are important events.

Events	Name
Error And Warning
	Blocked Process report
	Exeception
TSQL
	SQL:Batch completed
	SQL:stmt completed
Store Procedure
	RPC:Completed
	SP:Completed
	SP:stmt completed
Locks
	Dead Lock graph

Query expression to track/trace SQL query events are Store procedure ->RPC Completed

Check the CRM Database overall usage

If the CRM is on-premises, it is useful to find out about which out-of-the-box CRM entities are used, custom entities if any, number of records, and size of tables. You can find out all about this easily through few clicks and without having to write any T-SQL.

• Logon to SQL Server Management Studio
• Right-click on the CRM database (e.g. org_MSCRM), then select Reports > Standard Reports > Disk Usage by Table
• A report will be loaded in a new tab. This may take a couple of minutes depending on the overall size of the database.

Database cannot be started in this edition of SQL Server because it contains a partition function 'AuditPFN'

Today I faced this problem when trying to restore a Dynamics CRM  database from a SQL Server  Enterprise Edition to a SQL Server  Developer Edition or Standard edition.

It got me crazy as I did not created any partition on the original DB and this problem didn’t allow to me to restore the DB

Cause:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Database 'Org_MSCRM' cannot be started in this edition of SQL Server because it contains a partition function 'AuditPFN'. Only Enterprise edition of SQL Server supports partitioning. Database 'Org_MSCRM' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

Solution:

IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name='AuditPScheme') BEGIN SELECT   CASE WHEN ind.type != 1    THEN     'DROP INDEX [dbo].[AuditBase].' + QUOTENAME(ind.name) + ' '    ELSE ' '   END +   'CREATE ' + CASE is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END  +   ind.type_desc + ' INDEX ' + QUOTENAME(ind.name  COLLATE SQL_Latin1_General_CP1_CI_AS )  + ' ON [dbo].' +  QUOTENAME(OBJECT_NAME(object_id)) + ' (' +     REVERSE(SUBSTRING(REVERSE((    SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','    FROM     sys.index_columns sc     JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id    WHERE     OBJECT_NAME(sc.object_id) = 'AuditBase' AND     sc.object_id = ind.object_id AND     sc.index_id = ind.index_id    ORDER BY index_column_id ASC    FOR XML PATH('')         )), 2, 8000)) + ')' +   CASE WHEN ind.type = 1    THEN     ' WITH (DROP_EXISTING = ON) ON [PRIMARY]'    ELSE     ' '   END  as Script INTO #indexesScript FROM sys.indexes ind JOIN sys.partition_schemes ps on ind.data_space_id=ps.data_space_id WHERE   OBJECT_NAME(object_id) = 'AuditBase'   AND ps.name = 'AuditPScheme'   AND is_unique_constraint = 0 SELECT * FROM #indexesScript   DECLARE @recreateScript nvarchar(max) DECLARE indScript CURSOR FOR SELECT Script FROM #indexesScript OPEN indScript FETCH NEXT FROM indScript INTO @recreateScript   WHILE @@FETCH_STATUS = 0   BEGIN     BEGIN TRANSACTION t1   Execute sp_executesql @recreateScript     IF @@ERROR > 0   BEGIN    ROLLBACK TRAN t1    declare @message varchar(max)    set @message = 'Audit history recreate index failed. SQL: ' + @recreateScript       RAISERROR (@message, 10,1)   END   ELSE   BEGIN    COMMIT TRAN   END   FETCH NEXT FROM indScript INTO @recreateScript   END   DROP PARTITION SCHEME AuditPScheme DROP PARTITION FUNCTION AuditPFN   CLOSE indScript   DEALLOCATE indScript DROP TABLE #indexesScript END