Microsoft Dynamics CRM (Archived)

Convert Fetch XML to SQL Query

(1) Share

Report

Posted on by Abdul Wahab

12,119 Moderator

Hi all

Below is my Fetch XML

and below is my converted SQL query

SELECT DISTINCT contact.telephone1
, contact.contactid
, contact.emailaddress1
, contact.lastname
, contact.jobtitle
, contact.firstname
, contact.ParentCustomerIdName
FROM contact
INNER JOIN account AS bd
ON contact.parentcustomerid = bd.accountid
INNER JOIN opportunity AS be
ON contact.accountid = be.customerid
WHERE
bd.aw_segmentid in ('{12345678-1234-5678-1234-123456789123}','{12345678-1234-5678-1234-123456789124}') AND Contact.EMailAddress1 is not null OR
be.aw_payment !='{12345678-1234-5678-1234-123456789125}'
ORDER BY contact.firstname

The results of both are not matching. There is some problem, I know. Please mention my mistake on this query.

Thank You

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Nithya Gopinath 17,078 on at

Like (0)

Report

Hi Abdul Wahab,

There is a tool to convert MSCRM FetchXML to SQL query. Please see the link below.

fetchxml2sql.codeplex.com

Also, refer the following community thread.

community.dynamics.com/.../151662

Hope this helps you.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Please try this SQL:

SELECT DISTINCT contact.telephone1

, contact.contactid

, contact.emailaddress1

, contact.lastname

, contact.jobtitle

, contact.firstname

, contact.ParentCustomerIdName

FROM contact

INNER JOIN account AS bd

ON contact.parentcustomerid = bd.accountid AND

bd.aw_segmentid in ('{12345678-1234-5678-1234-123456789123}','{12345678-1234-5678-1234-123456789124}')

INNER JOIN opportunity AS be

ON contact.accountid = be.customerid AND

be.aw_payment !='{12345678-1234-5678-1234-123456789125}'

WHERE

Contact.EMailAddress1 is not null

ORDER BY contact.firstname

Was this reply helpful? Yes No
Abdul Wahab 12,119 Moderator on at

Like (0)

Report

Hi Nithya Gopinath

I used this fetchxml2sql but same inaccurate results

Thank You

Was this reply helpful? Yes No
Abdul Wahab 12,119 Moderator on at

Like (0)

Report

Hi Mohd Saad Akhtar

I have 5000 + records in crm but when I run in sql it gives only 42.

Where am I wrong ? Please do let me know.

Thank YOu

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

deleted

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Are you running it on correct SQL instance. To verify it run a simple sql query to find number of records on contacts.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Try this qury once more. If it is not correct then try to build query by using two tables first then join the third table:

SELECT DISTINCT contact.telephone1

, contact.contactid

, contact.emailaddress1

, contact.lastname

, contact.jobtitle

, contact.firstname

, contact.ParentCustomerIdName

FROM contact

Left JOIN account AS bd

ON contact.parentcustomerid = bd.accountid AND

bd.aw_segmentid in ('{12345678-1234-5678-1234-123456789123}','{12345678-1234-5678-1234-123456789124}')

Left JOIN opportunity AS be

ON bd.accountid = be.customerid AND

be.aw_payment !='{12345678-1234-5678-1234-123456789125}'

WHERE

Contact.EMailAddress1 is not null

ORDER BY contact.firstname

Was this reply helpful? Yes No
Suggested answer

Mahadeo Matre 17,021 on at

Like (0)

Report

Hi,

How your are executing FetchXML and SQL queries?

If you are doing advanced find for FetchXML, then might be result is different than SQL query.

When you are executing advanced find, user security applied to result and result is filtered based on user permission.

When Executing SQL query with Filtered views, there is no security applied on result.

You can try adding filtered view in sql query and execute FetchXML and SQL query with same user permission.

Hope this will help..

Was this reply helpful? Yes No
Suggested answer

Mahadeo Matre 17,021 on at

Like (0)

Report

try this query

SELECT DISTINCT contact.telephone1

, contact.contactid

, contact.emailaddress1

, contact.lastname

, contact.jobtitle

, contact.firstname

, contact.parentcustomerid

FROM contact INNER JOIN account AS bd ON

contact.parentcustomerid = bd.accountid

INNER JOIN opportunity AS be ON

contact.accountid = be.customerid

WHERE ((

contact.emailaddress1 is not null)) AND (((

bd.aw_segmentid in ({12345678-1234-5678-1234-123456789123},{12345678-1234-5678-1234-123456789124})))) AND (((

be.aw_payment !='{12345678-1234-5678-1234-123456789125}')))

ORDER BY contact.firstname ASC

Was this reply helpful? Yes No