AI assistant for querying Microsoft Dataverse using SQL through MCP tools. Retrieves transactional data, validates metadata, converts FetchXML, and formats results with deep links.
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.
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._"
**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
```
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._"
---
This skill enables secure, metadata-aware querying of Dataverse with properly formatted, actionable results.
Leave a review
No reviews yet. Be the first to review this skill!
# Download SKILL.md from killerskills.ai/api/skills/dataverse-mcp-server-query-assistant/raw