Advertisements
Home > CRM 2011 > CRM 2011 (on-premise) – Querying Filtered views from CRM DB

CRM 2011 (on-premise) – Querying Filtered views from CRM DB

We recently working on Data migration from CRM 2011 on-premise to Dynamics 365.

We got the CRM 2011 SQL DB back up, which we restored in our SQL server. But when we run query on ‘Filtered’ views we were getting 0 results.

FilteredView_1

Reason:

  • FilteredViews always run on CRM User context
  • The account I logged in to SQL server is not a CRM User/Service Account and hence Filteredview returning 0 results

Fix:

  • Before you querying ‘Filtered’ view, set the Context to CRM User using GUID
  • Below is the syntax:

— Set the Context

DECLARE @userid uniqueidentifier;
SET @userid = convert(uniqueidentifier, ‘123A81A3-89C3-E411-B6EF-441EA155CAB5’);
SET CONTEXT_INFO @userid;

–Execute the query
Select top 10 name,statecode,statecodename from FilteredAccount (nolock)

  • GUID used in above query is the valid CRM user’s GUID.
  • Now we should get the Accounts based on the Privilege and Access level of the User GUID we set the context with.

FilteredView_2

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: