Archive

Archive for the ‘SQL’ Category

CRM data load – Finding long running queries

December 17, 2012 Leave a comment

The other day when we were doing performance testing on our CRM application by importing huge data, we got few “Generic SQL Errors”.

One common reason for the “Generic SQL Errors” is either SQL timeout because of long running queries or dead locks.

So to find the root cause of the issue we wanted to find the long running query’s.

Here is the useful article which will give you the “Longest running queries” on your SQL Server.

P.S. You might get “Division by 0 errors” error when you run the query. Keep trying the query you will get results for sure.

🙂

Getting logged in user’s GUID in CRM reports

We can get the logged in user GUID in the custom reports deployed in CRM using “fn_FindUserGuid()”  function

Below is the sample query for the same

DECLARE @currUserId UNIQUEIDENTIFIER

SELECT @currUserId=dbo.fn_FindUserGuid()

PRINT @currUserId

🙂

Database ‘Org_MSCRM’ cannot be started in this edition of SQL Server because it contains a partition function ‘AuditPFN’ – Error while restoring organization

June 16, 2012 1 comment

Hi,

I got below error, when I was trying to restore an organization database from SQL server enterprise edition to SQL server developer / Data center editions

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.

Reason :-

  • When CRM 2011 is installed using a SQL Server Enterprise edition, a partition is automaticallycreated for the auditing functionality of CRM 2011.
  • The AuditBase table then uses partitioning (which is only available for SQL Server Enterprise).

Fix :-

  • Either upgrade current SQL version to Enterprise
  • OR
  • We have to run the script to recreate all the indexes on the Primary partition and then drops the partition
  • Refer below MSDN KB article

http://support.microsoft.com/kb/2567984

Below is the useful article on the same

Link

🙂

Intellisense feature in Sql server management studio 2008 R2 stopped working – Fix

Hi,

You might have lost the Intellisense in ‘Sql server management studio 2008 R2’ after you installed Visual studio 2010 Sp1.

This is a known issue and it got fixed after I installed “Service Pack 1” of Sql server from below link

Download

When you click the above link, from the page download & install “SQLManagementStudio_x64_ENU.exe” and restart the machine

SSMS SP1

SSMS SP1

There might be other reasons for this and it explained in-detail in below article

Link

Hope it helps 🙂

Case statement in Select clause

September 13, 2011 Leave a comment

Hi,

Below is a sample query that has “CASE statement”  in SELECT clause

SELECT EmpID,

CASE
WHEN Salary>=2000 THEN ‘High Salaried’
WHEN Salary<=1000 THEN ‘Medium’
ELSE ‘Below Bar’
END

‘New Column’, *

FROM

Employee

Below is the screenshot for reference

CASE in SELECT Clause

CASE in SELECT Clause

Hope it helps 🙂