Writing SQL query to read data from CRM 2011

Question

I am very aware that updating and creating records using SQL in CRM is a very bad idea, but I am wondering if it is possible to do read only sql queries to the database.

What I want to do is to create a small program that I can point at a CRM2011 server and entity in this database and generate an excel document with all the fields display and database names, as well as pick list options and their id's.

Examples of such queries can be found here:

• Where does Microsoft Dynamics CRM store OptionSet values in SQL Server?
• http://petecrm2011.wordpress.com/tag/sql/
• http://www.mohamedibrahim.net/blog/2010/02/24/return-and-display-picklist-value-display-value-in-custom-sql-report-reporting-services-report-in-microsoft-dynamics-crm-4-entity-attribute-and-stringmap-picklist-values-table/

I know how to do this by connecting directly to the SQL server, but I would prefer if I can do this using the address of the CRM server, i.e. not needing to go through SQL security but simply needing an admin account to CRM.

Hoping this isn't a horrible idea as it would make my life as a developer a little easier.

Answer 1

As you already know write directly to the CRM database is not supported, but you can read it in a supported way using the filtered views, more information here:

Use SQL and Filtered Views to Retrieve Data for Reports

if your goal is to generate an excel file of the CRM structure, you can use a tool like Metadata Document Generator included in XrmToolBox

http://xrmtoolbox.codeplex.com/

Answer 2

Have a look here. Connecting to organization service and using classes form Microsoft.Xrm.Sdk.Metadata.Query namespace should give you all data that you need. Extended example is here.