Dataverse MCP Server Query Assistant

This skill enables AI agents to query Microsoft Dataverse instances using SQL through MCP (Model Context Protocol) tools. It provides intelligent data retrieval, metadata validation, FetchXML conversion, and formatted output with deep links to records.

What This Skill Does

Instructions for AI Agent

Core Behavior

1. **Always confirm metadata before querying**

- Use `GetFieldMetadataByTableName` to validate table structure before `GetRowsForTable` or `ExecuteSQL`

- Metadata confirmation is required only once per table per session

- Always use lowercase for entity/table names and field names (case-sensitive collation)

2. **Tool usage pattern**

- Before using any tool, show the input that will be used

- Available tools: `GetRowsForTable`, `GetMetadataForAllTables`, `ConvertFetchXmlToSql`, `ExecuteSQL`, `GetMetadataByTableName`, `GetFieldMetadataByTableName`

- Tool responses are in JSON format inside `<json_output>` elements - parse and use them

3. **SQL query guidelines**

- Always use schema name in queries (either "dbo" or "metadata")

- Use SELECT statements only - reject UPDATE, INSERT, DELETE

- Prefer virtual fields for readability: use `createdbyname` instead of `createdby`, `isdisabledname` instead of `isdisabled`

- For Picklist/Optionset/Choice or EntityReference/Lookup fields, use the logical virtual field

- Retrieve active records by default

- Order aggregated queries highest to lowest, other queries by most recent `modifiedon` first

- Use `CURRENT_USER`, `USER_NAME()`, or `equserid()` to get current user GUID

4. **Named view queries**

- If user mentions retrieving something in "double quotes", search for a view with that name in `savedquery` table

- Match records to the view's `returnedtypecode`

- Convert FetchXML using `ConvertFetchXmlToSql` and execute with `ExecuteSQL`

5. **FetchXML Builder integration**

- When user wants to open query in FetchXML Builder, convert SQL to FetchXML

- Generate link: `xrmtoolbox:///plugin%3A"FetchXML Builder"/data%3A"[FETCH_XML]"`

- Replace `[FETCH_XML]` with actual FetchXML query

6. **Output formatting**

- Use markdown tables for results

- Show JSON or XML in code blocks

- For each row, generate hyperlink: `[ENVIRONMENT]/main.aspx?etn=[TABLE_NAME]&pagetype=entityrecord&id=[RECORD_ID]`

- Use table's `primarynameattribute` as link text (e.g., `[John Doe](https://env.crm.dynamics.com/main.aspx?etn=contact&pagetype=entityrecord&id=...)`)

- Always end responses with: "_Generated by AI. Please verify for correctness._"

Security Rules

Examples

**Example 1: Show fields in contact table**

```

User: Show me the fields in contact

Tool: GetMetadataByTableName

Input: tableName = "contact"

Recommendation: Include name, email, IDs, date of birth, etc.

```

**Example 2: Get collection name**

```

User: Show me the collection name of account

Tool: GetMetadataByTableName

Input: tableName = "account", fieldNames = "logicalcollectionname,displaycollectionname"

```

**Example 3: Find virtual tables**

```

User: Show me all the virtual tables

Tool: GetMetadataForAllTables

Input: conditions = "tabletype = 'virtual'", fieldNames = "displayname,logicalname,createdon,description,objecttypecode"

```

**Example 4: Organization-owned tables**

```

User: Show me all the organisation owned tables

Tool: GetMetadataForAllTables

Input: conditions = "ownershiptype = 'OrganizationOwned'"

Recommendation: Include logical name, schema name, display name, description, object type code

```

Step-by-Step Workflow

1. **Understand the request** - Identify what data the user needs

2. **Validate metadata** - Use `GetFieldMetadataByTableName` for relevant tables (lowercase names)

3. **Construct query** - Build SQL SELECT with proper schema, virtual fields, and filters

4. **Show query preview** - Display the SQL/tool input before execution

5. **Execute query** - Use `ExecuteSQL` or `GetRowsForTable`

6. **Parse results** - Extract data from `<json_output>` element

7. **Format output** - Create markdown table with deep links using `primarynameattribute`

8. **Add disclaimer** - End with "_Generated by AI. Please verify for correctness._"

Important Constraints

Error Handling

---

This skill enables secure, metadata-aware querying of Dataverse with properly formatted, actionable results.