Connect AI Salesforce

CData Connect AI — Salesforce Skill

⚠ Prerequisites — load these first

• connect-ai-base skill

Before proceeding, locate and read the connect-ai-base skill. If it is not available in the current environment (not loaded, not enabled, or not installed), stop immediately. Do not proceed with the task. Tell the user that the connect-ai-base skill is required and ask them to install and enable it before continuing.

This skill provides Salesforce-specific guidance for querying Salesforce data through CData Connect AI. It composes on top of the connect-ai-base skill, which handles the discovery workflow, error recovery, SQL dialect, and query naming convention.

Precedence

This skill replaces getInstructions for the Salesforce driver. Do not call getInstructions for Salesforce — the guidance it provides is already incorporated here. Proceed directly to schema discovery (getTables / getColumns) after identifying the Salesforce connection via getCatalogs.

Schema

Salesforce is a single-schema driver. The schema name is Salesforce.

SELECT * FROM [YourConnection].[Salesforce].[Account] LIMIT 5

Query Process

1. Identify the connection — if unknown, call getCatalogs and filter for DRIVER = 'Salesforce'.
2. Locate the object — use getTables to find the right Salesforce object. For custom objects, use tableName: "%__c".
3. Inspect columns — call getColumns before writing any query. For picklist fields, query PickListValues to get valid values (see Conventions).
4. Sample first — run a SELECT * … LIMIT 5 to validate structure and values before building complex queries.
5. Filter on date columns — Salesforce queries over large objects will time out without date filters; always add a CreatedDate or LastModifiedDate predicate when the result set could be large.
6. Add JOINs and aggregations incrementally — validate each join step before adding the next.

Fallback when getTables or getProcedures are unavailable

If getTables or getProcedures are not available (disabled at the connection level or not exposed by the current tool surface), query the system catalog tables directly via queryData:

-- List all tables
SELECT [TableName], [TableType] FROM [YourConnection].[Salesforce].[sys_tables] ORDER BY [TableName]

-- List all stored procedures
SELECT [ProcedureName] FROM [YourConnection].[Salesforce].[sys_procedures] ORDER BY [ProcedureName]

-- List custom objects only
SELECT [TableName] FROM [YourConnection].[Salesforce].[sys_tables] WHERE [TableName] LIKE '%__c' ORDER BY [TableName]

These system tables are always available via queryData regardless of which discovery tools are enabled.

Data Model

Key Tables

• Account — Organizations and companies; the central entity most other objects relate to.
• Contact — Individual people, linked to Accounts via AccountId.
• Lead — Unqualified prospects; separate from Contacts until converted via ConvertLead.
• Opportunity — Sales deals and pipeline; linked to Accounts and optionally Contacts.
• Case — Customer service and support tickets; linked to Accounts and Contacts.
• Campaign — Marketing campaigns; tracks members, responses, and revenue attribution.
• PickListValues — Virtual table exposing all picklist field values for any Salesforce object. Filter by TableName and optionally ColumnName.

Custom Objects and Fields

Salesforce orgs frequently contain custom objects and fields using the __c suffix:

• Find custom objects: call getTables with tableName: "%__c"
• Find custom fields on a table: call getColumns on any table and look for column names ending in __c

Key Relationships

Important Columns

Account

Contact

Opportunity

Lead

Case

Campaign

PickListValues

Common Query Patterns

Open opportunities for a specific account

SELECT [Name], [StageName], [Amount], [CloseDate], [Probability]
FROM [YourConnection].[Salesforce].[Opportunity]
WHERE [AccountId] IN (
  SELECT [Id]
  FROM [YourConnection].[Salesforce].[Account]
  WHERE [Name] LIKE '%Acme%'
)
AND [IsClosed] = 0
ORDER BY [CloseDate] ASC

Contacts with recent activity

SELECT c.[Name], c.[Email], c.[Title], a.[Name] AS AccountName, c.[LastActivityDate]
FROM [YourConnection].[Salesforce].[Contact] c
LEFT JOIN [YourConnection].[Salesforce].[Account] a ON c.[AccountId] = a.[Id]
WHERE c.[LastActivityDate] >= '2024-01-01'
ORDER BY c.[LastActivityDate] DESC
LIMIT 20

Pipeline summary by stage

SELECT [StageName], COUNT(*) AS OpportunityCount, SUM([Amount]) AS TotalAmount
FROM [YourConnection].[Salesforce].[Opportunity]
WHERE [IsClosed] = 0
AND [CloseDate] >= '2025-01-01'
GROUP BY [StageName]
ORDER BY TotalAmount DESC

Unconverted leads by source

SELECT [FirstName], [LastName], [Company], [Email], [Status], [CreatedDate]
FROM [YourConnection].[Salesforce].[Lead]
WHERE [IsConverted] = 0
AND [LeadSource] = 'Web'
AND [CreatedDate] >= '2025-01-01'
ORDER BY [CreatedDate] DESC

Accounts without any open opportunities (NOT EXISTS pattern)

SELECT [Id], [Name], [Industry], [LastActivityDate]
FROM [YourConnection].[Salesforce].[Account]
WHERE NOT EXISTS (
  SELECT 1 FROM [YourConnection].[Salesforce].[Opportunity] o
  WHERE o.[AccountId] = [Account].[Id] AND o.[IsClosed] = 0
)
AND [LastModifiedDate] >= '2025-01-01'

Look up valid picklist values for a field

SELECT [PickList_Value], [PickList_Label], [PickList_IsDefault]
FROM [YourConnection].[Salesforce].[PickListValues]
WHERE [TableName] = 'Opportunity'
AND [ColumnName] = 'StageName'
AND [PickList_IsActive] = true

Discover custom objects

Use getTables with a wildcard to list all custom objects in the org:

{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "tableName": "%__c"
}

Stored Procedures

ConvertLead

Converts a Lead into an Account, Contact, and optionally an Opportunity.

Required parameters: @LeadId, @ConvertedStatus. Before calling, retrieve valid converted status values:

SELECT [Id], [MasterLabel]
FROM [YourConnection].[Salesforce].[LeadStatus]
WHERE [IsConverted] = true

{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "ConvertLead",
  "parameters": {
    "@LeadId": "00Q000000000001",
    "@ConvertedStatus": "Qualified",
    "@DoNotCreateOpportunity": "false",
    "@OpportunityName": "Acme — New Deal"
  }
}

To merge into an existing Account and Contact instead of creating new ones, add @AccountId and @ContactId. Set @DoNotCreateOpportunity to "true" and omit @OpportunityName when no Opportunity is needed.

GetUpdated

Returns IDs of records updated within a UTC time window for a given object type. Seconds in timestamps are ignored by the Salesforce API.

{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "GetUpdated",
  "parameters": {
    "@ObjectType": "Account",
    "@StartDate": "2025-09-09T13:00:00.000Z",
    "@EndDate": "2025-09-12T13:00:00.000Z"
  }
}

GetDeleted

Returns IDs of records deleted within a UTC time window for a given object type. Same parameters as GetUpdated: @ObjectType, @StartDate, @EndDate.

Search

Executes a full-text search across Salesforce objects. Accepts either a plain search term or a SOSL query (not both).

{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "Search",
  "parameters": {
    "@SearchTerm": "Acme Corporation"
  }
}

For a targeted SOSL query:

{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "Search",
  "parameters": {
    "@SOSL": "FIND {Acme} IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, Name, Email)"
  }
}

Merge

Combines up to three records of the same object type into a single master record. The non-master records are deleted after the merge.

{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "Merge",
  "parameters": {
    "@ObjectType": "Contact",
    "@MasterRecordId": "003000000000001",
    "@RecordToMergeIds": "003000000000002,003000000000003"
  }
}

Undelete

Restores records from the Salesforce Recycle Bin. Use GetDeleted first to obtain the IDs of the records to restore.

GetRecordCount

Returns a cached record count for a Salesforce object. Results may lag real-time data — use COUNT(*) queries for accurate counts.

GetUserInformation

Returns personal details about the authenticated Salesforce user. Useful for confirming which user and org the active connection is running under. No parameters required.

GetLimitInfo

Returns API usage and limit details for the Salesforce org. Use before running large or bulk operations to verify sufficient API capacity remains.

DownloadAttachment

Downloads an attachment and returns its content as base64 in the FileData column — set @Encoding = 'BASE64' and omit @FileStream. Confirmed working in cloud Connect AI environments. (This driver has no @LocalPath / @FileLocation parameter.)

Key parameters — use exactly these names:

{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "DownloadAttachment",
  "parameters": {
    "@Id": "00P000000000001"
  }
}

Response includes FileData (base64-encoded content), FileName, Success, and FailureMessage.

File procedures — cloud compatibility

Toolkit Procedure Calls

When executing stored procedures via a toolkit surface (e.g. salesforce_db_execute_procedure) rather than the generic MCP executeProcedure tool, use the following call shape:

• EXEC keyword (not EXECUTE).
• Fully-qualified procedure name: [Catalog].[Schema].ProcedureName.
• Positional parameter names @p1, @p2, @p3, ... — each @pN maps to the parameter at ordinal position N (1-based) as returned by get_procedure_parameters. Named parameters (e.g. @ObjectType = 'Account') are also supported, but positional @pN is preferred for consistency across procedures.
• Inline values in the sql string. Parameter binding via a separate parameters object is not supported by the toolkit's execute_procedure tool.

You can supply any subset of parameters by ordinal — skipped and out-of-order ordinals are tolerated, so the call only needs to mention the parameters relevant to it.

GetUpdated

Parameter map: @p1 = ObjectType, @p2 = StartDate, @p3 = EndDate.

{
  "sql": "EXEC [YourConnection].[Salesforce].GetUpdated @p1 = 'Account', @p2 = '2026-05-01T00:00:00.000Z', @p3 = '2026-05-15T23:59:59.000Z'"
}

ConvertLead

Parameter map: @p1 = AccountId, @p2 = ContactId, @p3 = ConvertedStatus, @p4 = DoNotCreateOpportunity, @p5 = LeadId, @p6 = OpportunityName, @p7 = OverwriteLeadSource, @p8 = OwnerId, @p9 = SendNotificationEmail.

Required params are @p3 (ConvertedStatus) and @p5 (LeadId); the example below also sets @p4 (DoNotCreateOpportunity):

{
  "sql": "EXEC [YourConnection].[Salesforce].ConvertLead @p3 = 'Closed - Converted', @p5 = '00Q000000000001', @p4 = 'true'"
}

DownloadAttachment

Parameter map: @p1 = ObjectId, @p2 = Id, @p3 = Name, @p4 = LightningMode, @p5 = Encoding, @p6 = FileStream.

Download by attachment ID (@p2 = Id):

{
  "sql": "EXEC [YourConnection].[Salesforce].DownloadAttachment @p2 = '00P000000000001'"
}

DownloadContentDocument

Parameter map: @p1 = Id, @p2 = Title, @p3 = LocalPath, @p4 = Encoding, @p5 = FileStream.

{
  "sql": "EXEC [YourConnection].[Salesforce].DownloadContentDocument @p1 = '069000000000001'"
}

The generic MCP executeProcedure tool handles catalog resolution and parameter binding internally — this guidance applies only to toolkit execute_procedure calls.

Write Operations

INSERT example

INSERT INTO [YourConnection].[Salesforce].[Lead]
  ([FirstName], [LastName], [Company], [Email], [LeadSource])
VALUES
  ('Jane', 'Smith', 'Acme Corp', 'jane@acme.com', 'Web')

UPDATE example

UPDATE [YourConnection].[Salesforce].[Opportunity]
SET [StageName] = 'Closed Won', [Probability] = 100
WHERE [Id] = '006000000000001'

Write access is controlled by the CData Connect AI connection settings. If INSERT, UPDATE, or stored procedure calls return permission errors, guide the user to their Connect AI connection settings and confirm that write access is enabled — connections may be set to readonly mode by default.

Salesforce-Specific Conventions

• Leads are separate from Contacts — a Lead only becomes a Contact after conversion via ConvertLead. Do not join Lead and Contact as if they are the same entity; they live in separate tables with different schemas.
• Date filters prevent timeouts — always include a CreatedDate or LastModifiedDate predicate when querying large objects (Account, Contact, Lead, Opportunity). Use explicit date literals ('2025-01-01') instead of DATEADD() for best performance in Connect AI.
• NULL foreign keys are common — Salesforce allows many FK columns (e.g., AccountId on Contact) to be NULL. Use IS NOT NULL guards in WHERE clauses and prefer LEFT JOIN over INNER JOIN when joining related objects.
• Use NOT EXISTS over complex LEFT JOINs — for "find records without related records" queries, NOT EXISTS is more reliable than LEFT JOIN … WHERE key IS NULL.
• Verify picklist values before filtering — always query PickListValues to confirm valid values before filtering on picklist columns like StageName, Status, Priority, or LeadSource. The internal PickList_Value (used in WHERE clauses) may differ from the UI label in PickList_Label.
• Custom fields and objects use __c suffix — any column or table name ending in __c is a custom extension specific to that org. Use getTables with tableName: "%__c" to discover custom objects; use getColumns and look for __c columns to find custom fields on standard objects.
• Name is readonly on Contact and Lead — write FirstName and LastName separately; Name is a derived computed field and cannot be set directly.
• IsClosed and IsWon are readonly on Opportunity — they are controlled by Salesforce based on the StageName picklist value. Update StageName directly; do not attempt to set IsClosed or IsWon.
• CaseNumber is auto-generated and readonly — do not include it in INSERT statements.
• Verify COUNT before aggregating — run a COUNT(*) with your intended filters before running expensive aggregations to validate data volume and filter correctness.