Connecting to Salesforce Marketing Cloud

For information on the available WHL files for supported environments, and how to install the appropriate file for your Python distribution, see Package Installation.

For information on the module to import, and how to configure the necessary connection properties in a connection string, see Establishing a Connection.

Other available connection properties can be used to configure other aspects of the connector capabilities.

Python Version Support

The CData Python Connector for Salesforce Marketing Cloud can be installed and used in Python 3.10 or newer.

Salesforce Marketing Cloud Version Support

The connector leverages the SOAP Web Service API and the REST API to enable bidirectional access to Salesforce Marketing Cloud.

See Also

• Using the Connector: Establish connections and query Salesforce Marketing Cloud through Python code.
• From SQLAlchemy: Use SQLAlchemy to establish a connection with dialect URL, and interact with Salesforce Marketing Cloud data using mapped classes and Sessions.

Dependencies

The Python connectors require that Python 3.10 or newer be installed.

Installation

The CData Python Connector for Salesforce Marketing Cloud is available as a WHL file for Windows, Linux, and Mac. Each connector is built using the Python 3.10 Stable ABI (indicated by the abi3 tag in the filename), so a single wheel supports any Python 3.10 or newer installation — there is no need to match your exact Python minor version. Use the "pip install" command with the appropriate WHL file for your platform.

Windows:

pip install cdata_sfmarketingcloud_connector-26.0.9655-cp310-abi3-win_amd64.whl

Linux:

pip install cdata_sfmarketingcloud_connector-26.0.9655-cp310-abi3-linux_x86_64.whl

macOS:

pip install cdata_sfmarketingcloud_connector-26.0.9655-cp310-abi3-macosx_12_0_arm64.whl

The macOS wheel supports arm64 (Apple Silicon) architectures only on macOS 12 and newer.

Regardless of the environment, certain distributions might require that the "pip3 install" command be used instead, to differentiate from a Python 2 distribution that might exist already. After installation, confirm whether the connector is successfully installed by running the "pip list" command. If "cdata_sfmarketingcloud_connector" is present in the list output by the command, then the installation was successful.

Upgrading

When upgrading, "pip install" does not automatically clean up old JRE files. To avoid leftover files that could cause JVM errors, uninstall the previous version before installing the new one.

Licensing

After the installation is complete, a separate step is needed to activate a license for the connector. Among the CData assets in the distribution's site packages, there is an install-license tool that activates this license. From within the distribution's site-packages folder, after navigating to the "cdata/installlic_sfmarketingcloud" folder, simply use a command like the below to activate the license. Omitting the <key> argument activates a trial license:

• Windows:
  

  ./install-license.exe <key>

• Linux / Mac:
  

  ./install-license.sh <key>

Sometimes, file access issues may cause pip to install the connector in a fallback file path that is not the python distribution's main or primary site-packages location. This can make it difficult to find where the connector was installed, and from there, the license activator. In that event, this python script below will print out the full file path of the connector's native file. This file will be stored in the mentioned cdata folder, from which the installlic_sfmarketingcloud folder is trivial to find:

import os
import cdata.sfmarketingcloud
path = os.path.abspath(cdata.sfmarketingcloud.__file__)
print(path)

Uninstallation

If the connector needs to be uninstalled for any reason, do so by running the pip uninstall command, as in the example below:

pip uninstall cdata-sfmarketingcloud-connector

The objects available within our connector are accessible from the "cdata.sfmarketingcloud" module. To use the module's objects directly:

1. Import the module as follows:
   

   import cdata.sfmarketingcloud as mod

2. To establish a connection string, call the connect() method from the connector object using an appropriate connection string, such as:
   

   mod.connect("User=myUser;Password=myPassword;")

Connecting to Salesforce Marketing Cloud

For both REST and SOAP APIs, you have the option to refine data access using the following properties:

• Instance: The instance of the Salesforce Marketing Cloud API used. The default Instance is s7 of the Web Services API; however, you can use this property to specify a different instance.
• Subdomain: If the instance is greater than s10, you must also specify the subdomain.

REST API

To connect, set Schema to REST.

The Salesforce Marketing Cloud REST API uses the OAuth authentication standard. To authenticate using OAuth, you must create a custom OAuth application to obtain values for the OAuthClientId and OAuthClientSecret connection properties. See Creating a Custom OAuth App for more information.

SOAP API

To connect, set Schema to SOAP.

The Salesforce Marketing Cloud SOAP API can connect using OAuth, but also supports a legacy use of login credentials.

Note: Data extension objects in Salesforce Marketing Cloud are only accessible through the SOAP API.

Authenticating to Salesforce Marketing Cloud

User Accounts (OAuth)

Set the AuthScheme to OAUTH. Also, in all OAuth flows, set AccountId to the specific MID of the target business unit. NOTE: This is not available for legacy packages. The following OAuth sections assume that you have set both these connection properties.

Desktop Apps

Follow the steps below to authenticate with the credentials for a custom OAuth application. See Creating a Custom OAuth App for information about custom OAuth applications. Get an OAuth Access Token

After setting the following, you are ready to connect:

• OAuthClientId: The Client Id in your application settings.
• OAuthClientSecret: The Client Secret in your application settings.
• CallbackURL: The Redirect URL in your application settings.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values in OAuthSettingsLocation. These values persist across connections.

Web Apps

When connecting via a Web application, you need to register a custom OAuth application with Salesforce Marketing Cloud. See Creating a Custom OAuth App for information about custom OAuth applications. Then use the connector to get and manage the OAuth token values. Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: The Client Id in your application settings.
• OAuthClientSecret: The Client Secret in your application settings.

You can then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the AuthMode input to WEB and set the CallbackURL input to the Redirect URI you specified in your application settings. If necessary, set the Permissions parameter to request custom permissions.

   The stored procedure returns the URL to the OAuth endpoint.

2. Open the URL, log in, and authorize the application. You are redirected back to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL. If necessary, set the Permissions parameter to request custom permissions.

To connect to data, set the OAuthAccessToken connection property to the access token returned by the stored procedure. When the access token expires after ExpiresIn seconds, call GetOAuthAccessToken again to obtain a new access token.

Headless Machines

To configure the driver to use OAuth with a user account on a headless machine, you need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an Internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow, as described in "Transfer OAuth Settings" below.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an Internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application click Salesforce Marketing Cloud OAuth endpoint to open the endpoint in your browser.
   • If you are using a custom OAuth application, create the Authorization URL by setting the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the rpgrestsp-getoauthauthorizationurl stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens. Set the following properties:

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: REFRESH.
• OAuthVerifier: The verifier code.
• OAuthClientId: (custom applications only) The Client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) The Client Secret in the custom OAuth application settings.
• OAuthSettingsLocation: The location of the settings file where OAuth values are saved when you set InitiateOAuth to GETANDREFRESH or REFRESH. Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'. When this connection property is set, the data persists across connections.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

• InitiateOAuth: REFRESH.
• OAuthClientId: (custom applications only) The client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) The client secret assigned when you registered your application.
• OAuthSettingsLocation: The location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an Internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties:

• OAuthClientId: (custom applications only) The client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) The client secret assigned when you registered your application.
• OAuthSettingsLocation: The location of your OAuth settings file. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Server-to-Server (OAuthClient)

When creating an application in Salesforce Marketing Cloud, you can select server-to-server authentication. In this case, the application's permissions are configured directly in the Salesforce Marketing Cloud UI. As such, there is no user context and hence no browser-based login or permission grants. For this scheme, you must create your own credentials.

Specify the following properties to enable server-to-server OAuth authentication for your application:

• AuthScheme: OAuthClient.
• InitiateOAuth: GETANDREFRESH.
• OAuthClientId: The client Id.
• OAuthClientSecret: The client secret.
• Subdomain: Specify the Salesforce Marketing Cloud API subdomain.

User/Password Accounts (Basic)

The Salesforce Marketing Cloud SOAP API can connect using either your login credentials or OAuth authentication. Note that this authentication scheme is not available for REST API-based applications.

To connect to data using login credentials authentication, set the following:

• AuthScheme: Basic.
• User: The Salesforce Marketing Cloud user account.
• Password: The password used to authenticate the user.
• UseLegacyAuthentication: True.

Java Native Interface (JNI) is a standard programming interface for writing Java native methods and embedding the Java virtual machine into native applications.

The connector leverages the JNI for improved performance on Mac and Linux.

Configure the Config INI File

The Linux and Mac editions of the Salesforce Marketing Cloud python connector are configured with an ini file. This file is used to set several parameters, including JNI behavior. This file is to be located in:

{path_to_distribution_site-packages}/cdata/config.ini

Ensure that any configuration properties you set in the ini file fall under the following section name (adjust the 311 number if you are using an different python version from 3.11):

• For Linux:
  

  [sfmarketingcloud.cpython-311-x86_64-linux-gnu.so]

• For Mac:
  

  [sfmarketingcloud.cpython-311-darwin.so]

Configure the JNI connector's behavior by editing the properties in the connector's config.ini file. The connector can be configured as follows:

• LOGFILE: Set this the same way as the CDATA_LOGFILE envrionment variable below.
• JAVA_HOME: Configure the path to the JVM library location used to launch the JVM.
• CLASS_PATH: Use a colon-separated list to configure the paths to the third-party jar libraries.

Configure Environment Variables

Additionally, set the following environment variables:

• CDATA_JAVA_HOME: Configure the path to the JVM library location used to launch the JVM.
• CDATA_JVM_OPTIONS: Place JVM options here.
• CDATA_LOGFILE: Set this in the following scheme: <SCHEME>://<TAG>[|<LEVEL>]
  • SCHEME: The options are STDOUT, FILE.
    • STDOUT: Both the native wrapper and odbc core log into stdout. The Logfile and Verbosity properties can override the behavior of ODBC core.
    • FILE: The native wrapper logs into <FILENAME> while the odbc core logs into <FILENAME>.driver.log. The Logfile and Verbosity properties can override the behavior of ODBC core.
  • TAG
    • For STDOUT, set this to 1. For FILE, set this to the filename.
  • LEVEL
    • Set to one of: FATAL | ERROR | WARNING | INFO | DEBUG

The following are some examples of this syntax:

• STDOUT://1|DEBUG
• FILE:///tmp/my_py.log|DEBUG

Custom Logger

The Python connector supports a custom logging mechanism for redirecting log output to any destination, such as a cloud storage service or logging framework. Use setCustomLoggerFactory() to register a factory function that creates a logger instance for each connection.

The factory function receives the context string from the Logfile connection property (the portion after CUSTOM://) and must return an object with a writeLog(verbosity, message) method.

To enable custom logging:

1. Call setCustomLoggerFactory() with your factory function before opening connections.
2. Set Logfile to CUSTOM:// followed by a context string to identify the connection.
3. Set Verbosity to the desired log level.

The following example demonstrates a custom logger factory that creates a separate logger instance per connection:

import cdata.sfmarketingcloud as mod
import time

class MyLogger:
    def __init__(self, loggerId):
        self.loggerId = loggerId
    def writeLog(self, verbosity, message):
        print("[MyLogger " + self.loggerId + "] " + message)

def createLogger(context):
    return MyLogger(context[len("MyLoggerId="):])

mod.setCustomLoggerFactory(createLogger)

conn1 = mod.connect("...;Logfile=CUSTOM://MyLoggerId=1;Verbosity=2;")
# do something with conn1
time.sleep(1)  # Wait for logs to flush from conn1

conn2 = mod.connect("...;Logfile=CUSTOM://MyLoggerId=2;Verbosity=2;")
# do something with conn2
time.sleep(1)  # Wait for logs to flush from conn2

Create an App

Follow the procedure below to create and register an application and obtain the OAuth client credentials, the client Id, and client secret:

1. Log in to your Salesforce Marketing Cloud and navigate to Marketing Cloud | Administration | Installed Packages.
2. Click New.
3. Specify a package name and description.
4. Save the package. The saved package contains important data; see "Installed Packages Definitions" for more information about each field. Note that you see the Package ID, JWT Signing Secret, and Source Account only for packages created in your account.
5. Under Components, click Add Component.
6. Select API Integration.
7. You must select Server-to-Server or Web App as the integration type if the package supports enhanced functionality.
8. Assign the appropriate scope for your integration.
   • Perform server-to-server requests... is automatically selected for all API Integrations.
   • Select Perform requests on behalf of the user... if this package contains a Marketing Cloud application.
   • Select the Marketing Cloud scope for your API calls. Assign only the scope your package needs.
9. Save the component.
10. The Client ID and Client Secret are located under the component details.

The connector offers the possibility to select, insert, update, and delete data from data extensions as relational tables. To query a data extension, simple enter its name in the format DataExtensionObject_Name where Name is the name of your data extension.

Note: To connect to data extension objects in Salesforce Marketing Cloud, you must use the SOAP API. Set Schema to SOAP.

Selecting data from data extensions

SELECT * FROM DataExtensionObject_fsefes3

SELECT * FROM DataExtensionObject_fsefes3 WHERE FieldName1 = 'One'

Note 1: All filters which work with normal tables/views also work with data extensions.

Note 2: By default the Salesforce Marketing Cloud retrieves data for the LoggedIn ClientId. To retrieve results for more than one ClientID, use semi-colons (;) as a separator. The ClientID can be accounts and sub-accounts, including Enterprise 2.0, On-Your-Behalf, and Lock & Publish accounts.

SELECT * FROM DataExtensionObject_fsefes3 WHERE ClientID = '1234567'

Inserting data into data extensions

INSERT INTO DataExtensionObject_fsefes3 (FieldName1, FieldName2, FieldName3) VALUES ('One', 'Two', 'Three')

Updating data from data extensions

UPDATE DataExtensionObject_secondtest15 SET [Name] = 'ChangedFromTheApi' WHERE [Email Address] = 'update_me@gmail.com'

Note: A primary key has to exist for this data extension for the update to work.

Deleting data from data extensions

DELETE FROM DataExtensionObject_secondtest15 WHERE [Email Address] = 'delete_me@gmail.com'

Note: A primary key has to exist for this data extension for the deletion to work.

General Changes

Date	Version	Source	Category	Type	Description
2026-05-28	26.0.9644	Salesforce Marketing Cloud	Data Model	Removed	Removed the DeliveryProfile_CusomterKey column from the EmailSendDefinition table in the SOAP schema.
2026-05-27	26.0.9643	General	Connection	Removed	Removed the deprecated ReplaceInvalidTypesWithNull connection property. Use the ReplaceInvalidValuesWithNull property instead.
2026-05-27	26.0.9643	Salesforce Marketing Cloud	Connection	Removed	Removed the deprecated Instance connection property, which was previously used for legacy stack-specific endpoints that have been unsupported for a long time.
2026-05-22	26.0.9638	Python		Removed	Remove support for Intel x64 architecture on macOS
2026-05-07	26.0.9623	General	Data Model	Added	Added the ColumnCapabilities column to the sys_tablecolumns system table. This column is a bit mask denoting the column's write capabilities.
2026-05-07	26.0.9623	Python		Changed	Updated embedded JRE to jre-17.0.19+10 (Linux x64 / MacOs x64).
2026-04-15	26.0.9601	General	Query Exec	Changed	String comparisons using GREATER, LESS, and CONTAINS operators are now case-insensitive by default.
2026-04-08	26.0.9594	Salesforce Marketing Cloud	Security	Changed	TLS 1.3 is now supported by default for HTTP connections.
2026-01-13	25.0.9509	General		Added	Added support for the REGEXP_REPLACE() string function.
2025-12-21	25.0.9486	Python		Added	Added support for custom loggers in Python connectors on Linux and macOS.
2025-12-05	25.0.9470	General		Added	Added support for the INSERT INTO SELECT statement, with driver-side execution for providers that do not support the operation natively.
2025-10-31	25.0.9435	Salesforce Marketing Cloud		Changed	The column size has been changed from 1 to 2000 for the Type column of the List table and List_Type column of the UnsubEvent table in the SOAP schema.
2025-10-30	25.0.9434	Python		Changed	Updated embedded JRE to jre-17.0.17+10 (Linux x64 / MacOs x64).
2025-10-06	25.0.9410	General		Added	Support for parsing datetime formats using ".S" and ",S" for milliseconds and nanoseconds.
2025-10-02	25.0.9406	Salesforce Marketing Cloud		Added	Added the MobileApplications view in the REST schema.
2025-09-12	25.0.9386	General		Added	Added the IsInsertable, IsUpdateable, and IsDeleteable columns to the sys_tables table.
2025-09-10	25.0.9384	General		Changed	All columns in statically defined Views are now reported as read-only.
2025-09-03	25.0.9377	General		Changed	Corrected the behavior when IN criteria with NULL values are used in the projection part. It now returns NULL instead of 0. For example, "NULL IN (1,2)" returns "NULL".
2025-09-01	25.0.9375	General		Added	Added support for using the CAST function with infinity values. This function can cast "inf" and "-inf" to DOUBLE, FLOAT, or REAL.
2025-08-29	25.0.9372	Salesforce Marketing Cloud		Changed	Updated the data type of the WorkFlowApiVersion column to Double for the Journeys table in the REST schema.
2025-08-21	25.0.9364	General		Changed	Report behavior change: Fixed inconsistent string value comparisons in non-table queries. For example, "SELECT 'A' = 'a'" previously returned false, but it now returns true.
2025-08-13	25.0.9356	General		Changed	Changed the maximum number of pages held in memory from 15 to 5 for the page providers to decrease heap usage.
2025-08-04	25.0.9347	Salesforce Marketing Cloud		Changed	Changed the ID, ObjectID, and Modified fields in all tables and views that expose them in the SOAP schema. They can no longer be updated. All columns exposed in views are now readonly and DB-generated.
2025-08-04	25.0.9347	Salesforce Marketing Cloud		Added	Added the IsInsertRequired metadata column.
2025-07-18	25.0.9330	Salesforce Marketing Cloud		Removed	Removed the OAuthGrantType property. The grant type is now set implicitly through the 'AuthScheme' property. For example, you can use the 'OAuthPassword' AuthScheme instead of AuthScheme=OAuth with OAuthGrantType=Password.
2025-07-07	25.0.9319	Python		Removed	Removed the 32-bit version of Windows Python.
2025-07-02	25.0.9314	Python		Removed	Removed support for Python 3.9.
2025-06-25	25.0.9307	General		Removed	Removed the "ADLS Gen 1" value from the ConnectionType property.
2025-06-25	25.0.9307	Python		Added	Added support for Python 3.13 in Windows, Linux, and Mac editions.
2025-06-25	25.0.9307	Python		Removed	Removed support for Python 3.8 as it is no longer supported.
2025-06-20	25.0.9302	General		Added	Created the following functions: TEXT_ENCODE: encodes a string into a different charset (UTF8 → UTF7 and returns a binary array as the result). TEXT_DECODE: takes a binary array and decodes it back into a string when provided the charset. BASE64_ENCODE: takes a binary array and encodes it as a base64 string (varchar). BASE64_DECODE: takes a base 64-encoded string and decodes it into a binary array.
2025-06-18	25.0.9300	General		Changed	The internal code for exception handling has been refactored. Exception messages returned during certain error conditions may now have different wording or formatting.
2025-05-27	25.0.9278	General		Removed	Removed the "Proprietary" enum option from ProxyAuthscheme.
2025-05-12	25.0.9263	Python		Changed	Updated embedded JRE to jre-17.0.15+6 (Linux x64 / MacOS x64) and jre-17.0.15+6 (MacOS aarch64).
2025-04-01	25.0.9222	Salesforce Marketing Cloud		Changed	Changed the Client_Id column to be pushed as the primary key for the following tables and views: Account, Automation, BounceEvent, BusinessUnit, ClickEvent, ContentArea, DataExtension, DataExtensionField, DataExtensionTemplate, DataFolder, Email, EmailSendDefinition, FileTrigger, FileTriggerTypeLastPull, FilterDefinition, ForwardedEmailEvent, ForwardedEmailOptIn, Event, ImportDefinition, ImportResultsSummary, LinkSend, List, ListSend, ListSubscriber, MessagingVendorKind, NotSentEvent, OpenEvent, Portfolio, PrivateIP, ProgramManifestTemplatePublication, PublicKeyManagement, QueryDefinition, ReplyMailManagementConfiguration, Role, Send, SendClassification, SendEmailMOKeyword, SenderProfile, SendSMSMOKeyword, SendSummary, SentEvent, SMSMTEvent, SMSTriggeredSend, SMSTriggeredSendDefinition, SubscriberList, SubscriberSendResult, SubscriberStatusEvent, SuppressionListContext, SuppressionListDefinition, SurveyEvent, Template, TimeZone, TriggeredSendDefinition, TriggeredSendSummary, and UnsubEvent. Changed the SendID column to be pushed as the primary key for the following views: ListSend, NotSentEvent, and SentEvent. Changed the ID column to be pushed as the primary key for the following views: SendSummary, SubscriberList, and UnsubEvent. Changed ObjectID column to be pushed as the primary key for the following views: BounceEvent, ClickEvent, ForwardedEmailEvent, ForwardedEmailOptInEvent, and OpenEvent.
2025-02-18	24.0.9180	Salesforce Marketing Cloud		Added	Added the DefinitionId column to the Journeys table in the REST schema.
2025-02-15	24.0.9177	General		Added	Added support for converting unsigned integer types to the nearest signed data type that has enough precision to hold the unsigned value.This is done for JDBC only because it does not have support for unsigned data types.
2024-12-05	24.0.9105	Salesforce Marketing Cloud		Changed	Changed the Id, SubcriberKey, and Client_Id columns to primary keys in the Subscriber table.
2024-11-27	24.0.9097	General		Added	Added ThreadId to LogModule output. Logfile lines now include the Thread ID associated with the action being performed.
2024-07-04	24.0.8951	Salesforce Marketing Cloud		Added	Added ContactId and ContactKey columns to all child tables of the Contacts view.
2024-07-04	24.0.8951	Salesforce Marketing Cloud		Changed	Renamed all child tables of the Contacts view to "AttributeSet_(tableName)".
2024-06-26	24.0.8943	Salesforce Marketing Cloud		Added	Added support for INSERT/DELETE queries to the CampaignAssets table.
2024-06-12	24.0.8929	Salesforce Marketing Cloud		Removed	Removed the auto AuthScheme option. Removed the APIIntegrationType and OAuthGrantType connection properties.
2024-06-12	24.0.8929	Salesforce Marketing Cloud		Deprecated	Deprecated the Instance connection property.
2024-06-05	24.0.8922	Python		Added	Added support for Python 3.12.
2024-05-14	24.0.8900	Salesforce Marketing Cloud		Removed	Removed ActivityBatchInstanceId column of JourneyHistory view in schema REST.
2024-05-10	24.0.8896	Salesforce Marketing Cloud		Added	Added CreateDataExtensionJob stored procedure in schema REST to create an asynchronous job for inserting or upserting data into a Data Extension object. Added CheckDataExtensionJobStatus stored procedure in schema REST to get the status of the asynchronous insert or update job in a Data Extension object. Added GetDataExtensionJobResults stored procedure in schema REST to get the results of the asynchronous insert or update job in a Data Extension object.
2024-05-09	24.0.8895	General		Changed	The ROUND function previously did not accept negative precision values. That feature has now been restored.
2024-03-15	23.0.8840	General		Added	Created a new SQL function called STRING_COMPARE that provides java's String.compare() ability to SQL queries. Returns a number representative of the compared value of two strings
2023-11-29	23.0.8733	General		Changed	The ROUND function doesn't accept the negative precision values anymore.
2023-11-29	23.0.8733	General		Changed	The returning types of the FDMonth, FDQuarter, FDWeek, LDMonth, LDQuarter, LDWeek functions are changed from Timestamp to Date. The return type of the ABS function will be consistent with the parameter value type.
2023-11-28	23.0.8732	General		Added	Added the HMACSHA256 formatter to allow for secrets to be decoded if it is in base64 format
2023-08-29	23.0.8641	Python		Added	Added support for SQLAlchemy 2.0.
2023-08-09	23.0.8621	Salesforce Marketing Cloud		Changed	Removed primary key from JourneyHistory view as it is not unique
2023-06-20	23.0.8571	General		Added	Added the new sys_lastresultinfo system table.
2023-06-04	23.0.8555	Salesforce Marketing Cloud		Added	Added CreateTriggeredSend stored procedure for Rest schema.
2023-05-19	23.0.8539	Python		Added	Added support for Python 3.11 on Windows, Linux and Mac.
2023-05-16	23.0.8536	Python		Removed	Removed support for Python 3.7 on Windows and Linux
2023-04-25	23.0.8515	General		Removed	Removed support for the SELECT INTO CSV statement. The core code doesn't support it anymore.
2023-03-03	22.0.8462	Salesforce Marketing Cloud		Added	Added SendTransactionalMessageToMultipleRecipients and GetMessageSendStatus stored procedure. Added TransactionalMessages view.
2023-02-27	22.0.8458	Salesforce Marketing Cloud		Added	Added support for Synchronous and Asynchronous Batch Update.
2023-02-07	22.0.8438	Salesforce Marketing Cloud		Added	Added SendTransactionalMessageToRecipient stored procedure. Added SendDefinitions table.
2022-12-14	22.0.8383	General		Changed	Added the Default column to the sys_procedureparameters table.
2022-11-28	22.0.8367	Salesforce Marketing Cloud		Changed	Removed primary key from SubscriberList view as it is not unique.
2022-11-15	22.0.8354	Python		Changed	Updated embedded JRE to jre8u345-b01(Linux x64 / MacOS x64) and jre-17.0.5+8(MacOS aarch64).
2022-10-04	22.0.8312	Salesforce Marketing Cloud		Added	Added the FileStream input attribute to add inputstream in GetFileForAnAsset, GetChannelViewHtml and CreateSchema stored procedures. Added the Encoding input attribute to decide the response encode type in GetChannelViewHtml stored procedure. Added the FileData output attribute to print the BASE64 encoded response in Create schema stored procedure.
2022-09-30	22.0.8308	General		Changed	Added the IsPath column to the sys_procedureparameters table.
2022-09-13	22.0.8291	Salesforce Marketing Cloud		Added	Added JourneyHistory view.
2022-06-30	22.0.8216	Salesforce Marketing Cloud		Removed	Removed support for the legacy Stack-Specific Endpoints.
2022-06-14	22.0.8200	Salesforce Marketing Cloud		Added	Added support for Synchronous and Asynchronous Batch Insert. Added the UseAsyncBatch connection property to select the batch insert type. Added the WaitForBulkResults connection property, which defaults to true, to get Async Batch result. It only takes effect if UseAsyncBatch set to true.
2022-06-10	22.0.8196	Salesforce Marketing Cloud		Changed	Table columns will now use be using the underscore(_) onstead of of period(.) to distinguish between nested elements of the same entity.
2022-05-19	22.0.8174	Salesforce Marketing Cloud		Added	Added the OAuthClient (OAuth + Client Grant Type) value to AuthScheme property.
2022-05-19	22.0.8174	Salesforce Marketing Cloud		Deprecated	The OAuthGrantType and APIIntegrationType properties are deprecated. Use the AuthScheme connection property instead.
2022-05-18	22.0.8173	Python		Added	Added support for Python 3.10 on Windows, Linux, and Mac Added support for Python 3.9 on Mac Added support for Mac M1
2022-05-18	22.0.8173	Python		Removed	Removed support for Python 3.6 on Windows and Linux
2021-09-02	21.0.7915	General		Added	Added support for the STRING_SPLIT table-valued function in the CROSS APPLY clause.
2021-08-09	21.0.7891	Salesforce Marketing Cloud		Added	Added a new connection property DisplayChildDataExtensions to control if data extensions will display from Child Accounts. For example, if your Client Id and Secret were created for a parent business unit, then the child business units's shared data extensions will be displayed as tables data may be selected from.
2021-08-09	21.0.7891	Salesforce Marketing Cloud		Changed	The Data Extension objects may now be displayed based on which account we have logged in from. When creating an OAuth Client Id and Secret, it may be associated with a particular business unit. Based on which business unit the client id / secret are associated with, different Data Extensions may be displayed, reflecting the available data extensions for that particular business unit.
2021-08-07	21.0.7889	General		Changed	Added the KeySeq column to the sys_foreignkeys table.
2021-08-06	21.0.7888	General		Changed	Added the new sys_primarykeys system table.
2021-07-23	21.0.7874	General		Changed	Updated the Literal Function Names for relative date/datetime functions. Previously, relative date/datetime functions resolved to a different value when used in the projection as opposed to the predicate. For example: SELECT LAST_MONTH() AS lm, Col FROM Table WHERE Col > LAST_MONTH(). Formerly, the two LAST_MONTH() methods would resolve to different datetimes. Now, they will match. As a replacement for the previous behavior, the relative date/datetime functions in the criteria may have an 'L' appended to them. For example: WHERE col > L_LAST_MONTH(). This will continue to resolve to the same values that were previously calculated in the criteria. Note that the "L_" prefix will only work in the predicate - it not available for the projection.
2021-06-30	21.0.7851	Salesforce Marketing Cloud		Added	The child objects nested on the Contacts view have been moved into separate individual child views that will be dynamically determined at runtime. For example, MobileConnect_Demographics, Email_Demographics, etc.
2021-04-25	21.0.7785	General		Added	Added support for handling client side formulas during insert / update. For example: UPDATE Table SET Col1 = CONCAT(Col1, " - ", Col2) WHERE Col2 LIKE 'A%'
2021-04-23	21.0.7783	General		Changed	Updated how display sizes are determined for varchar primary key and foreign key columns so they will match the reported length of the column.
2021-04-23	21.0.7783	Salesforce Marketing Cloud		Added	Added a new Connection property DataExtensionObjectPrefix to customize the DataExtensionObject Prefix value.
2021-04-16	21.0.7776	General		Added	Non-conditional updates between two columns is now available to all drivers. For example: UPDATE Table SET Col1=Col2
2021-04-16	21.0.7776	General		Changed	Reduced the length to 255 for varchar primary key and foreign key columns.
2021-04-16	21.0.7776	General		Changed	Updated implicit and metadata caching to improve performance and support for multiple connections. Old metadata caches are not compatible - you need to generate new metadata caches if you are currently using CacheMetadata.
2021-04-16	21.0.7776	General		Changed	Updated index naming convention to avoid duplicates.
2020-10-01	20.0.7579	Salesforce Marketing Cloud		Removed	Support for SANDBOX accounts because Salesforce has terminated Marketing Cloud Sandbox Accounts (https://help.salesforce.com/articleView?id=mc_rn_october_2019_eol_sandbox_accounts.htm&type=5)

This section provides a walk-through for writing Salesforce Marketing Cloud data access code in Python script.

For more information on the available data source entities and how to query them with SQL, see Data Model. For the SQL syntax, see SQL Compliance.

Connecting from Code

For information on how to deploy the connector and configure the connection to Salesforce Marketing Cloud, see Package Installation and Establishing a Connection.

For information on how to connect with the sfmarketingcloud.connector module and its related classes, see Connecting.

Executing SQL

The connection's cursor object is used to directly execute SQL queries. For information on how to execute SELECT statements and process the returned result sets, see Querying Data. For information on to modify the data in Salesforce Marketing Cloud with INSERT, UPDATE, and DELETE statements, see Modifying Data .

Batch Processing

For information about how to modify several rows of Salesforce Marketing Cloud data at once using parameterized INSERT, UPDATE, and DELETE statements, see Batch Processing.

Connecting with the cdata.sfmarketingcloud Module:

The connector's module is used directly to establish a connection with the data source. It does this by using a connection string as its argument. For example:

import cdata.sfmarketingcloud as mod
conn = mod.connect("User=myUser;Password=myPassword;")

Once the connection is created, you can use it to execute subsequent SQL queries.

After connecting as described in Connecting, you can use the open connection to execute SQL statements.

Executing Queries

To execute SQL statements that return data, use the execute() method. Once a query is executed, the result set is fetched from the cursor. This result set can then be iterated over to process the records individually.

For example:

cur = conn.execute("SELECT Id, Status FROM Subscriber")
rs = cur.fetchall()
for row in rs:
	print(row)

Parameterized Queries

Various Python collections, such as arrays and tuples, can act as additional arguments for the execute() method. This enables you to parameterize the queries executed and help to prevent SQL Injection.

For example:

cmd = "SELECT Id, Status FROM Subscriber WHERE EmailAddress = ?"
params = ["john.doe@example.com"]
cur = conn.execute(cmd, params)
rs = cur.fetchall()
for row in rs:
	print(row)

The connection is also used to issue INSERT, UPDATE, and DELETE commands to the data source. Parameters can be used with these statements if desired.

Note that the connector does not support transactions. As with normal write operations, all SQL statements executed by this connector affect the data source immediately. Call the connection's commit() method following the execution.

Insert

The following example adds a new record to the table:

cmd = "INSERT INTO Subscriber (Id, Status) VALUES (?, ?)"
params = ["Jon Doe", "John"]
cur = conn.execute(cmd, params)
print("Records affected: ", cur.rowcount)

Update

The following example modifies an existing record in the table:

cmd = "UPDATE Subscriber SET Status = ? WHERE Id = ?"
params = ["John", "902548304"]
cur = conn.execute(cmd, params)
print("Records affected: ", cur.rowcount)

Delete

The following example removes an existing record from the table:

cmd = "DELETE FROM Subscriber WHERE Id = ?"
params = ["902548304"]
cur = conn.execute(cmd, params)
print("Records affected: ", cur.rowcount)

This Python connector also supports writing to the data source via batch processing, using the cursor object's executemany() method. This requires both a SQL statement string and a data frame of values that act as a series of parameters for executing the SQL statement.

Note that the connector does not support transactions. As with normal write operations, all SQL statements executed by this connector affect the data source immediately. Call the connection's commit() method following the execution.

Insert

The following example adds new records to the table:

cur = conn.cursor()
cmd = "INSERT INTO Subscriber (Id, Status) VALUES (?, ?)"
params = [["Jon Doe", "John"], ["Jon Doe", "John"]]
cur.executemany(cmd, params)
print("Records affected: ", cur.rowcount)

Update

The following example modifies existing records in the table:

cur = conn.cursor()
cmd = "UPDATE Subscriber SET Status = ? WHERE Id = ?"
params = [["John", "902548304"], ["John", "902548304"]]
cur.executemany(cmd, params)
print("Records affected: ", cur.rowcount)

Delete

The following example removes existing records from the table:

cur = conn.cursor()
cmd = "DELETE FROM Subscriber WHERE Id = ?"
params = [["902548304"], ["902548304"]]
cur.executemany(cmd, params)
print("Records affected: ", cur.rowcount)

The connector is integrated with other tools and packages within Python.

Python Integration Guides

The following sections show how to create and use connections with the connector in common packages in Python:

• From SQLAlchemy
• From Pandas
• From Matplotlib
• From Petl

Complete List of Salesforce Marketing Cloud Integration Quickstarts

For information on connecting from other applications, see Salesforce Marketing Cloud integration guides.

The CData Python Connector for Salesforce Marketing Cloud includes a Dialect class that enables integration with SQLAlchemy. Bear in mind that several aspects of connector functionality are not currently supported in SQLAlchemy 2.0 or above. If necessary, downgrade SQLAlchemy to version 1.4 or 1.3 before using this connector.

The following sections detail various aspects of this integration:

Connecting From SQLAlchemy

To construct a URL with which SQLAlchemy loads and uses the appropriate connector automatically, see Connecting

Reflecting Metadata With SQLAlchemy

To learn how to model Salesforce Marketing Cloud tables with mapped classes, see Reflecting Metadata.

Querying Data From SQLAlchemy

To learn how to use mapped classes to query the associated tables, see Querying Data.

Modifying Data From SQLAlchemy

The connector provides INSERT/UPDATE/DELETE functionality in SQLAlchemy. To learn how to call the session's execute() method to affect the data in the data source, see Modifying Data.

Connecting With a Dialect URL

Establishing a connection using SQLAlchemy requires a specific URL format. For this connector, you can create the engine using either of the following URL formats:

Format 1

from sqlalchemy import create_engine
engine = create_engine("sfmarketingcloud:///?User=myUser;Password=myPassword;")

Format 2

from sqlalchemy import create_engine
engine = create_engine("sfmarketingcloud://User:Password@/")

For SQLAlchemy 2.0, the dialect name is sfmarketingcloud_2. To establish a connection, use the following URL format:

from sqlalchemy import create_engine
engine = create_engine("sfmarketingcloud_2:///?User=myUser;Password=myPassword;")

SQLAlchemy can act as an Object-relational Map (ORM). This enables you to treat records of a database table as instantiable records. To leverage this functionality, you must reflect the underlying metadata in one of the following ways.

Note: The following examples employ SQLAlchemy 1.4.

Modeling Data Using a Mapping Class

Use "sqlalchemy.ext.declarative.declarative_base" to declare a mapping class for the table you wish to model in the ORM. A known table in the data model is modeled either partially or completely, as shown in the following example:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Subscriber(Base):
	__tablename__ = "Subscriber"
	Id = Column(String, primary_key=True)
	Id = Column(String)
	Status = Column(String)

Automatically Reflecting Metadata

Rather than mapping tables manually, SQLAlchemy can discover the metadata for one or more tables automatically. To accomplish this across the entire data model, use automap_base:

from sqlalchemy import MetaData
from sqlalchemy.ext.automap import automap_base
meta = MetaData()
abase = automap_base(metadata=meta)
abase.prepare(autoload_with=engine)
Subscriber = abase.classes.Subscriber

You can also reflect a single table with an inspector. When reflecting this way, providing a list of specific columns to map is optional:

from sqlalchemy import MetaData, Table
from sqlalchemy import inspect
meta = MetaData()
insp = inspect(engine)
Subscriber_table = Table("Subscriber", meta)
insp.reflect_table(Subscriber_table, ["Id","Status"])

After you use the steps in Connecting to connect, and use one of the methods in Reflecting Metadata to reflect some of the metadata, you can use a session object to query data.

Querying Data Using the Query Method

If the mapping class has been prepared, use it with a session object to query the data source. After binding the engine to the session, provide the mapping class to the session's query method.

For example:

engine = create_engine("sfmarketingcloud:///?User=myUser;Password=myPassword;")
factory = sessionmaker(bind=engine)
session = factory()
for instance in session.query(Subscriber).filter_by(EmailAddress="john.doe@example.com"):
	print("Id: ", instance.Id)
	print("Id: ", instance.Id)
	print("Status: ", instance.Status)
	print("---------")

Querying Data Using the Execute Method

The session object can also run the query with the execute() method alongside the appropriate Table object. Assuming you have an active session, the following is just as viable:

Subscriber_table = Subscriber.metadata.tables["Subscriber"]
for instance in session.execute(Subscriber_table.select().where(Subscriber_table.c.EmailAddress == "john.doe@example.com")):
	print("Id: ", instance.Id)
	print("FullName: ", instance.Name)
	print("City: ", instance.BillingCity)
	print("---------")

Implicit Joining

If mapped classes of related Salesforce Marketing Cloud objects have a singular foreign key relationship, the classes are implicitly joined. After importing the necessary objects, a relationship is established between your two mapped classes, as in the example below:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, DateTime, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()
class Contact(Base):
	__tablename__ = "Contact"
	Id = Column(Integer, primary_key=True)
	Name = Column(String)
	Email = Column(String)
	BirthDate = Column(DateTime)
	AccountId = Column(String, ForeignKey("Account.Id"))
	Account_Link = relationship("Account", back_populates="Contact_Link")

class Account(Base):
	__tablename__ = "Account"
	Id = Column(String, primary_key=True)
	Name = Column(String)
	BillingCity = Column(String)
	NumberOfEmployees = Column(Integer)
	Contact_Link = relationship("Contact", order_by=Contact.Id, back_populates="Account_Link")

Once the relationship is established, the tables are queried simultaneously using the session's query() method. For example:

rs = session.query(Account, Contact).filter(Account.Id == Contact.AccountId)
for Ac, Ct in rs:
  print("AccountId: ", Ac.Id)
  print("AccountName: ", Ac.Name)
  print("ContactId: ", Ct.Id)
  print("ContactName: ", Ct.Name)

Other Join Forms

In situations where mapped classes have either no foreign keys or multiple foreign keys, you may need different forms of the JOIN query to accommodate them. Using the earlier classes as examples, the following JOIN queries are possible as well:

• Explicit condition (necessary if there are no foreign keys in your mapped classes):
  

  rs = session.query(Account, Contact).join(Contact, Account.Id == Contact.AccountId)
  for Ac, Ct in rs:

• Left-to-right relationship:
  

  rs = session.query(Account, Contact).join(Account.Contact_Link)
  for Ac, Ct in rs:

• Left-to-right relationship with explicit target:
  

  rs = session.query(Account, Contact).join(Contact, Account.Contact_Link)
  for Ac, Ct in rs:

• String form of a left-to-right relationship:
  

  rs = session.query(Account, Contact).join("Contact_Link")
  for Ac, Ct in rs:

SQLAlchemy ORM also exposes support for other clauses in SQL, such as ORDER BY, GROUP BY, LIMIT, and OFFSET. All of these are supported by this connector:

ORDER BY

The following example sorts by a specified column using the session object's query() method:

rs = session.query(Subscriber).order_by(Subscriber.AnnualRevenue)
for instance in rs:
	print("Id: ", instance.Id)
	print("Id: ", instance.Id)
	print("Status: ", instance.Status)
	print("---------")

You can also use the session object's execute() method perform an ORDER BY. For example:

rs = session.execute(Subscriber_table.select().order_by(Subscriber_table.c.AnnualRevenue))
for instance in rs:

GROUP BY

The following example uses the session object's query() method to group records with a specified column:

rs = session.query(func.count(Subscriber.Id).label("CustomCount"), Subscriber.Id).group_by(Subscriber.Id)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("Id: ", instance.Id)
	print("---------")

You can also use the session object's execute() method to perform a GROUP BY:

rs = session.execute(Subscriber_table.select().with_only_columns([func.count(Subscriber_table.c.Id).label("CustomCount"), Subscriber_table.c.Id]).group_by(Subscriber_table.c.Id))
for instance in rs:

LIMIT and OFFSET

The following example uses the session object's query() method to skip the first 100 records and fetch the following 25:

rs = session.query(Subscriber).limit(25).offset(100)
for instance in rs:
	print("Id: ", instance.Id)
	print("Id: ", instance.Id)
	print("Status: ", instance.Status)
	print("---------")

You can also use the session object's execute() method to set a LIMIT or OFFSET:

rs = session.execute(Subscriber_table.select().limit(25).offset(100))
for instance in rs:

Certain aggregate functions can also be used within SQLAlchemy by using the func module.

To import this module, execute:

from sqlalchemy.sql import func

Once func is imported, the following aggregate functions are available:

COUNT

The following example counts the number of records in a set of groups using the session object's query() method.

rs = session.query(func.count(Subscriber.Id).label("CustomCount"), Subscriber.Id).group_by(Subscriber.Id)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("Id: ", instance.Id)
	print("---------")

You can also execute COUNT using the session object's execute() method:

rs = session.execute(Subscriber_table.select().with_only_columns([func.count(Subscriber_table.c.Id).label("CustomCount"), Subscriber_table.c.Id])group_by(Subscriber_table.c.Id))
for instance in rs:

SUM

This example calculates the cumulative amount of a numeric column in a set of groups.

rs = session.query(func.sum(Subscriber.AnnualRevenue).label("CustomSum"), Subscriber.Id).group_by(Subscriber.Id)
for instance in rs:
	print("Sum: ", instance.CustomSum)
	print("Id: ", instance.Id)
	print("---------")

You can also invoke SUM using the session object's execute() method.

rs = session.execute(Subscriber_table.select().with_only_columns([func.sum(Subscriber_table.c.AnnualRevenue).label("CustomSum"), Subscriber_table.c.Id]).group_by(Subscriber_table.c.Id))
for instance in rs:

AVG

This example uses the session object's query() method to calculate the average amount of a numeric column in a set of groups:

rs = session.query(func.avg(Subscriber.AnnualRevenue).label("CustomAvg"), Subscriber.Id).group_by(Subscriber.Id)
for instance in rs:
	print("Avg: ", instance.CustomAvg)
	print("Id: ", instance.Id)
	print("---------")

You can also use the session object's execute() method to invoke AVG:

rs = session.execute(Subscriber_table.select().with_only_columns([func.avg(Subscriber_table.c.AnnualRevenue).label("CustomAvg"), Subscriber_table.c.Id]).group_by(Subscriber_table.c.Id))
for instance in rs:

MAX and MIN

This example finds the maximum value and minimum value of a numeric column in a set of groups.

rs = session.query(func.max(Subscriber.AnnualRevenue).label("CustomMax"), func.min(Subscriber.AnnualRevenue).label("CustomMin"), Subscriber.Id).group_by(Subscriber.Id)
for instance in rs:
	print("Max: ", instance.CustomMax)
	print("Min: ", instance.CustomMin)
	print("Id: ", instance.Id)
	print("---------")

You can also use the session object's execute() method to invoke MAX and MIN:

rs = session.execute(Subscriber_table.select().with_only_columns([func.max(Subscriber_table.c.AnnualRevenue).label("CustomMax"), func.min(Subscriber_table.c.AnnualRevenue).label("CustomMin"), Subscriber_table.c.Id]).group_by(Subscriber_table.c.Id))
for instance in rs:

Commands can be executed individually by the session with a call to "execute()".

Obtaining the Table Object

The query supplied to this method is constructed using the associated Table object of a mapped class. This Table object is obtained from the mapped class's metadata field, as below:

Subscriber_table = Subscriber.metadata.tables["Subscriber"]

Once the table object is obtained, the write operations are executed in the following ways. The queries are executed immediately without the need for a call to "commit()":

Insert

The following example adds a new record to the table:

session.execute(Subscriber_table.insert(), {"Id": "Jon Doe", "Status": "John"})

Update

The following example modifies an existing record in the table:

session.execute(Subscriber_table.update().where(Subscriber_table.c.Id == "902548304").values(Id="Jon Doe", Status="John"))

Delete

The following example removes an existing record from the table:

session.execute(Subscriber_table.delete().where(Subscriber_table.c.Id == "902548304"))

When combined with the connector, Pandas can be used to generate data frames that contain your Salesforce Marketing Cloud data. Once created, a data frame can be passed to various other Python packages.

Connecting

Pandas relies on an SQLAlchemy engine to execute queries. Before you can use Pandas you must import it:

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("sfmarketingcloud:///?User=myUser;Password=myPassword;")

Querying Data

In Pandas, SELECT queries are provided in a call to the read_sql() method, alongside a relevant connection object. Pandas executes the query on that connection, and returns the results in the form of a data frame, which can be used for a variety of purposes.

df = pd.read_sql("""
	SELECT
	   Id,
	   Status,
     $exNumericCol;
	FROM Subscriber;""", engine)
print(df)

Modifying Data

To insert new records into a table, create a new data frame, and define its fields accordingly. When that is done, call to_sql() on the data frame to perform the INSERT operation with the connector, as shown in the example below. You must set the "if _exists" argument to "append" to prevent Pandas from attempting building the table from scratch. To prevent Pandas from writing the data frame index as a column, set index=False.

df = pd.DataFrame({"Id": ["Jon Doe"], "Status": ["John"]})
df.to_sql("Subscriber", con=engine, if_exists="append", index=False)

Matplotlib contains a number of tools that can graphically model Salesforce Marketing Cloud data after being fed a data frame From Pandas.

Using PyPlot

Before any Matplotlib tool, such as pyplot, can be used, it must be imported:

from matplotlib import pyplot as plt

Once a Pandas data frame is obtained, it can be used to create a plot visualizing Salesforce Marketing Cloud data. For example, the following plot generates and displays a bar graph relating Id and AnnualRevenue values:

df.plot(kind="bar", x="Id", y=["AnnualRevenue"])
plt.show()

The connector can be used to create ETL applications and pipelines for CSV data in Python using Petl.

Install Required Modules

Install the Petl modules using the pip utility.

pip install petl

Connecting

After you import the modules, including the CData Python Connector for Salesforce Marketing Cloud, you can use the connector's connect function to create a connection using a valid Salesforce Marketing Cloud connection string. If you prefer not to use a direct connection, you can use a SQLAlchemy engine.

import petl as etl
import cdata.sfmarketingcloud as mod
cnxn = mod.connect("User=myUser;Password=myPassword;")

Extract, Transform, and Load the Salesforce Marketing Cloud Data

Create a SQL query string and store the query results in a DataFrame.

sql = "SELECT	Id, Status FROM Subscriber "
table1 = etl.fromdb(cnxn,sql)

Loading Data

With the query results stored in a DataFrame, you can load your data into any supported Petl destination. The following example loads the data into a CSV file.

etl.tocsv(table1,'output.csv')

Modifying Data

Insert new rows into Salesforce Marketing Cloud tables using Petl's appenddb function.

table1 = [['Id','Status'],['Jon Doe','John']]
etl.appenddb(table1,cnxn,'Subscriber')

The extension supports schema discovery by using SQL queries to available System Tables.

Using SQL

The following sections describe the discovery of metadata through several System Tables:

• Tables and Views
• Columns
• Procedures

The connector possesses system tables that are used to discover the tables and views available in the data model. Of these system tables, "sys_tables" and "sys_views" are used to fetch information about the available tables and views respectively:

Tables

import cdata.sfmarketingcloud as mod
conn = mod.connect("User=myUser;Password=myPassword;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_tables"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Views

import cdata.sfmarketingcloud as mod
conn = mod.connect("User=myUser;Password=myPassword;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_views"
cur.execute(cmd, params)
rs = cur.fetchall()
for row in rs:
	print(row)

The available columns for any given table are fetched from a system table called "sys_tablecolumns". A specific table name is provided in the WHERE criteria to restrict the table from which the column information is fetched:

import cdata.sfmarketingcloud as mod
conn = mod.connect("User=myUser;Password=myPassword;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_tablecolumns WHERE TableName = 'Subscriber'"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Procedures

A system table called "sys_procedures" is queried to obtain the available stored procedures that are executed:

import cdata.sfmarketingcloud as mod
conn = mod.connect("User=myUser;Password=myPassword;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_procedures"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Parameters

The input parameters of any stored procedure are similarly obtained from the "sys_procedureparameters" system table:

import cdata.sfmarketingcloud as mod
conn = mod.connect("User=myUser;Password=myPassword;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'SelectEntries'"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

This section details a selection of advanced features of the Salesforce Marketing Cloud connector.

User Defined Views

The connector supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the connector for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Caching Data

Caching Data enables faster access to data and reduces the number of API calls, improving performance. The connector supports a simple caching model where multiple connections can also share the cache over time. When configuring the cache connection, you can specify automatic or explicit data caching.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Salesforce Marketing Cloud and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

Exception Handling

For an overview of how exceptions are reported and the components of an exception, see Exception Handling.

The CData Python Connector for Salesforce Marketing Cloud supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from a tool.

Use a user defined view to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Defining Views Using a Configuration File

User defined views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This user defined view configuration file is formatted so that each root element defines the name of a view, and includes a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM Subscriber WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"

Defining Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset the UserViewsSchemaName property.

Working with User Defined Views

For example, a SQL statement with a user defined view called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a user defined view that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

Customizing the SSL Configuration

By default, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

Note: The connector uses the system proxy settings by default, without further configuration needed. If you want to connect to other proxies, set ProxyAutoDetect to False and read further.

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the connector uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Caching Data

Caching data provides several benefits, including faster access to data and reducing the number of API calls, which improve performance. The connector supports a simple caching model where multiple connections can also share the cache over time. You can enable and configure caching features by setting the necessary connection properties.

Contents

The sections in this chapter detail the connector's caching functionality and link to the corresponding connection properties, as well as SQL statements.

Configuring the Cache Connection

Configuring the Cache Connection describes the properties that you can set when configuring the cache database.

Caching Metadata

Caching Metadata describes the CacheMetadata property. This property determines whether or not to cache the table metadata to a file store.

Automatically Caching Data

Automatically Caching Data describes how the connector automatically refreshes the cache when the AutoCache property is set.

Explicitly Caching Data

Explicitly Caching Data describes how you can decide what data is stored in the cache and when it is updated.

Data Type Mapping

Data Type Mapping shows the mappings between the data types configured in the schema and the data types in the database.

Configuring the Caching Database

This section describes the properties for caching data to the persistent store of your choice.

CacheLocation

The CacheLocation property species the path to a file-system-based database. When caching is enabled, a file-system-based database is used by default. If CacheLocation is not specified, this database is stored at the path in Location. If neither of these connection properties are specified, the connector uses a platform-dependent default location.

CacheConnection

The CacheConnection property specifies a database driver and the connection string to the caching database.

CacheDriver and CacheProvider

Both the CacheDriver and CacheProvider properties are supported. Each specifies a database driver and the connection string to the caching database. CacheDriver is designed for Linux and MacOS; CacheProvider is Windows-based.

This section describes how to enable caching metadata and how to update the metadata cache.

Before being able to query data, the connector requires relevant metadata to be retrieved. By default, metadata is cached in memory and shared across connections. But if you want to persist across processes, or if metadata requests are expensive, the solution is to cache the metadata to disk.

Enable Caching Metadata

To enable caching of metadata, set CacheMetadata = true and see Configuring the Cache Connection for instructions on how to configure your connection string. The connector caches the metadata the first time it is needed and uses the metadata cache for subsequent requests.

Update the Metadata Cache

Because metadata is cached, changes to metadata on the live source, for example, adding or removing a column or attribute, are not automatically reflected in the metadata cache. To get updates to the live metadata, you need to delete or drop the cached data.

Automatically caching data is useful when you do not want to rebuild the cache for each query. When you query data for the first time, the connector automatically initializes and builds a cache in the background. When AutoCache = true, the connector uses the cache for subsequent query executions, resulting in faster response times.

If replication is enabled, the data is generated once and then copied to local and cloud data stores. With incremental updates, the connector achieves a performance advantage over dropping the cached tables and retrieving the entire table again on every refresh. With iterative updates, the connector only performs the query from the last time that the date was refreshed. If replication is not enabled, updates to the cache require downloading the entire data set.

Configuring Automatic Caching

To automatically update the cache and return results from the local cache, set the following connection string properties:

• AutoCache: This property automatically updates the cache when the value is set to true.
• CacheTolerance: This property ensures that the data retrieved from the database is the most current version. The default value is 600 seconds (10 minutes). The connector checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.

Caching the Subscriber Table

The following example caches the Subscriber table in the file specified by the CacheLocation property of the connection string.

SELECT Id, Status FROM Subscriber WHERE EmailAddress = 'john.doe@example.com'

Common Use Case

A common use for automatically caching data is to improve driver performance when making repeated requests to a live data source, such as building a report or creating a visualization. With auto caching enabled, repeated requests to the same data may be executed in a short period of time, but within an allowable tolerance (CacheTolerance) of what is considered "live" data.

With explicit caching (AutoCache = false), you decide exactly what data is cached and when to query the cache instead of the live data. Explicit caching gives you full control over the cache contents by using CACHE Statements. This section describes some strategies to use the caching features offered by the connector.

Creating the Cache

To load data in the cache, issue the following statement.

CACHE SELECT * FROM tableName WHERE ...

Once the statement is issued, any matching data in tableName is loaded into the corresponding table.

Updating the Cache

This section describes two ways to update the cache.

Updating with the SELECT Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. However, this statement does not delete extra rows that are already in the cache. This statement only merges the new rows or updates the existing rows.

CACHE SELECT * FROM Subscriber WHERE EmailAddress = 'john.doe@example.com'

Updating with the TRUNCATE Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. This statement can also delete rows in the cache table that are not present in the live data source.

  CACHE WITH TRUNCATE SELECT * FROM Subscriber WHERE EmailAddress = 'john.doe@example.com'
  

Query the Data in Online or Offline Mode

This section describes how to query the data in online or offline mode.

Online: Select Cached Tables

You can use the tableName#CACHE syntax to explicitly execute queries to the cache while still online, as shown in the following example.

SELECT * FROM Subscriber#CACHE

Offline: Select Cached Tables

With Offline = true, SELECT statements always execute against the local cache database, regardless of whether you explicitly specify the cached table or not. Modification of the cache is disabled in Offline mode to prevent accidentally updating only the cached data. Executing a DELETE/UPDATE/INSERT statement while in Offline mode results in an exception.

The following example selects from the local cache but not the live data source because Offline = true.

SELECT * FROM Subscriber WHERE EmailAddress='john.doe@example.com' ORDER BY Status ASC

Delete Data from the Cache

You can delete data from the cache by building a direct connection to the database. Note that the connector does not support manually deleting data from the cache.

Common Use Case

A common use for caching is to have an application always query the cached data and only update the cache at set intervals, such as once every day or every two hours. There are two ways in which this can be implemented:

• AutoCache = false and Offline = false. All queries issued by the application explicitly reference the tableName#CACHE table. When the cache needs to be updated, the application executes a tableName#CACHE ... statement to bring the cached data up to date.
• Offline = true. Caching is transparent to the application. All queries are executed against the table as normal, so most application code does not need to be aware that caching is done. To update the cached data, simply create a separate connection with Offline = false and execute a tableName#CACHE ... statement.

The connector maps types from the data source to the corresponding data type available in the chosen cache database. The following table shows the mappings between the data types configured in the schema and the data types in the database. Some schema types have synonyms which are all listed in the Schema column.

Data Type Mapping

Note: String columns can map to different data types depending on their length.

Schema	.NET	JDBC	SQL Server	Derby	MySQL	Oracle	SQLite	Access
int, integer, int32	Int32	int	int	INTEGER	INT	NUMBER	integer	LONG
smallint, short, int16	Int16	short	smallint	SMALLINT	SMALLINT	NUMBER	integer	SHORT
double, float, real	Double	double	float	DOUBLE	DOUBLE	NUMBER	double	DOUBLE
date	DateTime	java.sql.Date	date	DATE	DATE	DATE	date	DATETIME
datetime, timestamp	DateTime	java.sql.Date	datetime	TIMESTAMP	DATETIME	TIMESTAMP	datetime	DATETIME
time, timespan	TimeSpan	java.sql.Time	time	TIME	TIME	TIMESTAMP	datetime	DATETIME
string, varchar	String	java.lang.String	If length > 4000: nvarchar(max), Otherwise: nvarchar(length)	If length > 32672: LONG VARCHAR, Otherwise VARCHAR(length)	If length > 255: LONGTEXT, Otherwise: VARCHAR(length)	If length > 4000: CLOB, Otherwise: VARCHAR2(length)	nvarchar(length)	If length > 255: LONGTEXT, Otherwise: VARCHAR(length)
long, int64, bigint	Int64	long	bigint	BIGINT	BIGINT	NUMBER	bigint	LONG
boolean, bool	Boolean	boolean	tinyint	SMALLINT	BIT	NUMBER	tinyint	BIT
decimal, numeric	Decimal	java.math.BigDecimal	decimal	DECIMAL	DECIMAL	DECIMAL	decimal	CURRENCY
uuid	Guid	java.util.UUID	nvarchar(length)	VARCHAR(length)	VARCHAR(length)	VARCHAR2(length)	nvarchar(length)	VARCHAR(length)
binary, varbinary, longvarbinary	byte[]	byte[]	binary(1000) or varbinary(max) after SQL Server 2000, image otherwise	BLOB	LONGBLOB	BLOB	BLOB	LONGBINARY

Query Processing

CData has a client-side SQL engine built into the connector library. This enables support for the full capabilities that SQL-92 offers, including filters, aggregations, functions, etc.

For sources that do not support SQL-92, the connector offloads as much of SQL statement processing as possible to Salesforce Marketing Cloud and then processes the rest of the query in memory (client-side). This results in optimal performance.

For data sources with limited query capabilities, the connector handles transformations of the SQL query to make it simpler for the connector. The goal is to make smart decisions based on the query capabilities of the data source to push down as much of the computation as possible. The Salesforce Marketing Cloud Query Evaluation component examines SQL queries and returns information indicating what parts of the query the connector is not capable of executing natively.

The Salesforce Marketing Cloud Query Slicer component is used in more specific cases to separate a single query into multiple independent queries. The client-side Query Engine makes decisions about simplifying queries, breaking queries into multiple queries, and pushing down or computing aggregations on the client-side while minimizing the size of the result set.

There's a significant trade-off in evaluating queries, even partially, client-side. There are always queries that are impossible to execute efficiently in this model, and some can be particularly expensive to compute in this manner. CData always pushes down as much of the query as is feasible for the data source to generate the most efficient query possible and provide the most flexible query capabilities.

More Information

For a full discussion of how CData handles query processing, see CData Architecture: Query Execution.

Logging

Capturing connector logging can be very helpful when diagnosing error messages or other unexpected behavior.

Basic Logging

To begin capturing connector logging, set these properties:

• Logfile: A filepath that designates the name and location of the log file.
• Verbosity: A numerical value (1-5) that determines the amount of detail in the log. See the page in the Connection Properties section for an explanation of the five levels.
• MaxLogFileSize: When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.
• MaxLogFileCount: A string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted. Minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.

Once these properties are set, the connector populates the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it is created.

Log Verbosity

The verbosity level determines the amount of detail that the connector reports to the Logfile. Supported Verbosity levels range from 1 to 5.

The following list describes each level:

1	Setting Verbosity to 1 logs the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
2	Setting Verbosity to 2 logs everything included in Verbosity 1, cache queries, and additional information about the request.
3	Setting Verbosity to 3 also logs HTTP headers, as well as the body of the request and the response.
4	Setting Verbosity to 4 also logs transport-level communication with the data source. This includes SSL negotiation.
5	Setting Verbosity to 5 also logs communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

For normal operations, Verbosity should not be set to greater than 1. At higher verbosities you can log substantial amounts of data, which can delay execution times.

To refine the logged content further by showing/hiding specific categories of information, see LogModules.

Sensitive Data

Verbosity levels of 3 and higher may capture information that you do not want shared outside of your organization. The following lists information of concern for each level:

• Verbosity 3: The full body of the request and the response, which includes all the data returned by the connector
• Verbosity 4: SSL certificates
• Verbosity 5: Any extra transfer data not included at Verbosity 3, such as non human-readable binary transfer data

Note: Although we mask sensitive values, such as passwords, in the connection string and any request in the log, it is always best practice to review the logs for any sensitive information before sharing outside your organization.

Advanced Logging

You may want to refine the exact information that is recorded to the log file. This can be accomplished using the LogModules property. This property allows you to filter the logging using a semicolon-separated list of logging modules.

Example property value:

LogModules=INFO;EXEC;SSL;SQL;META;

Note that the logfile filtering triggered by the Verbosity connection property takes precedence over the filtering imposed by this connection property. This means that operations of a higher verbosity level than the level specified in the Verbosity connection property are not printed in the logfile, even if they belong to one of the modules specified in this connection property.

The available modules and submodules are:

Module Name	Module Description	Submodules
INFO	General Information. Includes the connection string, product version (build number), and initial connection messages.	Connec – Information related to creating or destroying connections. Messag – Generic label for messages pertaining to connections, the connection string, and product version. These messages are typically specific to the connector, rather than being received and passed along directly from the service.
EXEC	Query Execution. Includes execution messages for user-written SQL queries, parsed SQL queries, and normalized SQL queries. Success/failure messages for queries and query pages appear here as well.	Messag – Messages pertaining to query execution. These messages are typically specific to the connector, rather than being received and passed along directly from the service. Normlz – Query normalization steps. Query normalization is when the product takes the user-submitted query and rewrites the query to get the same results with optimal performance. Origin – This label applies to any messages recording a user's original query (the exact, unaltered, non-normalized query executed by the user). Page – Messages related to query paging. Parsed – Query parsing steps. Parsing is the process of converting the user-submitted query into a standardized format for easier processing.
HTTP	HTTP protocol messages. Includes HTTP requests/responses (including POST messages), as well as Kerberos related messages.	KERB – HTTP requests related to Kerberos. Messag – Messages pertaining to HTTP protocols. These messages are typically specific to the connector, rather than being received and passed along directly from the service. Unpack – This label applies to messages about zipped data being returned from the service API and unpacked by the product. Res – Messages containing HTTP responses. Req – Messages containing HTTP requests.
WSDL	Messages pertaining to the generation of WSDL/XSD files.	—
SSL	SSL certificate messages.	Certif – Messages pertaining to SSL certificates.
AUTH	Authentication related failure/success messages.	Messag – Messages pertaining to authentication. These messages are typically specific to the connector, rather than being received and passed along directly from the service. OAuth – Messages related to OAuth authentication. Krbros – Kerberos-related authentication messages.
SQL	Includes SQL transactions, SQL bulk transfer messages, and SQL result set messages.	Bulk – Messages pertaining to bulk query execution. Cache – Messages related to reading row data from and writing row data to the product's cache for better performance. Messag – Messages pertaining to SQL transactions. These messages are typically specific to the connector, rather than being received and passed along directly from the service. ResSet – Query resultsets. Transc – Messages related to handling transactions, including information about the number of jobs executed and backup table handling.
META	Metadata cache and schema messages.	Cache – Messages related to reading from and modifying column and table definitions in the product's cache for better performance. Schema – Messages related to retrieving metadata from or modifying the service schema. MemSto – Messages related to writing to or reading from in-memory metadata cache. Storag – Messages relating to storing metadata on disk or in an external data store, rather than in memory.
FUNC	Information related to executing SQL functions.	Errmsg – Error messages related to executing SQL functions.
TCP	Incoming and outgoing raw bytes on TCP transport layer messages.	Send – Raw data sent via the TCP protocol. Receiv – Raw data received via the TCP protocol.
FTP	Messages pertaining to the File Transfer Protocol.	Info – Status messages related to communication in the FTP protocol. Client – Messages related to actions taken by the FTP client (the product) during FTP communication. Server – Messages related to actions taken by the FTP server during FTP communication.
SFTP	Messages pertaining to the Secure File Transfer Protocol.	Info – Status messages related to communication in the SFTP protocol. To_Server – Messages related to actions taken by the SFTP client (the product) during SFTP communication. From_Server – Messages related to actions taken by the SFTP server during SFTP communication.
POP	Messages pertaining to data transferred via the Post Office Protocol.	Client – Messages related to actions taken by the POP client (the product) during POP communication. Server – Messages related to actions taken by the POP server during POP communication. Status – Status messages related to communication in the POP protocol.
SMTP	Messages pertaining to data transferred via the Simple Mail Transfer Protocol.	Client – Messages related to actions taken by the SMTP client (the product) during SMTP communication. Server – Messages related to actions taken by the SMTP server during SMTP communication. Status – Status messages related to communication in the SMTP protocol.
CORE	Messages relating to various internal product operations not covered by other modules.	—
DEMN	Messages related to SQL remoting.	—
CLJB	Messages about bulk data uploads (cloud job).	Commit – Submissions for bulk data uploads.
SRCE	Miscellaneous messages produced by the product that don't belong in any other module.	—
TRANCE	Advanced messages concerning low-level product operations.	—

Exception Handling

Exceptions can be surfaced from either the API or the CData Python Connector for Salesforce Marketing Cloud. Each exception will have an error code, an error message, and a SQL state.

Error Codes

The error code classifies the type of error.

0	NONE	Used for unclassified errors and internally handled errors. This code also covers data source-specific errors that do not fit in any specific category.
65537	TCP_UNKNOWN_HOST	Unable to resolve a hostname (DNS failure).
65538	TCP_CONNECTION_REFUSED	Could not connect to the remote port.
65539	TCP_AUTH_FAILED	Login failed when using a binary authentication protocol. Use this for auth errors when the protocol is not HTTP (LDAP, SASL, Kerberos, ...).
65540	TCP_TIMEOUT	Did not receive a response after sending a request to the server.
65541	TCP_PROTOCOL	For wire protocol drivers. Either the server sent a bad packet that we are unable to process, or we cannot construct a packet to send.
131073	TLS_SERVER_UNTRUSTED	Could not verify SSL server certificate.
131074	TLS_CLIENT_UNTRUSTED	Server did not accept the client certificate we sent.
196609	OAUTH_DECRYPT_FAILED	OAuthEncryptKey did not decrypt the OAuthSettings file.
196610	OAUTH_MISSING_CLIENT_INFO	OAuthClientId / OAuthClientSecret / OAuthJWTCert is missing.
196611	OAUTH_MISSING_PROP	General OAuth property missing. OAUTH_MISSING_CLIENT_INFO is used for missing client ID/secret and JWT cert.
196612	OAUTH_NO_ACCESS_TOKEN	Unable to retrieve access token. Only use this when getting a token in GetOAuthAccessToken / RefreshOAuthAccessToken.
196613	OAUTH_TOKEN_EXPIRED	The access token expired. Normally used with a RefreshOAuth/OAuthException behavior.
196614	OAUTH_INVALID_PROP	OAuth property has an invalid value. OAUTH_MISSING_CLIENT_INFO / OAUTH_MISSING_PROP is used if the value is not set.
262145	HTTP_REQUEST_TIMEOUT	Did not receive a response from the HTTP server.
262146	HTTP_CLIENT_ERROR	Generic HTTP 4xx error. Only use for 4xx errors not covered by other codes.
262147	HTTP_AUTH_FAILED	HTTP 401 error.
262148	HTTP_LIMIT_EXCEEDED	HTTP 429 error.
262149	HTTP_SERVER_ERROR	HTTP 5xx error.
262150	HTTP_NOT_FOUND_ERROR	HTTP 404 error.
327681	CORE_TIMEOUT	General timeout. Not related to a specific network request.
327682	CORE_OP_NOT_ALLOWED	Operation blocked by provider permissions.
327683	CORE_CONNECTION_CONFIG	Connection configuration is not valid.
327684	CORE_SERIALIZE	Failed to encode data into a specific format (XML, JSON, CSV, ...).
327685	CORE_DESERIALIZE	Failed to decode data from a specific format (XML, JSON, CSV, ...).
393217	SQL_SYNTAX_ERROR	Unable to parse a SQL query.
393218	SQL_MISSING_COLUMNS	Query did not include required columns.
393219	SQL_MISSING_PARAMS	Stored procedure call did not include required parameters.
393220	SQL_QUERY_NOT_SUPPORTED	A part of the query is not allowed in the current context.
458753	SSH_SERVER_UNTRUSTED	Could not verify SSH server.
524289	STORAGE_LIST_EXCEPTION	Issue listing storage resources.
524290	STORAGE_RESOURCE_NOT_FOUND	Issue finding storage resources.
524291	STORAGE_ROOT_RESOURCE_NOT_FOUND	The root resource (bucket/share/drive) was not found; cannot create it in flat file drivers.
524292	STORAGE_RESOURCE_NOT_A_DIRECTORY	Storage resource is not a directory.
524293	STORAGE_RESOURCE_NOT_A_FILE	Storage resource is not a file.
524294	STORAGE_PERMISSIONS_DENIED	Storage permissions denied.

SQL State

The SQL state is used when throwing generic provider errors to the wrapper and indicates the success or failure of a call.

Some of the common SQL states are listed below:

07007	REQUIRED_CLAUSE	Class Code 07: Dynamic SQL Error.
08001	OPEN_CONNECTION	Class Code 08: Connection Exception. The connection was unable to be established to the application server or other server.
08004	REJECT_CONNECTION	The application server rejected establishment of the connection.
42501	PRIVILEGE_IDENTIFIED_OBJECT	Class Code 42: Syntax Error or Access Rule Violation. The authorization ID does not have the privilege to perform the specified operation on the identified object.
42506	AUTH_FAILED	Owner authorization failure occurred.
42601	SQL_SYNTAX	A character, token, or clause is invalid or missing.

Error Message

The error message provides more detailed reasoning about why the error occurred. It provides an explanation of the issue, and may include steps on how to resolve it.

The CData Python Connector for Salesforce Marketing Cloud supports several operations on data, including querying, deleting, modifying, and inserting.

SELECT Statements

See SELECT Statements for a syntax reference and examples.

See Data Model for information on the capabilities of the Salesforce Marketing Cloud API.

INSERT Statements

See INSERT Statements for a syntax reference and examples, as well as retrieving the new records' Ids.

UPDATE Statements

The primary key Id is required to update a record. See UPDATE Statements for a syntax reference and examples.

UPSERT Statements

An UPSERT updates a record if it exists and inserts the record if it does not. See UPSERT Statements for a syntax reference and examples.

DELETE Statements

The primary key Id is required to delete a record. See DELETE Statements for a syntax reference and examples.

CACHE Statements

CACHE statements allow granular control over the connector's caching functionality. For a syntax reference and examples, see CACHE Statements.

For more information on the caching feature, see Caching Data.

Names and Quoting

• Table and column names are considered identifier names; as such, they are restricted to the following characters: [A-Z, a-z, 0-9, _:@].
• To use a table or column name with characters not listed above, the name must be quoted using square brackets ([name]) in any SQL statement.
• Parameter names can optionally start with the @ symbol (e.g., @p1 or @CustomerName) and cannot be quoted.
• Strings must be quoted using single quotes (e.g., 'John Doe').

The connector provides functions that are similar to those that are available with most standard databases. These functions are implemented in the CData provider engine and thus are available across all data sources with the same consistent API. Three categories of functions are available: string, date, and math.

The connector interprets all SQL function inputs as either strings or column identifiers, so you need to escape all literals as strings, with single quotes. For example, contrast the SQL Server syntax and connector syntax for the DATENAME function:

• SQL Server:
  

  SELECT DATENAME(yy,GETDATE())

• connector:
  

  SELECT DATENAME('yy',GETDATE())

String Functions

These functions perform string manipulations and return a string value. See STRING Functions for more details.

Date Functions

These functions perform date and date time manipulations. See DATE Functions for more details.

Math Functions

These functions provide mathematical operations. See MATH Functions for more details.

ASCII(character_expression)

Returns the ASCII code value of the left-most character of the character expression.

• character_expression: The character expression.

                      SELECT ASCII('0');
                      --  Result: 48
                    

BASE64_ENCODE(input_binary)

Returns the Base64-encoded string form of a binary input.

• input_binary: The binary value to encode.

                        SELECT BASE64_ENCODE(BinaryData);
                    -- Result: 'QmFzZTY0RW5jb2RlZA=='
                    

BASE64_DECODE(input_string)

Returns the binary result of decoding a Base64-encoded string.

• input_string: The Base64-encoded string.

                        SELECT BASE64_DECODE('QmFzZTY0RW5jb2RlZA==');
                    -- Result: (binary output)
                    

CHAR(integer_expression)

Converts the integer ASCII code to the corresponding character.

• integer_expression: The integer from 0 through 255.

                      SELECT CHAR(48);
                      -- Result: '0'
                    

CHARINDEX(expressionToFind ,expressionToSearch [,start_location ])

Returns the starting position of the specified expression in the character string.

• expressionToFind: The character expression to find.
• expressionToSearch: The character expression, typically a column, to search.
• start_location: An optional character position to start searching for expressionToFind in expressionToSearch.

                      SELECT CHARINDEX('456', '0123456');
                      -- Result: 4

                      SELECT CHARINDEX('456', '0123456', 5);
                      -- Result: -1
                    

CHAR_LENGTH(character_expression),

Returns the number of UTF-8 characters present in the expression.

• character_expression: The set of characters to be evaluated for length.

				 SELECT CHAR_LENGTH('sample text') FROM Account LIMIT 1
				 -- Result: 11			
				

CONCAT(string_value1, string_value2, ..., string_valueN)

Returns the string that is the concatenation of two or more string values.

• string_value1: The first string to be concatenated.
• string_value2: The second string to be concatenated.
• string_valueN: (optional) Any additional strings to be concatenated.

                      SELECT CONCAT('Hello, ', 'world!');
                      -- Result: 'Hello, world!'
                    

CONTAINS(expressionToSearch, expressionToFind)

Returns 1 if expressionToFind is found within expressionToSearch; otherwise, 0.

• expressionToSearch: The character expression, typically a column, to search.
• expressionToFind: The character expression to find.

                      SELECT CONTAINS('0123456', '456');
                      -- Result: 1

                      SELECT CONTAINS('0123456', 'Not a number');
                      -- Result: 0
                    

ENDSWITH(character_expression, character_suffix)

Returns 1 if character_expression ends with character_suffix; otherwise, 0.

• character_expression: The character expression.
• character_suffix: The character suffix to search for.

                      SELECT ENDSWITH('0123456', '456');
                      -- Result: 1

                      SELECT ENDSWITH('0123456', '012');
                      -- Result: 0
                    

FILESIZE(uri)

Returns the number of bytes present in the file at the specified file path.

• uri: The path of the file from which to read the size.

				SELECT FILESIZE('C:/Users/User1/Desktop/myfile.txt');
				-- Result: 23684
				

FORMAT(value [, parseFormat], format )

Returns the value formatted with the specified format.

• value: The string to format.
• format: The string specifying the output syntax of the date or numeric format.
• parseFormat: The string specifying the input syntax of the date value. Not applicable to numeric types.

                      SELECT FORMAT(12.34, '#');
                      -- Result: 12

                      SELECT FORMAT(12.34, '#.###');
                      -- Result: 12.34

                      SELECT FORMAT(1234, '0.000E0');
                      -- Result: 1.234E3
                      
                      SELECT FORMAT('2019/01/01', 'yyyy-MM-dd');
                      -- Result: 2019-01-01
                      
                      SELECT FORMAT('20190101', 'yyyyMMdd', 'yyyy-MM-dd');
                      -- Result: '2019-01-01'
                    

HASHBYTES(algorithm, value)

Returns the hash of the input value as a byte array using the given algorithm. The supported algorithms are MD5, SHA1, SHA2_256, SHA2_512, SHA3_224, SHA3_256, SHA3_384, and SHA3_512.

• algorithm: The algorithm to use for hashing. Must be one of MD5, SHA1, SHA2_256, SHA2_512, SHA3_224, SHA3_256, SHA3_384, or SHA3_512.
• value: The value to hash. Must be either a string or byte array.

                      SELECT HASHBYTES('MD5', 'Test');
                      -- Result (byte array): 0x0CBC6611F5540BD0809A388DC95A615B
                    

INDEXOF(expressionToSearch, expressionToFind [,start_location ])

Returns the starting position of the specified expression in the character string.

• expressionToSearch: The character expression, typically a column, to search.
• expressionToFind: The character expression to find.
• start_location: An optional character position to start searching for expressionToFind in expressionToSearch.

                      SELECT INDEXOF('0123456', '456');
                      -- Result: 4

                      SELECT INDEXOF('0123456', '456', 5);
                      -- Result: -1
                    

ISALPHABETIC(character_expression)

Returns 1 if the character expression consists only of alphabetic characters; otherwise, 0.

• character_expression: The string expression to evaluate.

                      SELECT ISALPHABETIC('Hello');
                      -- Result: 1

                      SELECT ISALPHABETIC('Hello123');
                      -- Result: 0

                      SELECT ISALPHABETIC('Hello!');
                      -- Result: 0
                    

ISALPHANUMERIC(character_expression)

Returns 1 if the character expression consists only of alphabetic and numeric characters; otherwise, 0.

• character_expression: The string expression to evaluate.

                      SELECT ISALPHANUMERIC('Hello123');
                      -- Result: 1

                      SELECT ISALPHANUMERIC('123');
                      -- Result: 1

                      SELECT ISALPHANUMERIC('Hello.123');
                      -- Result: 0
                    

ISNUMERIC(character_expression)

Returns 1 if the character expression consists only of numeric digits and up to one decimal point; otherwise, 0.

• character_expression: The string expression to evaluate.

                      SELECT ISNUMERIC('123');
                      -- Result: 1

                      SELECT ISNUMERIC('123.45');
                      -- Result: 1

                      SELECT ISNUMERIC('123.45.67');
                      -- Result: 0

                      SELECT ISNUMERIC('12a3');
                      -- Result: 0
                    

JSON_EXTRACT(json, jsonpath)

Selects any value in a JSON array or object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to extract.
• jsonpath: The XPath used to select the nodes. The JSONPath must be a string constant. The values of the nodes selected will be returned in a token-separated list.

                      SELECT JSON_EXTRACT('{"test": {"data": 1}}', '$.test');
                      -- Result: '{"data":1}'

                      SELECT JSON_EXTRACT('{"test": {"data": 1}}', '$.test.data');
                      -- Result: 1

                      SELECT JSON_EXTRACT('{"test": {"data": [1, 2, 3]}}', '$.test.data[1]');
                      -- Result: 2
                    

LEFT ( character_expression , integer_expression )

Returns the specified number of characters counting from the left of the specified string.

• character_expression: The character expression.
• integer_expression: The positive integer that specifies how many characters will be returned counting from the left of character_expression.

                      SELECT LEFT('1234567890', 3);
                      -- Result: '123'
                    

LEN(string_expression)

Returns the number of characters of the specified string expression.

• string_expression: The string expression.

                      SELECT LEN('12345');
                      -- Result: 5
                    

LOCATE(substring,string)

Returns an integer representing how many characters into the string the substring appears.

• substring: The substring to find inside larger string.
• string: The larger string that is searched for the substring.
• start locations: An optional integer that sets the character position (offset) from which to start searching.

				SELECT LOCATE('sample','XXXXXsampleXXXXX');
				-- Result: 6

                SELECT LOCATE('sample', 'XXXXXsampleXXXXX', 7)
                -- Result: 0
				

LOWER ( character_expression )

Returns the character expression with the uppercase character data converted to lowercase.

• character_expression: The character expression.

                      SELECT LOWER('MIXED case');
                      -- Result: 'mixed case'
                    

LTRIM(character_expression)

Returns the character expression with leading blanks removed.

• character_expression: The character expression.

                      SELECT LTRIM('     trimmed');
                      -- Result: 'trimmed'
                    

MASK(string_expression, mask_character [, start_index [, end_index ]])

Replaces the characters between start_index and end_index with the mask_character within the string.

• string_expression: The string expression to be searched.
• mask_character: The character to mask with.
• start_index: The optional number of characters to leave unmasked at beginning of string. Defaults to 0.
• end_index: The optional number of characters to leave unmasked at end of string. Defaults to 0.

                        SELECT MASK('1234567890','*',);
                        -- Result: '**********'
                        SELECT MASK('1234567890','*', 4);
                        -- Result: '1234******'
                        SELECT MASK('1234567890','*', 4, 2);
                        -- Result: '1234****90'  
                    

NCHAR(integer_expression)

Returns the Unicode character with the specified integer code as defined by the Unicode standard.

• integer_expression: The integer from 0 through 65535 (0 through xFFFF).

OCTET_LENGTH(character_expression),

Returns the number of bytes present in the expression.

• character_expression: The set of characters to be be evaluated.

				 SELECT OCTET_LENGTH('text') FROM Account LIMIT 1
				 -- Result: 4
				

PATINDEX(pattern, expression)

Returns the starting position of the first occurrence of the pattern in the expression. Returns 0 if the pattern is not found.

• pattern: The character expression that contains the sequence to be found. The wild-card character % can be used only at the start or end of the expression.
• expression: The expression, typically a column, to search for the pattern.

                      SELECT PATINDEX('123%', '1234567890');
                      -- Result: 1

                      SELECT PATINDEX('%890', '1234567890');
                      -- Result: 8

                      SELECT PATINDEX('%456%', '1234567890');
                      -- Result: 4
                    

POSITION(expressionToFind IN expressionToSearch)

Returns the starting position of the specified expression in the character string.

• expressionToFind: The character expression to find.
• expressionToSearch: The character expression, typically a column, to search.

                      SELECT POSITION('456' IN '123456');
                      -- Result: 4

                      SELECT POSITION('x' IN '123456');
                      -- Result: 0
                    

QUOTENAME(character_string [, quote_character])

Returns a valid SQL Server-delimited identifier by adding the necessary delimiters to the specified Unicode string.

• character_string: The string of Unicode character data. The string is limited to 128 characters. Inputs greater than 128 characters return null.
• quote_character: An optional single character to be used as the delimiter. These include:
  • a single quotation mark (')
  • a left or right bracket ([])
  • a double quotation mark (")
  • a left or right parenthesis ( () )
  • a greater or less than sign (><)
  • a left or right brace ({})
  • a backtick (`)

  If quote_character is not specified brackets are used. If an unacceptable character is supplied, it returns NULL.

                      SELECT QUOTENAME('table_name');
                      -- Result: '[table_name]'

                      SELECT QUOTENAME('table_name', '"');
                      -- Result: '"table_name"'

                      SELECT QUOTENAME('table_name', '[');
                      -- Result: '[table_name]'
                    

REGEXP_REPLACE(expr, pattern [, replacement [, position [, occurrence [, match_type]]]])

Replaces occurrences of a regular expression pattern in the input string with a specified value and returns the resulting string.

• expr: The string expression to be searched.
• pattern: The regular expression pattern to match.
• replacement: (optional) The string to replace each matched occurrence of pattern with. Supports backreferences \1 through \9 and escape sequences \n, \r, \t, and \\. By default, this argument is an empty string, meaning matched portions are removed from the output string.
• position: (optional) The 1-based starting position used when searching for regular expression matches in expr. The default is 1. All characters prior to the starting position are included in the output string unaltered. Skipped characters are ignored when calculating regular expression matches, even if they match pattern.
• occurrence: (optional) Specifies whether all occurrences of pattern,, or only a specific occurrence of pattern are replaced. The default is 0, which means all occurrences of pattern are replaced with replacement. Set to 1 to only replace the first instance of the pattern; 2 to replace the second; etc.
• match_type: (optional) Modifiers used to customize matching behavior. Supported values are: 'c' (case-sensitive, default), 'i' (case-insensitive), 'm' (multiline), 'n' (dot matches newline), 'x' (extended mode). These can be freely combined by including the letters back to back. For example, 'im' applies the functionality of both 'i' and 'm'. The regular expression syntax used is that of the Extended mode ('x') ignores whitespace and allows inline comments. If the pattern needs to match a literal space, it must be explicitly escaped.

                      SELECT REGEXP_REPLACE('abc123def456', '\d+', 'NUM');
                      -- Result: 'abcNUMdefNUM'

                      SELECT REGEXP_REPLACE('Hello\nHELLO\nhello', '^hello', 'X', 1, 0, 'im');
                      -- Result: 'X\nX\nX'
                    

REPLACE(string_expression, string_pattern, string_replacement)

Replaces all occurrences of a string with another string.

• string_expression: The string expression to be searched. This can be a character or binary data type.
• string_pattern: The substring to be found. Cannot be an empty string.
• string_replacement: The replacement string.

                      SELECT REPLACE('1234567890', '456', '|');
                      -- Result: '123|7890'

                      SELECT REPLACE('123123123', '123', '.');
                      -- Result: '...'

                      SELECT REPLACE('1234567890', 'a', 'b');
                      -- Result: '1234567890'
                    

REPLICATE ( string_expression ,integer_expression )

Repeats the string value the specified number of times.

• string_expression: The string to replicate.
• integer_expression: The repeat count.

                      SELECT REPLACE('x', 5);
                      -- Result: 'xxxxx'
                    

REVERSE ( string_expression )

Returns the reverse order of the string expression.

• string_expression: The string.

                      SELECT REVERSE('1234567890');
                      -- Result: '0987654321'
                    

RIGHT ( character_expression , integer_expression )

Returns the right part of the string with the specified number of characters.

• character_expression: The character expression.
• integer_expression: The positive integer that specifies how many characters of the character expression will be returned.

                      SELECT RIGHT('1234567890', 3);
                      -- Result: '890'
                    

RTRIM(character_expression)

Returns the character expression after it removes trailing blanks.

• character_expression: The character expression.

                      SELECT RTRIM('trimmed     ');
                      -- Result: 'trimmed'
                    

SOUNDEX(character_expression)

Returns the four-character Soundex code, based on how the string sounds when spoken.

• character_expression: The alphanumeric expression of character data.

                      SELECT SOUNDEX('smith');
                      -- Result: 'S530'
                    

SPACE(repeatcount)

Returns the string that consists of repeated spaces.

• repeatcount: The number of spaces.

                      SELECT SPACE(5);
                      -- Result: '     '
                    

SPLIT(string, delimiter, offset)

Returns a section of the string between to delimiters.

• string: The string to split.
• delimiter: The character to split the string with.
• offset: The number of the split to return. Positive numbers are treated as offsets from the left, and negative numbers are treated as offsets from the right.

                      SELECT SPLIT('a/b/c/d', '/', 1);
                      -- Result: 'a'
                      SELECT SPLIT('a/b/c/d', '/', -2);
                      -- Result: 'c'
                    

STARTSWITH(character_expression, character_prefix)

Returns 1 if character_expression starts with character_prefix; otherwise, 0.

• character_expression: The character expression.
• character_prefix: The character prefix to search for.

                      SELECT STARTSWITH('0123456', '012');
                      -- Result: 1

                      SELECT STARTSWITH('0123456', '456');
                      -- Result: 0
                    

STR ( float_expression [ , integer_length [ , integer_decimal ] ] )

Returns the character data converted from the numeric data. For example, STR(123.45, 6, 1) returns 123.5.

• float_expression: The float expression.
• length: The optional total length to return. This includes decimal point, sign, digits, and spaces. The default is 10.
• decimal: The optional number of places to the right of the decimal point. The decimal must be less than or equal to 16.

                      SELECT STR('123.456');
                      -- Result: '123'

                      SELECT STR('123.456', 2);
                      -- Result: '**'

                      SELECT STR('123.456', 10, 2);
                      -- Result: '123.46'
                    

STUFF(character_expression , integer_start , integer_length , replaceWith_expression)

Inserts a string into another string. It deletes the specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

• character_expression: The string expression.
• start: The integer value that specifies the location to start deletion and insertion. If start or length is negative, null is returned. If start is longer than the string to be modified, character_expression, null is returned.
• length: The integer that specifies the number of characters to delete. If length is longer than character_expression, deletion occurs up to the last character in replaceWith_expression.
• replaceWith_expression: The expression of character data that will replace length characters of character_expression beginning at the start value.

                      SELECT STUFF('1234567890', 3, 2, 'xx');
                      -- Result: '12xx567890'
                    

SUBSTRING(string_value FROM start FOR length)

Returns the part of the string with the specified length; starts at the specified index.

• string_value: The character string.
• start: The positive integer that specifies the start index of characters to return.
• length: Optional. The positive integer that specifies how many characters will be returned.

                      SELECT SUBSTRING('1234567890' FROM 3 FOR 2);
                      -- Result: '34'

                      SELECT SUBSTRING('1234567890' FROM 3);
                      -- Result: '34567890'
                    

You can also drop the FROM and FOR clauses:

                    SELECT SUBSTRING('1234567890', 3, 2)
                    --Result: '34'
                    SELECT SUBSTRING('1234567890', 3)
                    --Result: '34567890'
                    

TEXT_ENCODE(input_string, charset)

Returns binary output by encoding a string using the specified character set.

• input_string: The plain text string.
• charset: The character set to use, such as 'UTF-8', 'ISO-8859-1'.

                    SELECT TEXT_ENCODE('Café', 'UTF-8');
                    -- Result: (binary output)
                    

TEXT_DECODE(input_binary, charset)

Returns a string decoded from binary data using the specified character set.

• input_binary: The binary value to decode.
• charset: The character set used for decoding.

                    SELECT TEXT_DECODE(BinaryData, 'UTF-8');
                    -- Result: 'Café'
                    

TOSTRING(string_value1)

Converts the value of this instance to its equivalent string representation.

• string_value1: The string to be converted.

                      SELECT TOSTRING(123);
                      -- Result: '123'

                      SELECT TOSTRING(123.456);
                      -- Result: '123.456'

                      SELECT TOSTRING(null);
                      -- Result: ''
                    

TRIM(trimspec trimchar FROM string_value)

Returns the character expression with leading and/or trailing blanks removed.

• trimspec: Optional. If included must be one of the keywords BOTH, LEADING or TRAILING.
• trimchar: Optional. If included should be a one-character string value.
• string_value: The string value to trim.

                      SELECT TRIM('     trimmed     ');
                      -- Result: 'trimmed'

                      SELECT TRIM(LEADING FROM '     trimmed     ');
                      -- Result: 'trimmed     '

                      SELECT TRIM('-' FROM '-----trimmed-----');
                      -- Result: 'trimmed'

                      SELECT TRIM(BOTH '-' FROM '-----trimmed-----');
                      -- Result: 'trimmed'

                      SELECT TRIM(TRAILING '-' FROM '-----trimmed-----');
                      -- Result: '-----trimmed'
                    

UNICODE(ncharacter_expression)

Returns the integer value defined by the Unicode standard of the first character of the input expression.

• ncharacter_expression: The Unicode character expression.

UPPER ( character_expression )

Returns the character expression with lowercase character data converted to uppercase.

• character_expression: The character expression.

                      SELECT UPPER('MIXED case');
                      -- Result: 'MIXED CASE'
                    

XML_EXTRACT(xml, xpath [, separator])

Extracts an XML document using the specified XPath to flatten the XML. A comma is used to separate the outputs by default, but this can be changed by specifying the third parameter.

• xml: The XML document to extract.
• xpath: The XPath used to select the nodes. The nodes selected will be returned in a token-separated list.
• separator: The optional token used to separate the items in the flattened response. If this is not specified, the separator will be a comma.

                      SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch');
                      -- Result: 'a,e,i,o,u'

                      SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch', ';');
                      -- Result: 'a;e;i;o;u'
                    

ABS ( numeric_expression )

Returns the absolute (positive) value of the specified numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

                      SELECT ABS(15);
                      -- Result: 15

                      SELECT ABS(-15);
                      -- Result: 15
                    

ACOS ( float_expression )

Returns the arc cosine, the angle in radians whose cosine is the specified float expression.

• float_expression: The float expression that specifies the cosine of the angle to be returned. Values outside the range from -1 to 1 return null.

                      SELECT ACOS(0.5);
                      -- Result: 1.0471975511966
                    

ASIN ( float_expression )

Returns the arc sine, the angle in radians whose sine is the specified float expression.

• float_expression: The float expression that specifies the sine of the angle to be returned. Values outside the range from -1 to 1 return null.

                      SELECT ASIN(0.5);
                      -- Result: 0.523598775598299
                    

ATAN ( float_expression )

Returns the arc tangent, the angle in radians whose tangent is the specified float expression.

• float_expression: The float expression that specifies the tangent of the angle to be returned.

                      SELECT ATAN(10);
                      -- Result: 1.47112767430373
                    

ATN2 ( float_expression1 , float_expression2 )

Returns the angle in radians between the positive x-axis and the ray from the origin to the point (y, x) where x and y are the values of the two specified float expressions.

• float_expression1: The float expression that is the y-coordinate.
• float_expression2: The float expression that is the x-coordinate.

                      SELECT ATN2(1, 1);
                      -- Result: 0.785398163397448
                    

CEILING ( numeric_expression ) or CEIL( numeric_expression )

Returns the smallest integer greater than or equal to the specified numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

                      SELECT CEILING(1.3);
                      -- Result: 2

                      SELECT CEILING(1.5);
                      -- Result: 2

                      SELECT CEILING(1.7);
                      -- Result: 2
                    

COS ( float_expression )

Returns the trigonometric cosine of the specified angle in radians in the specified expression.

• float_expression: The float expression of the specified angle in radians.

                      SELECT COS(1);
                      -- Result: 0.54030230586814
                    

COT ( float_expression )

Returns the trigonometric cotangent of the angle in radians specified by float_expression.

• float_expression: The float expression of the angle in radians.

                      SELECT COT(1);
                      -- Result: 0.642092615934331
                    

DEGREES ( numeric_expression )

Returns the angle in degrees for the angle specified in radians.

• numeric_expression: The angle in radians, an expression of an indeterminate numeric data type except for the bit data type.

                      SELECT DEGREES(3.1415926);
                      -- Result: 179.999996929531
                    

EXP ( float_expression )

Returns the exponential value of the specified float expression. For example, EXP(LOG(20)) is 20.

• float_expression: The float expression.

                      SELECT EXP(2);
                      -- Result: 7.38905609893065
                    

EXPR ( expression )

Evaluates the expression.

• expression: The expression. Operators allowed are +, -, *, /, ==, !=, >, <, >=, and <=.

                      SELECT EXPR('1 + 2 * 3');
                      -- Result: 7

                      SELECT EXPR('1 + 2 * 3 == 7');
                      -- Result: true
                    

FLOOR ( numeric_expression )

Returns the largest integer less than or equal to the numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

                      SELECT FLOOR(1.3);
                      -- Result: 1

                      SELECT FLOOR(1.5);
                      -- Result: 1

                      SELECT FLOOR(1.7);
                      -- Result: 1
                    

GREATEST(int1,int2,....)

Returns the greatest of the supplied integers.

				SELECT GREATEST(3,5,8,10,1)
				-- Result: 10			
				

HEX(value)

Returns a the equivalent hex for the input value.

• value: A string or numerical value to be converted into hex.

				SELECT HEX(866849198);
				-- Result: 33AB11AE
				
				SELECT HEX('Sample Text');
				-- Result: 53616D706C652054657874
				

JSON_AVG(json, jsonpath)

Computes the average value of a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

                      SELECT JSON_AVG('[1,2,3,4,5]', '$[x]');
                      -- Result: 3

                      SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 3

                      SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 4.5
                    

JSON_COUNT(json, jsonpath)

Returns the number of elements in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

                      SELECT JSON_COUNT('[1,2,3,4,5]', '$[x]');
                      -- Result: 5

                      SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 5

                      SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 2
                    

JSON_MAX(json, jsonpath)

Gets the maximum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

                      SELECT JSON_MAX('[1,2,3,4,5]', '$[x]');
                      -- Result: 5

                      SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 5

                      SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[..3]');
                      -- Result: 4
                    

JSON_MIN(json, jsonpath)

Gets the minimum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

                      SELECT JSON_MIN('[1,2,3,4,5]', '$[x]');
                      -- Result: 1

                      SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 1

                      SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 4
                    

JSON_SUM(json, jsonpath)

Computes the summary value in JSON according to the JSONPath expression. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

                      SELECT JSON_SUM('[1,2,3,4,5]', '$[x]');
                      -- Result: 15

                      SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 15

                      SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 9
                    

LEAST(int1,int2,....)

Returns the least of the supplied integers.

				SELECT LEAST(3,5,8,10,1)
				-- Result: 1			
				

LOG ( float_expression [, base ] )

Returns the natural logarithm of the specified float expression.

• float_expression: The float expression.
• base: The optional integer argument that sets the base for the logarithm.

                      SELECT LOG(7.3890560);
                      -- Result: 1.99999998661119
                    

LOG10 ( float_expression )

Returns the base-10 logarithm of the specified float expression.

• float_expression: The expression of type float.

                      SELECT LOG10(10000);
                      -- Result: 4
                    

MOD(dividend,divisor)

Returns the integer value associated with the remainder when dividing the dividend by the divisor.

• dividend: The number to take the modulus of.
• divisor: The number to divide the dividend by when determining the modulus.

				SELECT MOD(10,3);
				-- Result: 1
				

NEGATE(real_number)

Returns the opposite to the real number input.

• real_number: The real number to find the opposite of.

				SELECT NEGATE(10);
				-- Result: -10
				
				SELECT NEGATE(-12.4)
				--Result: 12.4
				

PI ( )

Returns the constant value of pi.

                  SELECT PI()
                  -- Result: 3.14159265358979 
                

POWER ( float_expression , y )

Returns the value of the specified expression raised to the specified power.

• float_expression: The float expression.
• y: The power to raise float_expression to.

                      SELECT POWER(2, 10);
                      -- Result: 1024

                      SELECT POWER(2, -2);
                      -- Result: 0.25
                    

RADIANS ( float_expression )

Returns the angle in radians of the angle in degrees.

• float_expression: The degrees of the angle as a float expression.

                      SELECT RADIANS(180);
                      -- Result: 3.14159265358979
                    

RAND ( [ integer_seed ] )

Returns a pseudorandom float value from 0 through 1, exclusive.

• seed: The optional integer expression that specifies the seed value. If seed is not specified, a seed value at random will be assigned.

                      SELECT RAND();
                      -- This result may be different, since the seed is randomized
                      -- Result: 0.873159630165044

                      SELECT RAND(1);
                      -- This result will always be the same, since the seed is constant
                      -- Result: 0.248668584157093
                    

ROUND ( numeric_expression [ ,integer_length] [ ,function ] )

Returns the numeric value rounded to the specified length or precision.

• numeric_expression: The expression of a numeric data type.
• length: The optional precision to round the numeric expression to. When this is omitted, the default behavior will be to round to the nearest whole number.
• function: The optional type of operation to perform. When the function parameter is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

                      SELECT ROUND(1.3, 0);
                      -- Result: 1

                      SELECT ROUND(1.55, 1);
                      -- Result: 1.6

                      SELECT ROUND(1.7, 0, 0);
                      -- Result: 2

                      SELECT ROUND(1.7, 0, 1);
                      -- Result: 1
                      
                      SELECT ROUND (1.24);
                      -- Result: 1.0
                    

SIGN ( numeric_expression )

Returns the positive sign (1), 0, or negative sign (-1) of the specified expression.

• numeric_expression: The expression of an indeterminate data type except for the bit data type.

                      SELECT SIGN(0);
                      -- Result: 0

                      SELECT SIGN(10);
                      -- Result: 1

                      SELECT SIGN(-10);
                      -- Result: -1
                    

SIN ( float_expression )

Returns the trigonometric sine of the angle in radians.

• float_expression: The float expression specifying the angle in radians.

                     SELECT SIN(1);
                     -- Result: 0.841470984807897
                    

SQRT ( float_expression )

Returns the square root of the specified float value.

• float_expression: The expression of type float.

                      SELECT SQRT(100);
                      -- Result: 10
                    

SQUARE ( float_expression )

Returns the square of the specified float value.

• float_expression: The expression of type float.

                      SELECT SQUARE(10);
                      -- Result: 100

                      SELECT SQUARE(-10);
                      -- Result: 100
                    

TAN ( float_expression )

Returns the tangent of the input expression.

• float_expression: The expression of type float.

                      SELECT TAN(1);
                      -- Result: 1.5574077246549
                    

TRUNC(decimal_number,precision)

Returns the supplied decimal number truncated to have the supplied decimal precision.

• decimal_number: The decimal value to truncate.
• precision: The number of decimal places to truncate the decimal number to.

				SELECT TRUNC(10.3423,2);
				-- Result: 10.34
				

_ROW_NUMBER_()

Returns a row index as an additional column.

				SELECT ColumnName, _ROW_NUMBER_() FROM TableName
				-- Result: ColumnData, 0
				ColumnData2, 1
				ColumnData3, 2
				

CURRENT_DATE()

Returns the current date value.

                  SELECT CURRENT_DATE();
                  -- Result: 2018-02-01
                

CURRENT_TIMESTAMP()

Returns the current time stamp of the database system as a datetime value. This value is equal to GETDATE and SYSDATETIME, and is always in the local timezone.

                  SELECT CURRENT_TIMESTAMP();
                  -- Result: 2018-02-01 03:04:05
                

DATEADD (datepart , integer_number , date [, dateformat])

Returns the datetime value that results from adding the specified number (a signed integer) to the specified date part of the date.

• datepart: The part of the date to add the specified number to. The valid values and abbreviations are
  • year (yy, yyyy)
  • quarter (qq, q)
  • month (mm, m)
  • week (wk, ww)
  • weekday (dw)
  • dayofyear (dy, y)
  • day (dd, d)
  • hour (hh)
  • minute (mi, n)
  • second (ss, s)
  • millisecond (ms)
• number: The number to be added.
• date: The expression of the datetime data type.
• dateformat: The optional output date format.

                  SELECT DATEADD('d', 5, '2018-02-01');
                  -- Result: 2018-02-06

                  SELECT DATEADD('hh', 5, '2018-02-01 00:00:00');
                  -- Result: 2018-02-01 05:00:00
                

DATEDIFF ( datepart , startdate , enddate )

Returns the difference (a signed integer) of the specified time interval between the specified start date and end date.

• datepart: The part of the date that is the time interval of the difference between the start date and end date. The valid values and abbreviations are:
  • Year (year, yyyy, yy)
  • Quarter (quarter, qq, q)
  • Month (month, mm, m)
  • Week (week, wk, ww)
  • Weekday (weekday, dw)
  • Dayofyear (dayofyear, dy, y)
  • Day (day, dd, d)
  • Hour (hour, hh)
  • Minute (minute, mi, n)
  • Second (second, ss, s)
  • Millisecond (millisecond, ms)
• startdate: The datetime expression of the start date.
• enddate: The datetime expression of the end date.

                  SELECT DATEDIFF('d', '2018-02-01', '2018-02-10');
                  -- Result: 9

                  SELECT DATEDIFF('hh', '2018-02-01 00:00:00', '2018-02-01 12:00:00');
                  -- Result: 12
                

DATE_FORMAT(date,format)

Returns the date or timestamp in the format specified. This function mirrors the MySQL DATE_FORMAT function.

• date: A date or timestamp string.
• format: The specifier string of the desired output format. The list of supported format specifiers comes from the MySQL DATE_FORMAT function (see link to MySQL documentation above).

					SELECT DATE_FORMAT('9/4/2021 3:11:53 AM','%h')
					-- Result: 03
				  

DATEFROMPARTS(integer_year, integer_month, integer_day)

Returns the datetime value for the specified year, month, and day.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.

                    SELECT DATEFROMPARTS(2018, 2, 1);
                    -- Result: 2018-02-01
                  

DATENAME(datepart , date)

Returns the character string that represents the specified date part of the specified date.

• datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), and nanosecond (ns).
• date: The datetime expression.

                     SELECT DATENAME('yy', '2018-02-01');
                     -- Result: '2018'

                     SELECT DATENAME('dw', '2018-02-01');
                     -- Result: 'Thursday'
                   

DATEPART(datepart, date [,integer_datefirst])

Returns a character string that represents the specified date part of the specified date.

• datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns), ISODOW, ISO_WEEK (isoweek, isowk,isoww), and ISOYEAR.
• date: The datetime string.
• datefirst: The optional integer representing the first day of the week. The default is 7, Sunday.

                    SELECT DATEPART('yy', '2018-02-01');
                    -- Result: 2018

                    SELECT DATEPART('dw', '2018-02-01');
                    -- Result: 5
                  

DATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_milliseconds)

Returns the datetime value for the specified date parts.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• milliseconds: The integer expression specifying the milliseconds.

                    SELECT DATETIMEFROMPARTS(2018, 2, 1, 1, 2, 3, 456);
                    -- Result: 2018-02-01 01:02:03.456
                  

DATETIME2FROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Returns the datetime value for the specified date parts.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• fractions: The integer expression specifying the fractions of the second.
• precision: The integer expression specifying the precision of the fraction.

				    SELECT DATETIME2FROMPARTS(2018, 2, 1, 1, 2, 3, 456, 3);
                    -- Result: 2018-02-01 01:02:03.456
                  

DATE_TRUNC(date, datepart)

Truncates the date to the precision of the given date part. Modeled after the Oracle TRUNC function.

• date: The datetime string that specifies the date.
• datepart: Refer to the Oracle documentation for valid datepart syntax.

				    SELECT DATE_TRUNC('05-04-2005', 'YY');
                    -- Result: '1/1/2005'
					
                    SELECT DATE_TRUNC('05-04-2005', 'MM');
                    -- Result: '5/1/2005'                    
                  

DATE_TRUNC2(datepart, date, [weekday])

Truncates the date to the precision of the given date part. Modeled after the PostgreSQL date_trunc function.

• datepart: One of 'millennium', 'century', 'decade', 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute' or 'second'.
• date: The datetime string that specifies the date.
• weekday: The optional day of the week to use as the first day for 'week'. One of 'sunday', 'monday', etc.

                    SELECT DATE_TRUNC2('year', '2020-02-04');
                    -- Result: '2020-01-01'

                    SELECT DATE_TRUNC2('week', '2020-02-04', 'monday');
                    -- Result: '2020-02-02', which is the previous Monday
                  

DAY(date)

Returns the integer that specifies the day component of the specified date.

• date: The datetime string that specifies the date.

                    SELECT DAY('2018-02-01');
                    -- Result: 1
                  

DAYNAME(date)

Returns the name of the day of the week of the specified date.

• date: The datetime string that specifies the date.

                    SELECT DAYNAME('8/18/2021');
                    -- Result: Wednesday
                  

DAYOFMONTH(date)

Returns the day of the month of the given date part.

• date: The datetime string that specifies the date.

				  SELECT DAYOFMONTH('04/15/2000');
				  -- Result: 15
				  

DAYOFWEEK(date)

Returns the day of the week of the given date part.

• date: The datetime string that specifies the date.

				  SELECT DAYOFWEEK('04/15/2000');
				  -- Result: 7
				  

DAYOFYEAR(date)

Returns the day of the year of the given date part.

• date: The datetime string that specifies the date.

				  SELECT DAYOFYEAR('04/15/2000');
				  -- Result: 106
				  

EOMONTH(date [, integer_month_to_add ]) or LAST_DAY(date)

Returns the last day of the month that contains the specified date with an optional offset.

• date: The datetime expression specifying the date for which to return the last day of the month.
• integer_month_to_add: The optional integer expression specifying the number of months to add to the date before calculating the end of the month.

                  SELECT EOMONTH('2018-02-01');
                  -- Result: 2018-02-28
                  
                  SELECT LAST_DAY('2018-02-01');
                  -- Result: 2018-02-28

                  SELECT EOMONTH('2018-02-01', 2);
                  -- Result: 2018-04-30
                

EXTRACT(date_part FROM date_column_name)

Returns the last day of the month that contains the specified date with an optional offset.

• date_part: One of the following date components: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
• date_column_name: The name of a date column in a table.

                  SELECT EXTRACT(YEAR FROM DateColumn)
                  -- Result: 2021
                

FDWEEK(date)

Returns the first day of the week of the given date part.

• date: The datetime string that specifies the date.
• weeks to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the week.

				  SELECT FDWEEK('02-08-2018');
				  -- Result: 2/4/2018

          SELECT FDWEEK('02-08-2018', 1)
          --Result: 02/11/2018
				  

FDMONTH(date)

Returns the first day of the month of the given date part.

• date: The datetime string that specifies the date.
• month to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the month.

				  SELECT FDMONTH('02-08-2018');
				  -- Result: 2/1/2018

          SELECT FDMONTH('02-08-2018', 1) 
          --Result: 03/01/2018
				  

FDQUARTER(date)

Returns the first day of the quarter of the given date part.

• date: The datetime string that specifies the date.
• quarters to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the quarter.

				  SELECT FDQUARTER('05-08-2018');
				  -- Result: 4/1/2018

          SELECT FDQUARTER('05-08-2018',1)
          --Result: 07/01/2018
				  

FILEMODIFIEDTIME(uri)

Returns the time stamp associated with the Date Modified of the relevant file.

• uri: An absolute path pointing to a file on the local file system.

				 SELECT FILEMODIFIEDTIME('C:/Documents/myfile.txt');
				 -- Result: 6/25/2019 10:06:58 AM
				 

FROM_DAYS(datevalue)

Returns a date derived from the number of days after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL FROM_DAYS function.

• datevalue: A integer value representing the number of days since 1582-10-15.

				SELECT FROM_DAYS(736000);
				-- Result: 2/6/2015
				

FROM_UNIXTIME(time, issecond)

Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS expressed in the current time zone.

• time: The time stamp value from epoch time. Milliseconds are accepted.
• issecond: Indicates the time stamp value is milliseconds to epoch time.

                      SELECT FROM_UNIXTIME(1540495231, 1);
                      -- Result: 2018-10-25 19:20:31

                      SELECT FROM_UNIXTIME(1540495357385, 0);
                      -- Result: 2018-10-25 19:22:37
                    

GETDATE()

Returns the current time stamp of the database system as a datetime value. This value is equal to CURRENT_TIMESTAMP and SYSDATETIME, and is always in the local timezone.

                  SELECT GETDATE();
                  -- Result: 2018-02-01 03:04:05
                

GETUTCDATE()

Returns the current time stamp of the database system formatted as a UTC datetime value. This value is equal to SYSUTCDATETIME.

In addition, GETUTCDATE can take an optional second parameter, a date and time that are converted to UTC.

                  SELECT GETUTCDATE();
                  -- For example, if the local timezone is Eastern European Time (GMT+2)
                  -- Result: 2018-02-01 05:04:05

                  SELECT GETUTCDATE('2020/08/31 13:56:00')
                  --Result: '2020-08-31 17:56:00'
                

HOUR(date)

Returns the hour component from the provided datetime.

• date: The datetime string that specifies the date.

				SELECT HOUR('02-02-2020 11:30:00');
				-- Result: 11
				

ISDATE(date, [date_format])

Returns 1 if the value is a valid date, time, or datetime value; otherwise, 0.

• date: The datetime string.
• date_format: The optional datetime format.

                      SELECT ISDATE('2018-02-01', 'yyyy-MM-dd');
                      -- Result: 1

                      SELECT ISDATE('Not a date');
                      -- Result: 0
                    

LAST_WEEK()

Returns a time stamp equivalent to exactly one week before the current date.

				SELECT LAST_WEEK();	//Assume the date is 3/17/2020	
			 -- Result: 3/10/2020 00:00:00
				

LAST_MONTH()

Returns a time stamp equivalent to exactly one month before the current date.

	
				SELECT LAST_MONTH(); //Assume the date is 3/17/2020
				-- Result: 2/17/2020 00:00:00
				

LAST_YEAR()

Returns a time stamp equivalent to exactly one year before the current date.

				SELECT LAST_YEAR();	//Assume the date is 3/17/2020	
				-- Result: 3/10/2019 00:00:00
				

LDWEEK(date)

Returns the last day of the provided week.

• date: The datetime string.
• weeks to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the week.

				SELECT LDWEEK('02-02-2020');
				-- Result: 2/8/2020
				

LDMONTH(date)

Returns the last day of the provided month.

• date: The datetime string.
• months to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the month.

				SELECT LDMONTH('02-02-2020');
				-- Result: 2/29/2020

        SELECT LDMONTH('02-08-2020', 1)
        --Result: 03/31/2020
				

LDQUARTER(date)

Returns the last day of the provided quarter.

• date: The datetime string.
• quarters to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the quarter.

				SELECT LDQUARTER('02-02-2020');
				-- Result: 3/31/2020

        SELECT LDQUARTER('02-02-2020',1)
        --Result: 06/30/2020
				

MAKEDATE(year, days)

Returns a date value from a year and a number of days.

• year: The year
• days: The number of days into the year. Value must be greater than 0.

          SELECT MAKEDATE(2020, 1);
          -- Result: 2020-01-01
        

MINUTE(date)

Returns the minute component from the provided datetime.

• date: The datetime string that specifies the date.

				SELECT MINUTE('02-02-2020 11:15:00');
				-- Result: 15
				

MONTH(date)

Returns the month component from the provided datetime.

• date: The datetime string that specifies the date.

				SELECT MONTH('02-02-2020');
				-- Result: 2
				

QUARTER(date)

Returns the quarter associated with the provided datetime.

• date: The datetime string that specifies the date.

				SELECT QUARTER('02-02-2020');
				-- Result: 1
				

SECOND(date)

Returns the second component from the provided datetime.

• date: The datetime string that specifies the date.

				SELECT SECOND('02-02-2020 11:15:23');
				-- Result: 23
				

SMALLDATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute)

Returns the datetime value for the specified date and time.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.

                      SELECT SMALLDATETIMEFROMPARTS(2018, 2, 1, 1, 2);
                      -- Result: 2018-02-01 01:02:00
                    

STRTODATE(string,format)

Parses the provided string value and returns the corresponding datetime.

• string: The string value to be converted to datetime format.
• format: A format string which describes how to interpret the first string input. A few special formats are available as well, including UNIX, UNIXMILIS, TICKS, and FILETICKS.

				SELECT STRTODATE('03*04*2020','dd*MM*yyyy');
				-- Result: 4/3/2020
				

SYSDATETIME()

Returns the current time stamp as a datetime value of the database system. It is equal to GETDATE and CURRENT_TIMESTAMP, and is always in the local timezone.

                  SELECT SYSDATETIME();
                  -- Result: 2018-02-01 03:04:05
                

SYSUTCDATETIME()

Returns the current system date and time as a UTC datetime value. It is equal to GETUTCDATE.

                  SELECT SYSUTCDATETIME();
                  -- For example, if the local timezone is Eastern European Time (GMT+2)
                  -- Result: 2018-02-01 05:04:05
                

TIMEFROMPARTS(integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Returns the time value for the specified time and with the specified precision.

• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• fractions: The integer expression specifying the fractions of the second.
• precision : The integer expression specifying the precision of the fraction.

                      SELECT TIMEFROMPARTS(1, 2, 3, 456, 3);
                      -- Result: 01:02:03.456
                    

TO_DAYS(date)

Returns the number of days since 0000-00-01. This will only return a value for dates on or after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL TO_DAYS function.

• date: The datetime string that specifies the date.

				SELECT TO_DAYS('02-06-2015');
				-- Result: 736000
				

WEEK(date)

Returns the week (of the year) associated with the provided datetime.

• date: The datetime string that specifies the date.

				SELECT WEEK('02-17-2020 11:15:23');
				-- Result: 8
				

YEAR(date)

Returns the integer that specifies the year of the specified date.

• date: The datetime string.

                      SELECT YEAR('2018-02-01');
                      -- Result: 2018
                    

The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.

L_TODAY()

The current day.

  SELECT * FROM MyTable WHERE MyDateField = L_TODAY()

L_YESTERDAY()

The previous day.

  SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()

L_TOMORROW()

The following day.

  SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()

L_LAST_WEEK()

Every day in the preceding week.

  SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()

L_THIS_WEEK()

Every day in the current week.

  SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()

L_NEXT_WEEK()

Every day in the following week.

  SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()

Also available:

• L_LAST/L_THIS/L_NEXT MONTH
• L_LAST/L_THIS/L_NEXT QUARTER
• L_LAST/L_THIS/L_NEXT YEAR

L_LAST_N_DAYS(n)

The previous n days, excluding the current day.

  SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)

L_NEXT_N_DAYS(n)

The following n days, including the current day.

  SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)

Also available:

• L_LAST/L_NEXT_90_DAYS

L_LAST_N_WEEKS(n)

Every day in every week, starting n weeks before current week, and ending in the previous week.

  SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)

L_NEXT_N_WEEKS(n)

Every day in every week, starting the following week, and ending n weeks in the future.

  SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)

Also available:

• L_LAST/L_NEXT_N_MONTHS(n)
• L_LAST/L_NEXT_N_QUARTERS(n)
• L_LAST/L_NEXT_N_YEARS(n)

A SELECT statement can consist of the following basic clauses.

• SELECT
• INTO
• FROM
• JOIN
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY
• LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the connector:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  { 
    FROM <table_reference> [ [ AS ] <identifier> ] 
  } [ , ... ]
  [ [  
      INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } 
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ] 
  ] [ ... ] 
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ HAVING <search_condition> ]
  [ UNION [ ALL ] <select_statement> ]
  [ 
    ORDER BY 
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [ 
    LIMIT <expression>
    [ 
      { OFFSET | , }
      <expression> 
    ]
  ] 
} | SCOPE_IDENTITY() 

<expression> ::=
  | <column_reference>
  | @ <parameter> 
  | ?
  | COUNT( * | { [ DISTINCT ] <expression> } )
  | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) 
  | NULLIF ( <expression> , <expression> ) 
  | COALESCE ( <expression> , ... ) 
  | CASE <expression>
      WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
    [ ELSE { <expression> | NULL } ]
    END 
  | {RANK() | DENSE_RANK()} OVER ([PARTITION BY <column_reference>] {ORDER BY <column_reference>})
  | <literal>
  | <sql_function> 

<search_condition> ::= 
  {
    <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN | IS DISTINCT FROM | IS NOT DISTINCT FROM } [ <expression> ]
  } [ { AND | OR } ... ] 

Examples

1. Return all columns:
   

   SELECT * FROM Subscriber

2. Rename a column:
   

   SELECT [Status] AS MY_Status FROM Subscriber

3. Cast a column's data as a different data type:
   

   SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Subscriber

4. Search data:
   

   SELECT * FROM Subscriber WHERE EmailAddress = 'john.doe@example.com'

5. Return the number of items matching the query criteria:
   

   SELECT COUNT(*) AS MyCount FROM Subscriber 

6. Return the number of unique items matching the query criteria:
   

   SELECT COUNT(DISTINCT Status) FROM Subscriber 

7. Return the unique items matching the query criteria:
   

   SELECT DISTINCT Status FROM Subscriber 

8. Sort a result set in ascending order:
   

   SELECT Id, Status FROM Subscriber  ORDER BY Status ASC

9. Restrict a result set to the specified number of rows:
   

   SELECT Id, Status FROM Subscriber LIMIT 10 

10. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
    

    SELECT * FROM Subscriber WHERE EmailAddress = @param

See Explicitly Caching Data for information on using the SELECT statement in offline mode.

Pseudo Columns

Some input-only fields are available in SELECT statements. These fields, called pseudo columns, do not appear as regular columns in the results, yet may be specified as part of the WHERE clause. You can use pseudo columns to access additional features from Salesforce Marketing Cloud.

    SELECT * FROM Subscriber WHERE Pseudo = '@Pseudo'
    

Aggregate Functions

For SELECT examples using aggregate functions, see Aggregate Functions.

JOIN Queries

See JOIN Queries for SELECT query examples using JOINs.

Date Literal Functions

Date Literal Functions contains SELECT examples with date literal functions.

Window Functions

See Window Functions for SELECT examples containing window functions.

Table-Valued Functions

See Table-Valued Functions for SELECT examples with table-valued functions.

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM Subscriber WHERE EmailAddress = 'john.doe@example.com'

COUNT(DISTINCT)

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT(DISTINCT Id) AS DistinctValues FROM Subscriber WHERE EmailAddress = 'john.doe@example.com'

AVG

Returns the average of the column values.

SELECT Status, AVG(AnnualRevenue) FROM Subscriber WHERE EmailAddress = 'john.doe@example.com'  GROUP BY Status

MIN

Returns the minimum column value.

SELECT MIN(AnnualRevenue), Status FROM Subscriber WHERE EmailAddress = 'john.doe@example.com' GROUP BY Status

MAX

Returns the maximum column value.

SELECT Status, MAX(AnnualRevenue) FROM Subscriber WHERE EmailAddress = 'john.doe@example.com' GROUP BY Status

SUM

Returns the total sum of the column values.

SELECT SUM(AnnualRevenue) FROM Subscriber WHERE EmailAddress = 'john.doe@example.com'

The CData Python Connector for Salesforce Marketing Cloud supports standard SQL joins like the following examples.

Inner Join

An inner join selects only rows from both tables that match the join condition:

SELECT ListSend.NumberSent, Send.Id FROM ListSend, Send WHERE ListSend.SendId=Send.Id

Left Join

A left join selects all rows in the FROM table and only matching rows in the JOIN table:

SELECT ListSend.NumberSent, Send.Id FROM ListSend LEFT OUTER JOIN SendId ON ListSend.SendId=Send.Id

Window functions allow you to create computed fields from a group of rows (a window) that return a result for each row, as opposed to one computed result for a set of rows, as is the case with aggregate functions. The connector supports the following window function syntax.

Note: Window function support is an experimental feature of the connector. This functionality extends beyond the connector's core scope of being SQL-92 compliant. As such, performance with window functions may not be optimal.

Window Function Clauses

OVER

The OVER clause defines the window over which window functions are performed.

SELECT A, B, <window function> OVER (<window frame>) FROM TableName

The <window function> refers to any supported window function clause, and the <window frame> refers to one or more clauses that specify the logic by which the window is defined.

PARTITION BY

The PARTITION BY clause subdivides a window into sub-windows called partitions. For each unique value in the column specified in the PARTITION BY clause, every record with that value collectively forms an individual partition.

SELECT A, B, <window function> OVER (PARTITION BY A ORDER BY B) From Subscriber

The <window function> refers to any supported window function clause.

Window Functions

The connector supports math, ranking, and analytic window functions.

Math

These window functions perform mathematical operations on the records within the window.

COUNT()

Calculates the number of records in each partition. The calculated column is of the data type "int".

In each partition, every record will display the total number of records in that partition.

SELECT Name, Role, Earnings, COUNT() OVER (PARTITION BY Role) FROM Employees

COUNT_BIG()

Calculates the number of records in each partition. The calculated column is of the data type "bigint".

In each partition, every record will display the total number of records in that partition.

SELECT Name, Role, Earnings, COUNT_BIG() OVER (PARTITION BY Role) FROM Employees

MIN(numeric_column)

Calculates the minimum value of a numerical column per partition.

In each partition, every record will display the minimum value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MIN(Earnings) OVER (PARTITION BY Role) FROM Employees

MAX(numeric_column)

Calculates the maximum value of a numerical column per partition.

In each partition, every record will display the maximum value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MAX(Earnings) OVER (PARTITION BY Role) FROM Employees

SUM(numeric_column)

Calculates the sum of a numerical column per partition.

In each partition, every record will display the sum of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, SUM(Earnings) OVER (PARTITION BY Role) FROM Employees

AVG(numeric_column)

Calculates the average value of a numerical column per partition.

In each partition, every record will display the average value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, AVG(Earnings) OVER (PARTITION BY Role) FROM Employees

MEDIAN(numeric_column)

Calculates the median value of a numerical column per partition.

In each partition, every record will display the median value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MEDIAN(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEV(numeric_column)

Calculates the standard deviation of a numerical column per partition.

In each partition, every record will display the standard deviation of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, STDEV(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEVP(numeric_column)

Calculates the population standard deviation of a numerical column per partition.

In each partition, every record will display the population standard deviation of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, STDEVP(Earnings) OVER (PARTITION BY Role) FROM Employees

VAR(numeric_column)

Calculates the statistical standard variance of a numerical column per partition.

In each partition, every record will display the statistical standard variance of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, VAR(Earnings) OVER (PARTITION BY Role) FROM Employees

VARP(numeric_column)

Calculates the variance population of a numerical column per partition.

In each partition, every record will display the variance population of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, VARP(Earnings) OVER (PARTITION BY Role) FROM Employees

Ranking

These window functions rank records that fall within the window and its partitions.

RANK()

Assigns a rank number to each record in a window based on the value of the column specified in the required ORDER BY clause.

If two or more records have an equal value in the in ranked column, they all receive the same rank number and the rank count increments internally, skipping ahead one rank number for each record with a duplicate value in the ORDER BY column.

SELECT Id, Status, RANK() OVER (ORDER BY Status) AS Rank FROM Subscriber

If you add a PARTITION BY clause, a separate set of ranks is calculated for each partition.

SELECT Id, Status, RANK() OVER (PARTITION BY Id ORDER BY Status) AS Rank FROM Subscriber

DENSE_RANK()

Operates like the RANK() function, but it doesn't increment the internal rank counter for each record with a duplicate value in the ranked column.

This means that, while records with identical values in the ORDER BY column still share a rank number, the function never skips a rank number.

SELECT Id, Status, DENSE_RANK() OVER (PARTITION BY Id ORDER BY Status) AS Rank FROM Subscriber

If you add a PARTITION BY clause, a separate set of ranks is calculated for each partition.

SELECT Id, Status, DENSE_RANK() OVER (PARTITION BY Id ORDER BY Status) AS Rank FROM Subscriber

ROW_NUMBER()

Calculates a row number for each record. An ORDER BY clause in the OVER clause is required.

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (ORDER BY Role) FROM Employees

If you define multiple partitions with PARTITION BY, a new set of row numbers are calculated for each partition.

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (PARTITION BY Role ORDER BY Earnings) FROM Employees

NTILE()

Distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.

The syntax of NTILE() is:

NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

The following are paramaters that NTILE() supports:

• buckets: The number of buckets into which the rows are divided. The buckets can be an expression or subquery that evaluates to a positive integer. It cannot be a window function.
• PARTITION BY: distributes rows of a result set into partitions to which the NTILE() function is applied.
• ORDER BY is clause that specifies the logical order of rows in each partition to which the NTILE() is applied.

If the number of rows is not divisible by the buckets, the NTILE() function returns groups of two sizes with the difference by one. The larger groups always precede the smaller group in the order set by ORDER BY in the OVER() clause.

If the total of rows is divisible by the number of buckets, the function divides the rows evenly among buckets. The following statement creates a new table named ntile_demo that stores 10 integers:

CREATE TABLE sales.ntile_demo (
	v INT NOT NULL
);
	
INSERT INTO sales.ntile_demo(v) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);	
	
SELECT * FROM sales.ntile_demo;

This statement uses the NTILE() function to divide ten rows into three groups:

SELECT 
	v, 
	NTILE (3) OVER (
		ORDER BY v
	) buckets
FROM 
	sales.ntile_demo;

Analytical

These window functions perform analytical operations on the records within the window.

PERCENT_RANK()

Calculates the relative rank SQL Percentile of each row. It returns values greater than zero, but the maximum value is one. It does not count any NULL values. This function is nondeterministic.

The syntax of PERCENT_RANK() is:

PERCENT_RANK() OVER (
      [PARTITION BY partition_expression, ... ]
      ORDER BY sort_expression [ASC | DESC], ...
  )
  

This syntax uses the following parameters.

• PARTITION BY: By default, SQL Server treats the whole data set as a single set. You can specify the PARTITION BY clause to divide data into multiple sets. The Percent_Rank function performs the analytical calculations on each set. This parameter is optional.
• ORDER BY: Sorts the data in either ascending or descending order. This parameter is required.

Table-valued functions are functions that return a table (rowset).

Note: Table-valued function support is an experimental feature of the connector. This functionality extends beyond the connector's core scope of being SQL-92 compliant. As such, performance with these functions may not be optimal.

Table-Valued Function Clauses

CROSS APPLY

The CROSS APPLY operator is used to perform a subquery on each row of a table or resultset produced by a preceding table expression.

<table_expression_1> CROSS APPLY <table_expression_2>

The second table expression can reference results from the first table expression to create derived columns or an altered recordset via a table-valued function.

Each resulting record is an instance of the record it's splitting, with all the same column values, except for the column(s) containing the value split by the function.

WITH

The WITH clause is used alongside certain table-valued functions to match against constructs within the structure being split (keys, element names, attribute names, etc.) and/or to specify metadata for the columns generated from the function.

SELECT A.ColumnName, X.DerivedColumnName FROM TableName A CROSS APPLY <table-valued function> WITH (DerivedColumnName varchar(255)) AS X

Table-Valued Functions

STRING_SPLIT(input_text,delimiter)

Takes each record in the recordset of the preceding table expression, splits the column containing delimiters (input_text) into substrings separated by the delimiter, and returns one record per substring.

• input_text: A column whose value you want to parse.
• delimiter: The character used to split the value of the column specified in input_text.

Suppose there is a column called "SplitColumn" with the following content:

One-Two-Three

To unpack this value across multiple records:

SELECT A.ID, X.Value FROM [TableWithDelimitedStringField] A CROSS APPLY STRING_SPLIT(A.SplitColumn,'-') WITH (Value VARCHAR(255)) AS X

-- Results:
-----------
|ID|Value|
|1|One|
|1|Two|
|1|Three|

JSONTABLE(json_content,[jsonpath])

For each record in the recordset of the preceding table expression, returns one record for each instance of a key in a JSON array (json_content) that matches the key(s) specified in the WITH clause, at the scope specified by the "jsonpath" input.

• json_content: A JSON "table" (array of objects). The contents can nest, but this must be a single JSON array, not any other JSON structure, at the root level.
  • The values of every instance of the key(s) provided in the WITH clause are retrievable only for substructures which are immediate children of the root-level JSON array.
• jsonpath: An optional JSONPath query defining the scope, within the json_content array, that you want to pull content from. The JSON key(s) identified in the WITH clause must exist at the scope defined in this parameter. This defaults to the JSON root ($).

Consider a sample table with a single record, including an ID column and column with JSON content called "JSONColumn" with the following content:

[
	{
		"name": "Samuel",
		"email": "sam@gmail.com",
		"extrainfo": {
			"city": "Seattle"
		}
	},
	{
		"name": "Katherine",
		"email": "kat@gmail.com",
	},
	{
		"name": "George",
		"email": "george23@gmail.com",
	},
	{
		"name": "Carlos",
		"email": "carlos32@gmail.com",
	}
]

To extract all values for a certain key, specify the scope in the JSONTABLE function and provide the desired key(s) in the WITH clause.

SELECT A.ID, X.name FROM [TableWithJSONField] A CROSS APPLY JSONTABLE(A.JSONColumn) WITH (name VARCHAR(255)) AS X

-- Results: 
|ID|name|
---------
|1 |Samuel|
|1 |Katherine|
|1 |George|
|1 |Carlos|

XMLTABLE(xml_content,[xpath,child_type])

For each record in the resultset of the preceding table expression, returns one record for each of the elements and/or attributes in an XML structure (xml_content) that match the tag name(s) and/or attribute name(s) specified in the WITH clause, at the scope specified in the "xpath" input.

• xml_content: A column containing an XML structure.
• xpath: An optional XPath that specifies the scope within the XML structure at which the connector extracts content matching the tag/attribute name(s) specified in the WITH clause.
  • When extracting the content of sub-elements, the connector can retrieve all content from tags at the root level, (depth 0) immediate children of the root (depth 1), and children of those children (depth 2).
  • When extracting element attribute content, the connector can retrieve all content from tags containing the specified attribute at the root level (depth 0) and from immediate children of root-level elements (depth 1).
• child_type: An optional parameter that specifies the part(s) of the parent element (specified in the xpath input) that the column(s) provided in the WITH clause are checked against to identify content.
  • You can supply the following values:
    • 0: The column(s) in the WITH clause are checked for matches against the parent element's attribute names and sub-element tag names.
    • 1: The column(s) in the WITH clause are checked for matches against the parent element's attribute names.
    • 2: The column(s) in the WITH clause are checked for matches against the parent element's sub-element tag names.
  • When not supplied, this defaults to 0.

Extracting Sub-Element Values

Consider a sample table with a single record, including an ID column and a column with XML content called "XMLContent" with the following content:

<shoppingList>
    <item>
        <name>Apples</name>
        <quantity>3</quantity>
        <unit>Kg</unit>
    </item>
    <item>
        <name>Bread</name>
        <quantity>2</quantity>
        <unit>Loaf</unit>
		<extrainfo>
			<Type>Whole-Grain</Type>
		</extrainfo>
    </item>
    <item>
        <name>Milk</name>
        <quantity>1</quantity>
        <unit>Carton</unit>
    </item>
    <item>
        <name>Eggs</name>
        <quantity>12</quantity>
        <unit></unit>
    </item>
</shoppingList>

To extract sub-element content, specify the scope in the XMLTABLE function and provide the desired element name(s) in the WITH clause. Note that this will not work if the XMLTABLE function's child_type input is set to 1.

SELECT A.ID, X.name FROM [TableWithXMLField] A CROSS APPLY XMLTABLE(A.XMLContent,'//*/item') WITH (name VARCHAR(255)) AS X

-- Results: 
|ID|name|
---------
|1|Apples|
|1|Bread|
|1|Milk|
|1|Eggs|

Extracting Values Using Element Tag Attributes

Suppose you have this sample table with a single record, including an ID column and a column with XML content called "XMLContent" with the following content:

<restaurant>
  <dish type="appetizer">
    <name lang="en">Caprese Salad</name>
    <chef>Chef Giovanni</chef>
    <price currency="USD">9.99</price>
  </dish>
  <dish type="main-course">
    <name lang="fr">Boeuf Bourguignon</name>
    <chef>Chef Marie</chef>
    <price currency="EUR">19.99</price>
  </dish>
  <dish type="dessert">
    <name lang="es">Tres Leches Cake</name>
    <chef>Chef Alejandro</chef>
    <price currency="MXN">89.99</price>
  </dish>
</restaurant>

To extract attribute content, specify the scope in the XMLTABLE function and provide the desired attribute name(s) in the WITH clause. Note that this will not work if the XMLTABLE function's child_type input is set to 2.

SELECT A.ID, X.type FROM [TableWithXMLField] A CROSS APPLY XMLTABLE(A.XMLContent,'//*/dish') WITH (type VARCHAR(255)) AS X

-- Results: 
|ID|type|
---------
|1|appetizer|
|1|main-course|
|1|dessert|

CSVTABLE(csv_content,[delimiter])

For each record in the resultset of the preceding table expression, reads from a column that contains a CSV table (csv_content) and for each record in that CSV table, returns one record containing the value of the CSV column(s) specified in the WITH clause.

• csv_content: A column containing a CSV table.
• delimiter: An optional custom delimiter (instead of a comma) which splits the CSV content contained in the csv_content input.

Consider a sample table with a single record, including an ID column and a column containing CSV table called "CSVContent" with the following content:

Name;Category;Price
Apple;Fruit;0.99
Spaghetti;Pasta;5.49
Chicken Breast;Meat;8.99
Broccoli;Vegetable;2.49

To select every value in the "Name" column and account for the custom delimiter (;):

SELECT A.ID, X.Name FROM [TableWithCSVField] A CROSS APPLY CSVTABLE(A.CSVContent,';') WITH (Name VARCHAR(255)) AS X

-- Results:
|ID|Name|
-----------
|1|Apple|
|1|Spaghetti|
|1|Chicken Breast|
|1|Broccoli|

To create new records, use INSERT statements.

INSERT Syntax

The INSERT statement specifies the columns to be inserted and the new column values. You can specify the column values in a comma-separated list in the VALUES clause, as shown in the following example:

INSERT INTO <table_name> 
( <column_reference> [ , ... ] )
VALUES 
( { <expression> | NULL } [ , ... ] ) 
  

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

The following is an example query:

INSERT INTO Subscriber (Status) VALUES ('John')

To modify existing records, use UPDATE statements.

Update Syntax

The UPDATE statement takes as input a comma-separated list of columns and new column values as name-value pairs in the SET clause, as shown in the following example:

UPDATE <table_name> SET <select_statement> | {<column_reference> = <expression> [ , ... ]} WHERE { Id = <expression>  } [ { AND | OR } ... ] 

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

The following is an example query:

UPDATE Subscriber SET Status='John' WHERE Id = @myId

To delete information from a table, use DELETE statements.

DELETE Syntax

The DELETE statement requires the table name in the FROM clause and the row's primary key in the WHERE clause, as shown in the following example:

<delete_statement> ::= DELETE FROM <table_name> WHERE { Id = <expression> } [ { AND | OR } ... ]

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

The following is an example query:

DELETE FROM Subscriber WHERE Id = @myId

When caching is enabled, CACHE statements provide complete control over the data that is cached and the table to which it is cached. The CACHE statement executes the SELECT statement specified and caches its results to a table with the same name in the cache database or to table specified in <cached_table_name>. The connector updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns.

See Caching Data for more information on different caching strategies.

CACHE Statement Syntax

The cache statement may include the following options that alter its behavior:

CACHE [ <cached_table_name> ] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] <select_statement> 

WITH TRUNCATE

If this option is set, the connector removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.

AUTOCOMMIT

If this option is set, the connector commits each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.

DROP EXISTING

If this option is set, the connector drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.

SCHEMA ONLY

If this option is set, the connector creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA

If this option is set, the connector alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option results in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.

Common Queries

Use the following cache statement to cache all rows of a table:

CACHE SELECT * FROM Subscriber

Use the following cache statement to cache all rows of a table into the cache table CachedSubscriber:

CACHE CachedSubscriber SELECT * FROM Subscriber

Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The cache statement shows how incremental caching would work if there were such a column. Also, notice that, in this case, the WITH TRUNCATE and DROP EXISTING options are specifically omitted, which would have deleted all existing rows.

CACHE CachedSubscriber SELECT * FROM Subscriber WHERE DateModified > '2013-04-04'

Use the following cache statements to create a table with all available columns that will then cache only a few of them. The sequence of statements cache only Id and Status even though the cache table CachedSubscriber has all the columns in Subscriber.

CACHE CachedSubscriber SCHEMA ONLY SELECT * FROM Subscriber
CACHE CachedSubscriber SELECT Id, Status FROM Subscriber

Use INSERT INTO SELECT queries to select a list of records from one table and insert those same records into another table as a group. Inserting batches of records in this way may result in improved query performance compared to using many individual INSERT INTO queries.

The table whose records are selected for insertion into another table can be either a real table or a user-defined temporary table.

Inserting Records from Real Tables

To insert a group of records from one real, non-temporary, source table into another destination table, you can use an INSERT INTO SELECT query. This type of query is formatted similarly to a standard INSERT INTO query, except the VALUES clause is substituted with a SELECT query targeting the source table. All records matched by the embedded SELECT query are inserted into the destination table.

If the source table and destination tables have different column names, you must map columns from the source table to the corresponding columns in the destination table you want to insert them into. Perform this mapping by specifying the destination table columns in the same order as the source table columns you want to match them with. For example:

INSERT INTO DestinationTable (A,B,C,D) SELECT Q,R,S,T FROM SourceTable

In this example, the first source column (Q) is inserted into the first destination column (A), the second source column (R) is inserted into the second destination column (B), and so on.

If the source table and destination table both have the same column list with the same names, you can use a streamlined query.

INSERT INTO DestinationTableWithSameColumns SELECT * FROM SourceTable

In this example, there is no need to specify a list of columns for either the source or destination table, because their metadata already matches.

Inserting Records from Temporary Tables

You can manually define and populate temporary tables to hold a list of records for later bulk insertion.

Populate the Temporary Table

To create a temporary table, you must give it a name ending in "#TEMP" and execute an INSERT INTO query using that name, as if that table already existed in the database. After executing the first INSERT INTO, the temporary table exists and can receive subsequent INSERTs. For example:

INSERT INTO Subscriber#TEMP (Status, MyCustomField__c) VALUES ('New Subscriber', '9000');
INSERT INTO Subscriber#TEMP (Status, MyCustomField__c) VALUES ('New Subscriber 2', '9001');
INSERT INTO Subscriber#TEMP (Status, MyCustomField__c) VALUES ('New Subscriber 3', '9002');

This creates a temporary table called Subscriber#TEMP with two columns and three rows of data. Since type cannot be determined on the temporary table itself, all values are considered strings and later converted to the proper type when they are inserted together into the real (non-temporary) table of interest.

Insert Temporary Table Contents into Real Tables

Once your temporary table is populated, execute an INSERT INTO SELECT query targeting the real (non-temporary) table you want to insert the temporary table's records into. This is formatted similarly to a standard INSERT INTO query, except the VALUES clause is substituted with a SELECT query targeting the matching columns in the temporary table. For example:

INSERT INTO Subscriber (Status, MyCustomField__c) SELECT Status, MyCustomField__c FROM Subscriber#TEMP

In this example, the full contents of Subscriber#TEMP are inserted into the Subscriber.

Results

The LastResultInfo#TEMP temporary table contains details about the most recently executed query that uses the contents of a temporary table in an embedded SELECT clause, as is the case for INSERT INTO SELECT queries that use a temporary table as the source of records. This table is cleared and repopulated each time such a query is executed. LastResultInfo#TEMP includes information such as whether the query in question succeeded, and how many rows were affected by the query.

Temporary Table Lifespan

Temporary tables only last as long as the connection remains open. When the connection to Salesforce Marketing Cloud is closed, all temporary tables are cleared, including the LastResultInfo#TEMP table.

To perform multiple updates in a single request to Salesforce Marketing Cloud,first use the INSERT INTO syntax to insert a temporary table of data into Salesforce Marketing Cloud. This works by first populating a temporary table with the data you are going to submit to Salesforce Marketing Cloud. Once you have all of the data you want to update, use UPDATE SELECT FROM to pass the temporary table data into the table in Salesforce Marketing Cloud.

Populate the Temporary Table

The temporary table you are populating is dynamic and is created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to update, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you are inserting to. For example:

INSERT INTO Subscriber#TEMP (Id, Name, MyCustomField__c) VALUES ('AX1000001', 'New Subscriber', '9000');
INSERT INTO Subscriber#TEMP (Id, Name, MyCustomField__c) VALUES ('AX1000002', 'New Subscriber 2', '9001');
INSERT INTO Subscriber#TEMP (Id, Name, MyCustomField__c) VALUES ('AX1000003', 'New Subscriber 3', '9002');

This creates a temporary table called Subscriber#TEMP with three columns and three rows of data. Since type cannot be determined on the temporary table itself, all values are stored in memory as strings. The values are later converted to the proper type when they are submitted to the Subscriber table.

Update the Actual Table

Once your temporary table is populated, it is now time to update the actual table in Salesforce Marketing Cloud. You can do this by performing an UPDATE to the actual table and selecting the input data from the temporary table. For example:

UPDATE Subscriber (Id, Status, MyCustomField__c) SELECT Id, Status, MyCustomField__c FROM Subscriber#TEMP

In this example, the full contents of the Subscriber#TEMP table are passed into the Subscriber table. This results in fewer requests being submitted to Salesforce Marketing Cloud since multiple updates may be submitted with each request, which is much better for performance if you have many records to update.

Results

The results of the query are stored in the LastResultInfo#TEMP temporary table. This table is cleared and repopulated the next time data is modified by passing in a temporary table. Please be aware that the LastResultInfo#TEMP table has no predefined schema. You need to check its metadata at run time before reading data.

Temporary Table Life Span

Temporary tables only last as long as the connection remains open. When the connection to Salesforce Marketing Cloud is closed, all temporary tables are cleared, including the LastResultInfo#TEMP table.

The CData Python Connector for Salesforce Marketing Cloud models Salesforce Marketing Cloud data as an easy-to-use SQL database with tables, views, and stored procedures.

The connector exposes two schemas:

• The REST API exposes broad access to Salesforce Marketing Cloud capabilities. All new Salesforce Marketing Cloud technologies implement the REST API. See REST Data Model for the available entities in the REST API.
• The SOAP API provides comprehensive access to most email functionality. The SOAP API uses SOAP envelopes to pass information between you and Salesforce Marketing Cloud. See SOAP Data Model for the available entities in the SOAP API.

The CData Python Connector for Salesforce Marketing Cloud models Salesforce Marketing Cloud data as an easy-to-use SQL database. There are three parts to the data model: tables, views, and stored procedures.

Tables

The Tables section, which details standard SQL tables, and the Views section, which lists read-only SQL tables, describe the schema exposed through the Salesforce Marketing Cloud.com API. The available data depends on your account credentials and access level.

The tables shipped with the CData Python Connector for Salesforce Marketing Cloud include:

Table	Description
Assets	Provides access to digital assets within Salesforce Marketing Cloud. Assets include reusable content elements such as images, templates, or code snippets that are stored and managed in Content Builder.
AssetTypes	Returns the available asset types configured in Salesforce Marketing Cloud. Each asset type defines the structure, behavior, and rendering rules for specific types of content elements.
Callbacks	Manages event notification callbacks that are configured in Salesforce Marketing Cloud.
CampaignAssets	Maintains relationships between campaigns and their associated assets in Salesforce Marketing Cloud.
Campaigns	Represents campaigns in Salesforce Marketing Cloud. Each campaign groups related marketing efforts and performance metrics under a single initiative.
Categories	Stores category information (also known as folders) within Content Builder in Salesforce Marketing Cloud.
Contact	Retrieves detailed information for a specific contact in Salesforce Marketing Cloud. A contact represents an individual subscriber or customer who interacts with your marketing campaigns.
Contacts	Retrieves a comprehensive list of all contacts in Salesforce Marketing Cloud. Each contact represents a unique individual with associated communication preferences and subscription data.
EventDefinitions	Manages event definitions within Salesforce Marketing Cloud. An event definition specifies a trigger, such as a form submission or API call, that initiates an automated process like a journey entry or data update.
FacebookMessengerProperties	Defines properties for Facebook Messenger integrations in Salesforce Marketing Cloud. These properties control how messages are formatted, delivered, and tracked within Facebook channels.
JourneyActivities	Contains details about journey activities in Salesforce Marketing Cloud. A journey activity represents an action or decision point in a customer journey, such as sending an email or evaluating a contact attribute.
JourneyAuditLogs	Returns audit logs for journeys and their versions in Salesforce Marketing Cloud.
JourneyHistory	Retrieves the historical execution records of customer journeys in Salesforce Marketing Cloud. Each record reflects the runtime status, performance, and event details of a specific journey instance.
Journeys	Represents customer journeys in Salesforce Marketing Cloud.
LineMessengerProperties	The table that defines configuration properties for LINE messenger integrations in Salesforce Marketing Cloud. These properties determine how messages are delivered and tracked within LINE communication channels.
MobileApplications	Returns the list of mobile applications (apps) configured in a Salesforce Marketing Cloud account.
SmsStatusCodes	Returns Short Message Service (SMS) status codes recognized by Salesforce Marketing Cloud.
SendDefinitions	Stores send definitions in Salesforce Marketing Cloud.
Subscriptions	Manages event notification subscriptions within Salesforce Marketing Cloud.
TransactionalMessages	Retrieves a paginated list of transactional messages that are not successfully sent in Salesforce Marketing Cloud.

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. They perform tasks beyond standard CRUD operations, including checking job status and managing OAuth access.

The connector models the data in Salesforce Marketing Cloud as a list of tables in a relational database that can be queried using standard SQL statements.

CData Python Connector for Salesforce Marketing Cloud Tables

Name	Description
Assets	Provides access to digital assets within Salesforce Marketing Cloud. Assets include reusable content elements such as images, templates, or code snippets that are stored and managed in Content Builder. This table allows you to create, update, delete, and query these assets to support consistent brand messaging across campaigns and channels.
Callbacks	Manages event notification callbacks that are configured in Salesforce Marketing Cloud. A callback defines an endpoint that receives real-time notifications when specified system events occur, such as message delivery or data import completion. This table allows you to create, update, delete, and query callback definitions to automate event-driven workflows.
CampaignAssets	Maintains relationships between campaigns and their associated assets in Salesforce Marketing Cloud. Each record links campaign initiatives with digital assets such as email templates, images, or social media content. This table supports create, update, delete, and query operations for managing campaign resources effectively.
Campaigns	Represents campaigns in Salesforce Marketing Cloud. Each campaign groups related marketing efforts and performance metrics under a single initiative. This table allows you to create, update, delete, and query campaigns to manage lifecycle stages, track engagement, and associate related assets or audiences.
Categories	Create, update, delete and query categories(folders) in Content Builder.
EventDefinitions	Manages event definitions within Salesforce Marketing Cloud. An event definition specifies a trigger, such as a form submission or API call, that initiates an automated process like a journey entry or data update. This table allows you to create, delete, and query event definitions to orchestrate customer experiences.
FacebookMessengerProperties	Defines properties for Facebook Messenger integrations in Salesforce Marketing Cloud. These properties control how messages are formatted, delivered, and tracked within Facebook channels. This table allows you to query existing properties or create new configurations to manage social messaging campaigns.
JourneyActivities	Contains details about journey activities in Salesforce Marketing Cloud. A journey activity represents an action or decision point in a customer journey, such as sending an email or evaluating a contact attribute. This table allows you to create, update, and query journey activities to refine automation workflows.
Journeys	Represents customer journeys in Salesforce Marketing Cloud. A journey defines an automated, multi-step process that guides contacts through personalized interactions across channels. This table allows you to create, update, delete, and query journey definitions to manage audience engagement strategies.
LineMessengerProperties	The table that defines configuration properties for LINE messenger integrations in Salesforce Marketing Cloud. These properties determine how messages are delivered and tracked within LINE communication channels. This table allows you to query and create properties to support messaging automation for the LINE platform.
SendDefinitions	Stores send definitions in Salesforce Marketing Cloud. A send definition specifies the parameters for email, Short Message Service (SMS), or push sends, including target audiences, content, and delivery options. This table allows you to create, update, delete, and query send definitions to manage outbound communication workflows.
Subscriptions	Create, update, delete and query event notification subscriptions.

Provides access to digital assets within Salesforce Marketing Cloud. Assets include reusable content elements such as images, templates, or code snippets that are stored and managed in Content Builder. This table allows you to create, update, delete, and query these assets to support consistent brand messaging across campaigns and channels.

Table Specific Information

Select

Select all assets:

SELECT * FROM ASSETS

Retrieve a specific asset:

SELECT * FROM ASSETS WHERE ID = 20088

Advanced server side filtering using 'AND' and 'OR' logical operators are supported for this table. You can check in the table info if the column has supported filters. All the columns which have supported filters, also can be sorted server side.

SELECT * FROM ASSETS WHERE (Id = 5895 OR Id = 19442) AND EnterpriseId = 7307527 ORDER BY Name ASC, Id DESC

Insert

To create an Asset, you will need to specify at least the Name and TypeId column.

INSERT INTO ASSETS (TypeID, TypeName, Name) VALUES (207, 'templatebasedemail', 'First_Based_Template_Email_CData')

Update

Assets may be modified by providing the Id of the Asset and issuing an UPDATE statement.

UPDATE ASSETS SET Description = 'This is an updated asset.', Data = '
  "campaigns": {
    "campaigns": [
      {
        "campaignId": 12345,
        "campaignAssociationId": 2387
      }
    ]
  },
  "email": { } ... }'  WHERE ID = 19442

Delete

Assets may be deleted by providing the Id of the Asset and issuing a DELETE statement.

DELETE FROM ASSETS WHERE ID = 20027

Columns

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	Integer	True		=,!=,<,<=,>,>=	Identifies the unique record for the asset in Salesforce Marketing Cloud. This system-generated value is immutable and serves as the primary reference for all internal relationships, audit logs, and API operations. It links the asset to its associated metadata such as owner, category, and asset type, and is required for replication tracking and dependency resolution during publishing or enterprise sharing.
CustomerKey	String	False		=,!=	Stores the customer-defined key or external identifier (Id) for the asset. This human-readable key is unique within a business unit and allows developers or marketers to reference the asset programmatically through REST or SOAP API operations. Unlike the system-generated Id or ObjectId fields, this key maintains referential integrity during migrations or environment synchronization. When not provided, the system automatically derives it from the asset name.
ObjectId	String	True			Contains the system-controlled, read-only text string that globally identifies the asset object. Unlike the Id field, which represents the local record Id within a specific environment, this field remains consistent across services, API layers, and replicated versions of the same asset. It enables the platform to track and reconcile an asset's identity across business units, environments, and publishing workflows.
Name	String	False		=,!=	Specifies the display name that identifies the asset in the Content Builder module, the REST API, and automation tools. This value is defined by the user and used by marketers to locate, reference, and reuse assets across multiple campaigns. Renaming an asset updates its metadata but does not affect integrations that reference the CustomerKey.
Description	String	False		=,!=	Provides a human-readable explanation of the asset's content, purpose, or intended audience. This description improves search accuracy, filtering, and collaboration within shared libraries. It can be updated freely and is displayed in both the UI and API results to assist with documentation and compliance review.
OwnerId	Integer	True		=,!=,<,<=,>,>=	Identifies the user or business unit that owns the asset. This value defines edit and publish permissions and establishes accountability within enterprise environments. Ownership metadata is propagated to shared business units to preserve audit traceability. Changing this value can modify access rights in approval or publishing workflows.
OwnerName	String	True		=,!=	Displays the full name of the asset owner for visibility and collaboration. This value mirrors the user record that is associated with the OwnerId field and helps administrators coordinate updates and enforce brand stewardship policies.
OwnerEmail	String	True		=,!=	Holds the email address of the asset owner for audit reports, approval notifications, and workflow communication. This field supports compliance by enabling direct contact with content custodians and updates automatically when the owner's profile changes in Account Settings.
OwnerUserId	Integer	True			Links the asset owner to their internal user record. This numeric Id verifies permissions when publishing or sharing assets and functions as a join field for ownership validation across enterprise business units.
CreatedDate	Datetime	True		=,!=,<,<=,>,>=	Records the date and time when the asset was first created in Salesforce Marketing Cloud. This timestamp is automatically generated and cannot be modified. It supports reporting dashboards, content lifecycle analytics, and compliance audit trails to identify creation trends and authorship.
CreatorId	Integer	True		=,!=,<,<=,>,>=	Identifies the user who originally created the asset. This Id persists even if ownership later changes and supports accountability in activity-based reporting or historical audits. It ensures that original authorship is preserved for governance and performance review.
CreatorName	String	True			Shows the full name of the user who created the asset. This information appears in system logs, API metadata, and collaboration interfaces, allowing reviewers to attribute original authorship during approval or migration processes.
CreatorEmail	String	True			Holds the email address of the user who created the asset. This address is used to identify the original creator in audit notifications or cross-business-unit requests. It remains static even if the creator's role or access level changes later.
CreatorUserId	Integer	True			Links the asset creation event to the specific internal user record. This Id enables precise permission validation, and it is referenced in governance reporting and automation scripts to attribute imported assets to valid users.
ModifiedDate	Datetime	True		=,!=,<,<=,>,>=	Captures the most recent date and time when the asset was modified. This value updates automatically upon any change to the asset's content or metadata. It underpins version tracking, synchronization, and rollback operations, helping administrators identify assets requiring review before publication.
ModifierId	Integer	True		=,!=,<,<=,>,>=	Identifies the user who most recently modified the asset. This field provides traceability in shared environments and enables auditors to determine who performed the last update. It is referenced in version control processes to associate each revision with a specific editor.
ModifierName	String	True			Shows the name of the user who last modified the asset. This value appears in audit logs and the Content Builder details pane, informing collaborators of recent updates and supporting distributed review workflows.
ModifierEmail	String	True			Holds the email address of the user who most recently modified the asset. This address supports workflow notifications or change-request follow-ups and stays synchronized with user profile data to maintain audit consistency.
ModifierUserId	Integer	True			Links modification events to the user account that performed them. This Id connects the revision history to permission models, ensuring accurate accountability in compliance and quality-assurance reports.
EnterpriseId	Integer	True		=,!=,<,<=,>,>=	Identifies the enterprise-level business unit that governs the asset. This value represents the root organization in an Enterprise 2.0 hierarchy and remains constant even when the asset is replicated or shared across subordinate units. In contrast, the MemberId field specifies the individual business unit that owns or manages the asset locally.
MemberId	Integer	True		=,!=,<,<=,>,>=	Stores the Marketing Cloud Member Id (MID) for the business unit that owns the asset. This value defines the operational context for editing, localization, and data governance. Whereas the EnterpriseId field identifies the overarching enterprise parent, this field determines ownership and access permissions within that unit and is evaluated alongside EnterpriseId to resolve sharing scope and publishing rights.
ActiveDate	Datetime	False		=,!=,<,<=,>,>=	Defines the date and time when the asset becomes active or available for use. When no value is supplied, the asset is active immediately upon creation.
ExpirationDate	Datetime	False		=,!=,<,<=,>,>=	Defines when the asset expires or becomes inactive. After this date, the asset can no longer be used in new sends or automations but remains accessible for reporting. Used together with the ActiveDate field, it enforces lifecycle governance to prevent reuse of outdated content.
ContentType	String	False		=,!=	Specifies the format or media category of the asset (for example, HTML, Text, Image, or JSON). This field determines which rendering engine and delivery channels are supported and ensures compatibility between asset design and targeted output medium.
TypeId	Integer	False		=,!=,<,<=,>,>=	Identifies the asset's structural type definition within Salesforce Marketing Cloud. This system reference links the asset to internal metadata that controls supported properties, channels, and rendering logic.
TypeName	String	False			Indicates the internal technical name of the asset type. This value is used in API filtering and classification, enabling automation scripts to determine processing rules for specific asset formats during import or export.
TypeDisplayName	String	False			Provides the user-friendly display name of the asset type. This label appears in the Content Builder interface and reporting views to describe the content kind (for example, Email Template or Image File), helping users recognize content categories quickly.
CategoryId	Integer	False		=,!=,<,<=,>,>=	Links the asset to the category or folder where it resides. Categories organize assets hierarchically for storage and access control and are evaluated when determining folder permissions and applying Content Builder filters for retrieval.
CategoryName	String	False			Displays the name of the category or folder that contains the asset. This value supports navigation and search within Content Builder's folder hierarchy and maintains consistency between UI and API folder structures.
CategoryParentId	Integer	False			References the parent category of the folder containing the asset. This value defines the hierarchical relationship between nested folders and supports inherited permissions and breadcrumb navigation.
Content	String	False		=,!=	Contains the main content of the asset. This field can hold HTML markup, JSON configuration, or text that is used to render messages and pages. The message compiler reads this field directly during send execution to generate personalized output.
Design	String	False		=,!=	Defines fallback design data that used when neither the primary Content nor SuperContent field is available. This field ensures a consistent layout by providing default visuals or placeholders and preserves rendering integrity in conditional-content assets.
SuperContent	String	False		=,!=	Contains enhanced content that overrides the standard Content field during rendering. Typically used for localized or dynamic variations, this field allows targeted delivery of alternate layouts or messages when conditions are met.
File	String	False			Stores a Base64-encoded binary representation of a file that is associated with the asset. Supported media include images, documents, or templates uploaded to Content Builder. The file is validated for size and type according to the FileProperties field before distribution or publication.
FileProperties	String	False			Holds structured metadata about file-type assets, including file name, extension, size, and content type. These properties are generated automatically during upload and used to validate compatibility, enforce storage policies, and optimize asset delivery performance.
ForwardHtml	String	False			Contains the HTML markup that is used for the 'Forward to a Friend' version of the asset. This version renders when a recipient forwards the message using the platform's forwarding feature. This field preserves layout, branding, and personalization tokens where permitted to maintain a consistent experience across email clients.
ForwardText	String	False			Provides the plain-text equivalent of the 'Forward to a Friend' view. This version appears when a recipient's mail client or the forward flow supports text-only rendering. Including this view ensures accessibility and reliable delivery in environments that restrict HTML.
HtmlContent	String	False			Contains the primary HTML that is used by the compiler to render the asset during message generation. This field can include layout elements, reusable blocks, and personalization tokens that resolve for each recipient. Maintaining valid, lightweight HTML improves rendering accuracy and deliverability.
HtmlSlots	String	False			Defines named content regions, or slots, within the asset's HTML layout. Each slot acts as a placeholder where blocks or dynamic content can be inserted during authoring or send compilation. Well-defined slots standardize layouts while preserving design flexibility.
HtmlTemplate	String	False			References the base HTML template that governs the asset's structure and styling. The template determines available slots, shared components, and default formatting. Using consistent templates enforces brand standards and accelerates campaign creation.
Preheader	String	False			Stores the preheader text that appears beside or below the subject line in many inboxes. This short summary gives recipients additional context and encourages opens. The compiler evaluates this text at send time, and marketers can edit it in Content Builder.
SubjectLine	String	False			Specifies the subject line used for an email asset. The subject line directly influences open rates and must accurately reflect message content. This field can include personalization tokens that resolve during send execution.
SubscriptionCenter	String	False			Links the asset to an associated subscription-management experience. This connection enables recipients to manage preferences such as topics, frequency, and channels. Integrating assets with subscription centers supports compliance and reduces opt-out churn.
Text	String	False			Holds the plain-text representation of the asset for recipients who use text-only mail clients or prefer simplified messages. This field conveys the same information as the HTML version and includes required compliance text. Providing both formats improves accessibility and deliverability.
ViewAsAWebPage	String	False			Defines the configuration for the 'View as a Web Page' option in email assets. When this option is enabled, recipients can open the message in a browser to view a fully rendered version independent of client restrictions. This feature also aids troubleshooting and content review.
GenerateFrom	String	False			Specifies which view or template the compiler should use to generate the final version of the asset. This field directs the system to resolve the appropriate slots and content blocks for the selected channel. Using the correct source view ensures consistent presentation across automations and triggered sends.
Slots	String	False			Lists all content slots that are available within the asset. Each slot represents a defined region that can contain blocks or dynamic content during design. Establishing clear slot definitions provides structure and prevents layout errors.
Blocks	String	False			Lists the content blocks that compose the asset. Blocks are reusable modules such as images, text, buttons, or dynamic content elements that are inserted into slots. Standardized blocks speed production and preserve brand consistency.
MinBlocks	Integer	False			Specifies the minimum number of content blocks that are required for the asset to be considered complete. Authoring tools might enforce this rule to prevent incomplete designs and maintain layout integrity.
MaxBlocks	Integer	False			Specifies the maximum number of blocks that the asset can contain. This limit prevents overloaded layouts, improves rendering performance, and ensures a balanced design across devices.
AllowedBlocks	String	False			Lists the block types that are permitted within the asset. Restricting allowed blocks maintains technical compatibility and enforces creative guidelines. Validation occurs during authoring and send compilation to ensure compliance with design standards.
Template	String	False			Identifies the template that defines the asset's foundational layout and default styling. Templates establish reusable frameworks that simplify localization and seasonal updates while maintaining visual consistency.
CustomFields	String	False			Contains user-defined metadata fields that extend the standard asset schema. Custom fields capture workflow details, targeting notes, or reporting tags used during production. They appear in both the UI and APIs for automation and governance.
Data	String	False			Stores a set of key-value pairs that represent dynamic asset data. These values can be accessed by AMPscript or personalization logic during message compilation. Maintaining concise, well-structured data improves performance and readability.
Channels	String	False			Lists the delivery channels through which the asset can be used (for example, 'Email', 'SMS', 'Push', or 'Social'). Declaring supported channels guides validation and prevents use of incompatible assets. Clear channel assignment streamlines content discovery for multi-channel teams.
Version	Integer	False			Tracks the sequential version number of the asset. Each increment represents a published or saved milestone that supports rollback and approval workflows. Version tracking provides transparent change history for audit and collaboration.
Locked	Boolean	False			A Boolean field that returns a value of 'true' when the asset is locked to prevent edits or deletion, typically during approval, publication, or governance review. It returns a value of 'false' when the asset is open for modification. This state integrates with Content Builder approvals to safeguard approved content during campaigns.
Status	String	False			Defines the current workflow state of the asset (for example, 'Draft', 'Approved', 'Published', or 'Archived'). Status determines whether the asset can be used in sends and whether additional approval is required before editing. Consistent status management improves operational visibility and compliance.
Tags	String	False			Stores tags that are associated with the asset for filtering, search, and reporting. Tags help teams organize libraries by theme, campaign, or audience. Using standardized tags improves discoverability and reduces duplication.
BusinessUnitAvailability	String	False			Contains a mapping of business-unit Ids (MIDs) that are authorized to access or use the asset. This field ensures that shared content remains visible yet protected from unauthorized modification.
SharingProperties	String	False			Defines the configuration rules for sharing assets across business units that have Content Builder Sharing enabled. This field controls how those assets behave once they are shared, whether they replicated, synchronized, or linked as read-only copies. These settings maintain enterprise-wide content consistency while preserving source control.

Manages event notification callbacks that are configured in Salesforce Marketing Cloud. A callback defines an endpoint that receives real-time notifications when specified system events occur, such as message delivery or data import completion. This table allows you to create, update, delete, and query callback definitions to automate event-driven workflows.

Table Specific Information

Select

Select all callbacks:

SELECT * FROM Callbacks

Retrieve a specific callback:

SELECT * FROM Callbacks WHERE CallbackId = 94766

Insert

To create a Callback, you will need to specify at least the CallbackName and Url column.

INSERT INTO [Callbacks] (CallbackName, Url) VALUES ('cb1', 'https://example.com')

Update

Callbacks may be modified by providing the CallbackId of the callback and issuing an UPDATE statement.

UPDATE [Callbacks] SET CallbackName = 'cb update' WHERE CallbackId = '34cd6cfe-5a21-4f3e-94c5-b6313a6954a4'

Delete

Callbacks may be deleted by providing the CallbackId of the callback and issuing a DELETE statement.

DELETE FROM [Callbacks] WHERE CallbackId = '43841979-7154-4fc4-9789-909dbba3a54f'

Columns

Name	Type	ReadOnly	References	Filters	Description
CallbackId [KEY]	String	False		=	Identifies the unique string value that represents the event notification callback within Salesforce Marketing Cloud. This system-generated identifier (Id) is used to register, track, and manage callbacks that are defined for API event notifications.
CallbackName	String	False			Specifies the name that defines the event notification callback. This user-assigned label is used to identify callbacks in administrative views and API responses and helps distinguish multiple notification configurations that can exist within an account.
Url	String	False			Defines the endpoint URL that the system calls when an event notification is triggered. This value must be a valid, reachable URL that receives HTTPS POST requests containing payload data for subscribed events.
MaxBatchSize	Integer	False			Indicates the maximum number of event notifications that can be included in a single callback batch. This value controls the payload size for each delivery cycle and helps balance throughput against network performance. The system enforces this limit to optimize event dispatch efficiency.
Status	String	False			Defines the current operational state of the event notification callback (for example, 'Active', 'Paused', or 'Disabled'). This field determines whether the callback is currently processing notifications or awaiting reactivation. Monitoring callback status helps ensure uninterrupted event delivery.
StatusReason	String	False			Provides a descriptive reason that explains the callback's current status. This field records contextual information, such as error responses or administrative actions, that is used to diagnose callback issues or justify configuration changes.

Maintains relationships between campaigns and their associated assets in Salesforce Marketing Cloud. Each record links campaign initiatives with digital assets such as email templates, images, or social media content. This table supports create, update, delete, and query operations for managing campaign resources effectively.

View Specific Information

Select

Select all campaign assets for a specific campaign:

SELECT * FROM CampaignAssets WHERE CampaignId = '3130'

Retrieve a specific Campaign:

SELECT * FROM CampaignAssets WHERE CampaignId = '3130' AND Id = '3325'

Columns

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	Integer	True		=	Identifies the unique record for each campaign asset in Salesforce Marketing Cloud. This system-generated identifier (Id) serves as the primary key that is used to reference the campaign asset in API calls, reporting, and campaign management workflows.
CampaignId	Integer	True		=	Stores the Id of the campaign with which that the asset is associated. This value links the asset to its parent campaign, allowing Marketing Cloud to group and report assets that contribute to a specific marketing initiative. It ensures data integrity and supports cross-channel campaign tracking.
Type	String	False			Specifies the classification or type of campaign asset (for example, 'Email', 'Landing Page', or 'Content Block'). This field determines how the asset is processed and displayed in campaign analytics and reporting dashboards.
ItemId	String	False			Stores the internal object Id that represents the specific asset within Salesforce Marketing Cloud. This value links the campaign asset record to its source object, such as a Content Builder item or send definition, enabling end-to-end traceability between campaign components and asset instances.
CreatedDate	Datetime	False			Records the exact date and time when the campaign asset was created. This timestamp is system-generated and used for audit trails, campaign versioning, and time-based analytics. It helps marketers track asset creation trends and manage campaign lifecycle events.

Represents campaigns in Salesforce Marketing Cloud. Each campaign groups related marketing efforts and performance metrics under a single initiative. This table allows you to create, update, delete, and query campaigns to manage lifecycle stages, track engagement, and associate related assets or audiences.

Table Specific Information

Select

Select all campaigns:

SELECT * FROM Campaigns

Retrieve a specific Campaign:

SELECT * FROM Campaigns WHERE Id = '3130'

Insert

To create a campaign, you will need to specify at least the Name column.

INSERT INTO [Campaigns] (Name, Description, CampaignCode, Color, Favorite) VALUES ('Test Camp', 'Test Description', 'tst 001', '0000ff', true)

Update

UPDATE operations are not supported for this table.

Delete

Campaigns may be deleted by providing the CampaignId of the campaign and issuing a DELETE statement.

DELETE FROM [Campaigns] WHERE Id = '5161'

Columns

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	Integer	True		=	Identifies the unique record for each campaign in Salesforce Marketing Cloud. This system-generated identifier (Id) serves as the primary key that is used to reference the campaign in API calls, analytics, and campaign management workflows. It ensures data integrity and supports reporting relationships across assets, sends, and journeys.
Name	String	False			Specifies the name that identifies the campaign. This user-defined value appears in Marketing Cloud dashboards, reports, and automation interfaces. Maintaining a clear and consistent naming convention helps users quickly locate and organize campaigns across business units.
CampaignCode	String	False			Stores the custom code that uniquely represents the campaign. This value can be used for cross-platform integration, analytics tracking, or external system synchronization. Campaign codes help align Marketing Cloud campaigns with third-party customer relationship management (CRM) or advertising identifiers.
Color	String	False			Defines the hexadecimal or predefined color value that represents the campaign visually in the user interface. This color helps users differentiate campaigns quickly in dashboards and overview reports.
Favorite	Boolean	False			A Boolean field that returns a value of 'true' when the campaign is marked as a favorite by the user. It returns a value of 'false' when it is not marked as a favorite. Marking a campaign as a favorite adds it to personalized quick-access lists within the user interface for faster navigation.
CreatedDate	Datetime	False			Records the date and time when the campaign was first created. This system-generated timestamp supports audit tracking, campaign lifecycle management, and time-based reporting. It is immutable and reflects the campaign's original creation event.
ModifiedDate	Datetime	False			Records the most recent date and time when the campaign was updated. This timestamp is automatically refreshed whenever campaign details or metadata are changed. It enables teams to track modification activity and identify recently edited campaigns.
Description	String	False			Provides a summary that explains the purpose, scope, or target audience of the campaign. This field supports search, categorization, and collaboration by offering context for marketing goals and key initiatives within Salesforce Marketing Cloud.

Create, update, delete and query categories(folders) in Content Builder.

Table Specific Information

Select

Select all categories:

SELECT * FROM Categories

Retrieve all categories which have a specific ParentId:

SELECT * FROM Categories WHERE ParentId = 71839

Retrieve a specific category:

SELECT * FROM Categories WHERE Id = 94766

All the columns except SharedWith and SharingType can be sorted server side:

SELECT * FROM Categories ORDER BY Name ASC

Insert

To create a Category, you will need to specify at least the Name and ParentId column.

INSERT INTO Categories (Name, ParentId, categoryType) VALUES ('New New New Folder', 71839, 'asset')

Update

Categories may be modified by providing the Id of the category and issuing an UPDATE statement.

UPDATE Categories SET SharedWith = '333,555,888', SharingType = 'edit', EnterpriseId = 12345 WHERE Id = 71839

Delete

Categories may be deleted by providing the Id of the category and issuing a DELETE statement.

DELETE FROM Categories WHERE Id = 94843

Columns

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	Integer	True		=	The Id of the category(folder) in Content Builder.
Name	String	False			Name of the category.
ParentId	Integer	False		=	ID of the parent category.
CategoryType	String	False			The type of category, either asset or asset-shared, which is automatically set to the CategoryType of the parent category. If set to asset-shared, include the SharingProperties in the call.
EnterpriseId	Integer	False			ID of the enterprise this business unit belongs to.
MemberId	Integer	False			ID of the member who creates the category.
SharedWith	String	False			List of up to 100 MID IDs the category is shared with. To share the category with all business units in the enterprise, and if your account has access to Content Builder Across Enterprise Sharing, set this to 0. SharedWith cannot contain 0 and other MIDs simultaneously. Since shared categories live in and are owned by the enterprise business unit, don't include the enterprise business unit in the SharedWith property.
SharingType	String	False			Indicates the permission that you are granting to the list of MIDs in sharedWith. The only possible value for categories is edit. The allowed values are edit.
Description	String	False			Description of the category.

Manages event definitions within Salesforce Marketing Cloud. An event definition specifies a trigger, such as a form submission or API call, that initiates an automated process like a journey entry or data update. This table allows you to create, delete, and query event definitions to orchestrate customer experiences.

Table Specific Information

Select

Retrieve all event definitions:

SELECT * FROM EventDefinitions

Retrieve a specific event definition:

SELECT * FROM EventDefinitions WHERE Id = '9955614b-02e7-4147-91a2-3f5f5fe9d679'

Retrieve all event definitions which are running in a specific mode:

SELECT * FROM EventDefinitions WHERE Mode = 'Test'

Retrieve all event definitions which contain the specified quoted phrase in their names:

SELECT * FROM EventDefinitions WHERE CONTAINS (Name, 'Welcome Journey')

Insert

To create an event definition, you will need to specify at least the Name and Type column. DataExtensionId is also required. If you do not specify it, you must specify the Schema column.

INSERT INTO EventDefinitions (Type, Name, DataExtensionId, IsVisibleInPicker) VALUES ('Event', 'FirstEventDefinition', '74bc3342-eaca-e711-b98f-38eaa71427a1', true)

Delete

Event definitions may be deleted by providing the Id of the event definition and issuing a DELETE statement.

DELETE FROM EventDefinitions WHERE Id = 'f10efb9e-cb91-4fc9-be50-c20f00f7f255'

Columns

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	String	True		=	Specifies the unique identifier (Id) that represents the event definition in Salesforce Marketing Cloud. This system-generated value is used to reference the event definition in Journey Builder, Event Administration, and API operations.
Type	String	False			Defines the type of the event definition. The event type determines how the event is initiated and processed within Journey Builder. The allowed values are Event, ContactEvent, DateEvent, RestEvent.
Name	String	False		Contains	Specifies the name that identifies the event definition in Marketing Cloud. This name appears in Event Administration and Journey Builder interfaces and helps users distinguish among available event sources.
CreatedDate	Datetime	True			Records the date and time when the event definition was created. This timestamp is automatically assigned and supports audit tracking and historical reporting.
CreatedBy	Integer	True			Specifies the Id of the user who created the event definition. This reference enables audit traceability and assists administrators in reviewing ownership or activity history.
ModifiedDate	Datetime	True			Records the most recent date and time when the event definition was updated. This value updates automatically whenever any configuration property is changed.
ModifiedBy	Integer	True			Specifies the Id of the user who last modified the event definition. This field helps track configuration updates for governance and compliance auditing.
Mode	String	False		=	Defines the operational mode that determines how the event definition runs. Mode settings affect whether events are triggered manually, by schedule, or through API calls. The allowed values are Production, Test. The default value is Production.
EventDefinitionKey	String	False			Specifies the external key that uniquely identifies the event definition in Salesforce Marketing Cloud. This key must be unique and cannot contain special characters. It provides a stable reference across environments and API integrations.
DataExtensionId	String	False			Specifies the Id of the data extension that is associated with the event. When an event is fired through the API, the system writes event data to this data extension. This parameter is required when no schema is provided.
DataExtensionName	String	False			Displays the read-only name of the data extension that is associated with the event. This value helps users verify which data extension is linked to a specific event definition.
Description	String	False			Provides a summary that describes the purpose or behavior of the event definition. Including a meaningful description improves discoverability and documentation accuracy across business units.
Schema	String	False			Specifies the schema information that defines the structure of event data. When no data extension Id is provided, this schema is used to create the corresponding data extension automatically. This property ensures that incoming event data conforms to the expected format.
SendableCustomObjectField	String	False			Defines the field within the associated data extension that stores the subscriber key or email address. This value is required when defining a schema and enables message sends and contact association.
SendableSubscriberField	String	False			Indicates the type of subscriber field that the event definition uses to identify recipients. This field is required when defining a schema to ensure proper linkage between contact records and event data.
Schedule	String	False			Defines the scheduling information for an event-driven automation that runs daily according to the specified timing. When a schedule is configured, the system automatically creates a Fire Event activity in Automation Studio that triggers events from the associated data extension.
FilterDefinitionId	String	False			Specifies the Id of the filter definition that applies to the event. Filter definitions allow targeted event processing by limiting the scope of contacts that qualify for triggering.
FilterDefinitionTemplate	String	False			Specifies the template that is used to create or apply a filter definition for the event. Templates help standardize segmentation logic across event definitions.
IconUrl	String	False			Specifies the URL of the icon that represents the event definition in Event Administration and the Journey Builder Canvas. The icon provides a visual cue that helps users recognize the event type quickly.
Arguments	String	False			Contains filter criteria that define how the event data is evaluated before triggering.
Configuration	String	False			Specifies additional configuration data for the event.
ConfigurationArguments	String	False			Defines the set of arguments that configure how the event definition executes. These arguments can include dynamic parameters, mappings, or service endpoints used during event processing.
Metadata	String	False			Contains optional metadata that describes the event and its configuration properties. This parameter is typically used for event types other than 'Event' to store descriptive or operational information that supplements the definition.
InteractionCount	Integer	False			Records the total number of interactions that are associated with the event definition. This value provides insight into event usage and frequency within Journey Builder.
IsVisibleInPicker	Boolean	False			A Boolean field that returns a value of 'true' when the event definition is visible in the Event Picker for use in Journey Builder configuration. It returns a value of 'false' when the event is hidden or reserved for system use. The default value is true.
Category	String	False			Specifies the general category or classification of the event type. Categories help group events by function or source for easier discovery and filtering in administrative tools. The default value is event.
PublishedInteractionCount	Integer	False			Records the number of published interactions that use the event definition. This value helps determine the event's adoption and deployment within live journeys.
AutomationId	String	False			Specifies the Id of the automation that is linked to the event definition. This reference connects the event configuration to its corresponding Automation Studio workflow.

Defines properties for Facebook Messenger integrations in Salesforce Marketing Cloud. These properties control how messages are formatted, delivered, and tracked within Facebook channels. This table allows you to query existing properties or create new configurations to manage social messaging campaigns.

Table Specific Information

Select

Retrieve all registred facebook messenger properties:

SELECT * FROM FacebookMessengerProperties

Retrieve a specific registred facebook messenger property:

SELECT * FROM FacebookMessengerProperties WHERE PageId = '1732555047025799'

Insert

To register a new facebook messenger property you must specify PageId, ApplicationId, ApplicationSecret, PageName, PageAccessToken, CallbackVerifyToken, EndpointUrl and ApiVersion:

INSERT INTO FacebookMessengerProperties (PageId, ApplicationId, ApplicationSecret, PageName, PageAccessToken, EndpointUrl, CallbackVerifyToken, ApiVersion) VALUES ('1732555047025799', '1093076390764037', '03d537gg656gvkbe9b430f002e9c4517', 'SFMC Engineers', 'someaccesstoken4fasdcruib213123knubkdnfisdubnu12312ub3pijnb', 'https://graph.facebook.com/v2.6/me/messages', 'this_is_the_verify_token', 'v2.0')

Columns

Name	Type	ReadOnly	References	Filters	Description
PageId [KEY]	String	False		=	Specifies the unique identifier (Id) of the Facebook Page that is connected to the Messenger property. This identifier allows Salesforce Marketing Cloud to associate outgoing messages and incoming events with the correct Facebook Page.
ApplicationId	String	False			Specifies the Facebook Application Id that is registered for use with the Messenger integration. This value links the Salesforce Marketing Cloud account to a specific Facebook Application configuration for API authentication and message delivery.
ApplicationSecret	String	False			Specifies the Facebook application secret key that is used with the ApplicationId value to authenticate API requests. This value should be stored securely because it grants access to the associated Facebook App and its Messenger permissions.
PageName	String	False			Specifies the name of the Facebook page that is connected to the Messenger property. This name appears in user-facing interfaces and helps identify the origin of messages within Salesforce Marketing Cloud.
PageAccessToken	String	False			Specifies the access token that is required to authenticate message send requests to the Facebook Graph API. This token allows Salesforce Marketing Cloud to post messages, retrieve replies, and handle event callbacks for the connected page.
CallbackVerifyToken	String	False			Specifies the verification token that Facebook uses to validate webhook callbacks. This token ensures that incoming callback events originate from Facebook and have not been tampered with.
EndpointUrl	String	False			Specifies the Send API endpoint URL of the Facebook network. Salesforce Marketing Cloud uses this URL to deliver outbound messages and event notifications through the Facebook Graph API.
IsActive	Boolean	False			A Boolean field that returns a value of 'true' when the Facebook Messenger property is active and available for use. It returns a value of 'false' when the property is disabled, expired, or not linked to a valid page.
ApiVersion	String	False			Specifies the Facebook Graph API version that is used for the Messenger integration. This version determines available endpoints, authentication behavior, and compatibility with Facebook's latest messaging features.

Contains details about journey activities in Salesforce Marketing Cloud. A journey activity represents an action or decision point in a customer journey, such as sending an email or evaluating a contact attribute. This table allows you to create, update, and query journey activities to refine automation workflows.

Table Specific Information

Select

Retrieve journey activities for the latest version of the specified journey:

SELECT * FROM JourneyActivities WHERE JourneyId = '1cb643b5-3144-4d17-80fa-a1f0035e78e2'

Retrieve journey activities for a specific journey version:

SELECT * FROM JourneyActivities WHERE JourneyId = '1cb643b5-3144-4d17-80fa-a1f0035e78e2' AND JourneyVersion = 1

Retrieve journey activities from all journeys with a specific version:

SELECT * FROM JourneyActivities WHERE JourneyVersion = 1

Insert

To create a new journey activity, you will need to specify at least the JourneyId, JourneyVersion, Type, Key, Arguments, ConfigurationArguments column.

INSERT INTO JourneyActivities (JourneyId, JourneyVersion, Type, Key, Arguments, ConfigurationArguments) VALUES ('4753026f-20b2-481b-89c5-fcd76ffa41f7', 1, 'WAIT', 'WAITBYDURATION-1', '{
  "waitEndDateAttributeDataBound": "",
  "waitDefinitionId": "f3de0c9a-5ff8-4f7b-84bd-9309ca337227",
  "waitForEventId": "",
  "executionMode": "{{Context.ExecutionMode}}",
  "startActivityKey": "{{Context.StartActivityKey}}",
  "waitQueueId": "{{Context.WaitQueueId}}"
}', '{
  "waitDuration": 1,
  "waitUnit": "DAYS",
  "specifiedTime": "",
  "timeZone": "",
  "description": "",
  "waitEndDateAttributeExpression": "",
  "specificDate": "",
  "waitForEventKey": ""
}')

Update

Journey activities may be modified by providing the Id, JourneyId, JourneyVersion and issuing an UPDATE statement.

UPDATE JourneyActivities SET Description = 'First_Journey_Activity_Description', Outcomes = '[
  {
    "key": "11bb2807-3f3d-4305-af51-547df032dbaf",
    "next": "WAITBYDURATION-1",
    "arguments": null,
    "metaData": null
  }
]' WHERE Id = 'fa4c3d81-8043-40e2-9741-22708d3a2e25' AND  JourneyId = '4753026f-20b2-481b-89c5-fcd76ffa41f7' AND JourneyVersion = 1

Columns

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	String	True			Specifies the unique identifier (Id) that Marketing Cloud assigns to the activity. This Id links the activity to its corresponding configuration within Journey Builder.
JourneyId [KEY]	String	True	Journeys.Id	=	Specifies the unique Id of the journey that contains this activity. This Id is generated by the Journey Builder API when the journey is created and ensures that the activity is associated with the correct journey record.
JourneyVersion [KEY]	Integer	True	Journeys.Version	=	Specifies the version number of the journey that contains this activity. Each version represents a published or edited state of the journey and helps distinguish activity behavior across iterations.
Key	String	False			Specifies the customer key that uniquely identifies the activity within the journey. This key remains stable across edits and is used for API calls, configuration storage, and journey versioning.
Name	String	False			Specifies the display name of the activity as shown in the Journey Builder user interface. This name helps users identify the purpose or function of the activity during journey design.
Description	String	False			Specifies the descriptive text that explains the purpose or behavior of the activity. This description provides context to users who configure or troubleshoot the journey.
Type	String	False			Specifies the activity type (for example, email activity, wait activity, event activity, or decision activity). Each activity type expects specific inputs that must be provided for the activity to run correctly within the journey.
Outcomes	String	False			Specifies the JSON array that defines the available outcomes for the activity. Outcomes determine the paths a contact can follow after the activity executes (for example, success, failure, or custom-defined branching results).
Arguments	String	False			Specifies the set of arguments that the activity requires at runtime. These arguments define operational behavior (for example, message payloads, wait durations, filter conditions, or event-mapped values). Each activity type requires its own argument structure.
ConfigurationArguments	String	False			Specifies the arguments that the activity uses during both publish time and runtime. These arguments define configuration values, mappings, or settings that must remain consistent across journey versions for the activity to execute correctly.

Represents customer journeys in Salesforce Marketing Cloud. A journey defines an automated, multi-step process that guides contacts through personalized interactions across channels. This table allows you to create, update, delete, and query journey definitions to manage audience engagement strategies.

Table Specific Information

Select

Retrieve journeys with the most recent version:

SELECT * FROM Journeys

Retrieve a journey version:

SELECT * FROM Journeys WHERE ID = '1cb643b5-3144-4d17-80fa-a1f0035e78e2' AND Version = 1

Retrieve all journey versions:

SELECT * FROM Journeys WHERE MostRecentVersionOnly = false

Retrieve journeys with a specific tag:

SELECT * FROM Journeys WHERE Tag = 'First_Tag'

Retrieve journeys which have the specified search string inside the name or description:

SELECT * FROM Journeys WHERE NameOrDescription = 'Journey'

Retrieve journeys with specific work flow API version:

SELECT * FROM Journeys WHERE workFlowApiVersion = 1

Retrieve journeys with specific version:

SELECT * FROM Journeys WHERE Version = 1

Retrieve journeys with specific status:

SELECT * FROM Journeys WHERE Status = 'Draft'

Sort Journeys according to modifiedDate or name column:

SELECT * FROM Journeys ORDER BY ModifiedDate DESC
SELECT * FROM Journeys ORDER BY Name DESC

Insert

To create a new journey, you will need to specify at least the Name column.

INSERT INTO Journeys (Name) VALUES ('API-Created journey')

To create a new journey version, you will need to specify an existing journey key.

INSERT INTO Journeys (Name, Key) VALUES ('API-Created journey Version 3', '53bf5ea2-ff59-4c00-a23a-b1e9e333b80c')

Update

Journeys may be modified by providing the Id, Version of the journey and issuing an UPDATE statement.

UPDATE Journeys SET Name = 'API-Updated journey' WHERE Id = '257c51df-d6ed-4fb6-8fbc-70e63ed52b12' AND Version = 5

Delete

Journeys may be deleted by providing the Id of the journey and issuing a DELETE statement.

DELETE FROM Journeys WHERE Id = '53bf5ea2-ff59-4c00-a23a-b1e9e333b80c'

Journey versions may be deleted by providing the Id, Version of the journey and issuing a DELETE statement.

DELETE FROM Journeys WHERE Id = '257c51df-d6ed-4fb6-8fbc-70e63ed52b12' AND Version = 5

Columns

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	String	True		=	Specifies the unique identifier (Id) that Marketing Cloud generates for the journey when it is created. This Id is used to reference, query, and manage the journey across Journey Builder and API operations.
Version	Integer	True		=	Specifies the version number of the journey. Each time the journey is republished, Marketing Cloud increments this value to represent the updated iteration.
Key	String	False			Specifies the customer key that uniquely identifies the journey within the business unit. This key is user-defined and remains stable across journey versions.
Name	String	False			Specifies the display name of the journey as shown in Journey Builder. Users see this name when configuring, publishing, or monitoring journeys across the Salesforce Marketing Cloud account.
Status	String	False		=	Specifies the current publishing or execution status of the journey. This field helps filter journeys by their operational state. The allowed values are Draft, Published, ScheduledToPublish, Stopped, Unpublished, Deleted.
CreatedDate	Datetime	True			Specifies the date and time when the journey was originally created in Journey Builder. This timestamp reflects the initial creation event.
ModifiedDate	Datetime	True			Specifies the date and time when the journey was last modified. This timestamp reflects any change to content, structure, configuration, or settings.
LastPublishedDate	Datetime	True			Specifies the date and time when the journey was most recently published. This value is used to track deployment events and determine when updates were released.
Description	String	False			Specifies a descriptive summary of the journey that explains its business purpose, target audience, or automation objective. This description helps teams understand how and why the journey operates.
WorkFlowApiVersion	Double	False		=	Specifies the version of the Journey Builder workflow API that governs the execution logic for the journey. This version determines the available features and processing behaviors. The default value is 1.
Tags	String	False			Specifies the collection of tags that are assigned to the journey to support organization, filtering, and categorization within Journey Builder and API queries.
Goals	String	False			Specifies the goals that are associated with the journey. Goals define the success criteria that Salesforce Marketing Cloud evaluates during execution to determine when a contact has completed the journey.
Triggers	String	False			Specifies the trigger configuration for the journey, including the entry source that determines how contacts enter the workflow. Trigger details vary by entry type (for example, event, data extension, or API entry).
Defaults	String	False			Specifies the ordered list of email address expressions that Journey Builder evaluates to determine which address should be used as the default sender or recipient context for applicable activities.
DefinitionId	String	True			Specifies the Id of the journey definition that governs the structure, configuration, and publishable form of the journey. This Id links execution logic to the underlying definition.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
MostRecentVersionOnly	Boolean	Specifies whether the query should return only the most recent version of each matching journey. The value is 'true' when limiting results to the latest version and 'false' when including all versions. The default value is true.
Tag	String	Specifies a single tag to apply as a filter when retrieving journeys. The query returns only journeys that are associated with the specified tag.
NameOrDescription	String	Specifies a search string used to filter journeys by matching the provided text against the Name or Description fields.

The table that defines configuration properties for LINE messenger integrations in Salesforce Marketing Cloud. These properties determine how messages are delivered and tracked within LINE communication channels. This table allows you to query and create properties to support messaging automation for the LINE platform.

Table Specific Information

Select

Retrieve all registred line messenger properties:

SELECT * FROM LineMessengerProperties

Retrieve a specific registred line messenger property:

SELECT * FROM LineMessengerProperties WHERE ID = '23493453984234345'

Insert

To register a new line messenger property you must specify ChannelId, ChannelName, ChannelSecret, IsTransactional, IsTestChannel, EndpointUrl and ApiVersion:

INSERT INTO LineMessengerProperties (ChannelId, ChannelName, ChannelSecret, EndpointUrl, IsTransactional, IsTestChannel, IsActive, ApiVersion) VALUES ('23493453984234345', 'SFMC Engineers', '03d537gg656gvkbe9b430f002e9c4517', 'https://example.com/1732555047025799', true, false, true, 'v2.0')");

Columns

Name	Type	ReadOnly	References	Filters	Description
ChannelId [KEY]	String	False		=	Specifies the unique identifier (Id) of the LINE Messenger property. This Id corresponds to the LINE channel configured in Salesforce Marketing Cloud and is required for authentication and message routing.
ChannelName	String	False			Specifies the name of the LINE channel. This value matches the channel name configured in the LINE Developer Console and helps identify the messaging source within Salesforce Marketing Cloud.
ChannelSecret	String	False			Specifies the LINE channel secret that is required to authenticate requests between Salesforce Marketing Cloud and the LINE platform. This value validates that calls originate from a trusted integration.
CustomerConnectSecret	String	False			Specifies the LINE Customer Connect secret that validates inbound events sent through the Switcher API. This secret ensures that messages, replies, and webhook events originate from LINE and have not been tampered with.
IsTransactional	Boolean	False			Specifies whether the LINE channel is classified as transactional or reseller. A value of 'true' designates a transactional channel that is used for direct customer messaging, and a value of 'false' designates reseller channel behavior.
IsTestChannel	Boolean	False			Specifies whether the LINE channel is configured as a test channel. A value of 'true' indicates a sandbox or non-production configuration, and a value of 'false' indicates a live messaging channel.
EndpointUrl	String	False			Specifies the LINE Send API URL that Salesforce Marketing Cloud uses to transmit outbound messages. The endpoint determines where message payloads are delivered for processing by LINE.
IsActive	Boolean	False			Specifies whether the LINE Messenger resource is currently active. A value of 'true' indicates that the channel is enabled for messaging operations, and a value of 'false' indicates that the channel is disabled or not fully configured.
ApiVersion	String	False			Specifies the version of the Open Traceability Transport (OTT) API that applies to the LINE Messenger property. This version determines the supported message formats and API capabilities.

Stores send definitions in Salesforce Marketing Cloud. A send definition specifies the parameters for email, Short Message Service (SMS), or push sends, including target audiences, content, and delivery options. This table allows you to create, update, delete, and query send definitions to manage outbound communication workflows.

Table Specific Information

Select

Retrieve all send definitions:

SELECT * FROM SendDefinitions

Retrieve a specific send definition:

SELECT * FROM SendDefinitions WHERE DefinitionKey = '9955614b-02e7-4147-91a2-3f5f5fe9d679'

Retrieve all send definitions with Status as 'Active':

SELECT * FROM SendDefinitions WHERE Status = 'Active'

Insert

To create a send definition, you will need to specify at least the DefinitionKey, Name, SubscriptionsList and ContentCustomerKey column.

INSERT INTO SendDefinitions (DefinitionKey, Name, ContentCustomerKey, SubscriptionsList, OptionsCc, OptionsCreateJourney) VALUES ('TEST_Definition_Key', 'Test Definition Key', '76ad3572-abbc-4baa-b3fe-04c4364bf34a', 'All Subscribers', 'john@example.com', true)

Update

To update a send definition, you will need to specify the DefinitionKey of the SendDefinition.

UPDATE SendDefinitions SET OptionsCc = 'john@example.com,steve@example.com', OptionsBcc = 'michael@example.com,richard@example.com' WHERE DefinitionKey = 'TEST_Definition_Key'

Delete

Send definitions may be deleted by providing the Definition Key of the send definition and issuing a DELETE statement.

DELETE FROM SendDefinitions WHERE DefinitionKey = 'TEST_Definition_Key'

Columns

Name	Type	ReadOnly	References	Filters	Description
DefinitionKey [KEY]	String	False		=	Specifies the unique, user-generated key that identifies the send definition. This key is used to reference the definition programmatically and remains stable across edits.
DefinitionId	String	True			Specifies the unique identifier (Id) that Salesforce Marketing Cloud assigns to the send definition. This Id is used internally to manage, retrieve, and execute the definition.
Classification	String	False			Specifies the external key of the sending classification that is defined in Email Studio Administration. Only transactional classifications are permitted for this object. The default value is the system's transactional classification.
ContentCustomerKey	String	False			Specifies the unique key of the content asset that is associated with the send definition. This key identifies the email asset that is used when the definition executes.
CreatedDate	Datetime	True			Specifies the date and time when the send definition was created in Salesforce Marketing Cloud.
Description	String	False			Specifies the user-provided description that explains the purpose or intended use of the send definition.
Journey	Boolean	True			Specifies whether the send definition is available in Journey Builder as a transactional send journey. A value of 'true' enables the definition for journey-based triggering.
JourneyInteractionKey	String	True			Specifies the unique Id of the transactional send journey that is associated with the definition. This Id links the definition to the journey that triggers it.
ModifiedDate	Datetime	True			Specifies the date and time when the send definition was most recently modified.
Name	String	False			Specifies the name of the send definition as displayed in Email Studio and through API queries.
OptionsBcc	String	False			Specifies one or more email addresses to receive blind carbon copies (BCC) for every send. To dynamically populate BCC addresses at send time, create a profile attribute and reference it using the %%attribute%% syntax.
OptionsCc	String	False			Specifies one or more email addresses to receive carbon copies (CC) for every send. To dynamically populate CC addresses at send time, create a profile attribute and reference it using the %%attribute%% syntax.
OptionsTrackLinks	Boolean	False			Specifies whether Salesforce Marketing Cloud should wrap hyperlinks for click tracking and reporting. The default value is 'true'.
OptionsCreateJourney	Boolean	False			Specifies whether the system should create the underlying journey configuration when defining sendable schema fields such as subscriber key or email address. This setting is required only when schema information must be defined or validated.
RequestId	String	True			Specifies the unique Id of the request that performed the most recent operation on the send definition.
Status	String	False		=,!=	Specifies the operational state of the send definition (for example, active, inactive, or deleted). Messages submitted to an active definition are processed and delivered. Messages submitted to an inactive definition are not processed and are instead queued for up to three days for possible reactivation.
SubscriptionsAutoAddSubscriber	Boolean	False			Specifies whether the system should automatically add the recipient's email address and contact key to the system-managed subscriber list that stores subscriber keys and profile attributes. The default value is 'true'.
SubscriptionsDataExtension	String	False			Specifies the external key of the triggered send data extension. Each triggered request inserts a new row into this data extension with relevant message and subscriber details.
SubscriptionsList	String	False			Specifies the external key of the list or All Subscribers context in which subscriber keys and profile attributes are stored. This list is used as a repository for subscription and profile data related to the send.
SubscriptionsUpdateSubscriber	Boolean	False			Specifies whether the system should update the recipient's subscriber key with the provided email address and profile attributes in subscriptions.list. This setting applies to email messages only. The default value is 'true'.