Getting Started

This page provides a guide to Establishing a Connection to SendGrid in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to SendGrid

Establishing a Connection shows how to authenticate to SendGrid and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from SendGrid through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Establishing a Connection

Connect to SendGrid by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

You must use an API key to authenticate through the SendGrid Cloud.

Obtaining an API Key

To obtain an API key, follow the steps below:

Log into SendGrid.
Click Settings.
Click API keys.
Click Create API Key.
Choose the Permissions the API Key should have, either Full Access or Restricted Access. The API key is then displayed.

NOTE: If you want access to Email events, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

SSL Configuration

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

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.

Data Model

The Cloud models SendGrid APIs as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files.

However, the Recipients table is dynamic: The Cloud connects to SendGrid and gets the metadata for this table by calling the appropriate Web services. Any changes you make to the custom filters you define for Recipients are available when you connect; you can use these custom filters as columns in the WHERE clause. These custom filters are surfaced in the CustomFields table.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

New Marketing Campaigns Data Model

See New Marketing Campaigns Data Model for the available entities in the New Marketing Campaigns API.

Legacy Data Model

See Legacy Data Model for the available entities in the Legacy API.

New Marketing Campaigns Data Model

The CData Cloud models the New Marketing Campaigns API as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

To use the New Marketing Campaigns Data Model, simply set Schema to NMC.

Tables

Tables describes the available tables that are easy to query and update.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to Sendgrid. They can be used to search, update, and modify information.

Tables

The Cloud models the data in SendGrid as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - SendGrid Tables


Name	Description
Alerts	Create, update, delete, and query Alerts in SendGrid.
ApiKeys	Query, Create, Update and Delete the available ApiKeys in SendGrid.
Blocks	Query and Delete the available Blocks in SendGrid.
Bounces	Query and Delete the available Bounces in SendGrid.
CancelScheduledSends	Update, delete, and query Cancel Scheduled Sends in SendGrid.
GlobalUnsubscribes	Query Global Unsubscribes in SendGrid.
InvalidEmails	Query and Delete invalid emails in SendGrid.
NMCCustomFields	Create, delete, and query New Marketing Campaign Custom Fields in SendGrid.
NMCLists	Create, update, delete, and query the available New Marketing Campaign Lists in SendGrid.
NMCSegments	Create, update, delete, and query New Marketing Campaign Segments in SendGrid.
NMCSenders	Create, update, delete, and query New Marketing Campaign Senders in SendGrid.
NMCSingleSends	Create, update, delete, and query the available New Marketing Campaign Single Sends in SendGrid.
Spam	Query and delete Spam reports in SendGrid.
Teammates	Query Teammates in SendGrid.
UnsubscribeGroups	Create, update, delete, and query Unsubscribe Groups in SendGrid.

Alerts

Create, update, delete, and query Alerts in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM Alerts
SELECT * FROM Alerts WHERE Id = '148825'

Insert

The following columns are required in an insert for Stats Notification: Type, EmailTo, Frequency.

INSERT INTO Alerts (Type, EmailTo, Frequency) VALUES ('stats_notification', '[email protected]', 'daily')

The following columns are required in an insert for Usage Limit: Type, EmailTo, Percentage.

INSERT INTO Alerts (Type, EmailTo, Percentage) VALUES ('usage_limit', '[email protected]', 20)

Update

You can only update EmailTo by specifying an Id.

UPDATE Alerts SET EmailTo = '[email protected]' WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM Alerts WHERE Id = 1310789

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		The ID of the alert.
CreatedAt	Datetime	True		Date when the alert was created.
UpdatedAt	Datetime	True		Date when the alert was updated.
EmailTo	String	False		The email address that the alert will be sent to.
Percentage	Integer	False		The email address that the alert will be sent to.
Type	String	False		The type of alert. Allowed values (usage_limit, stats_notification).
Frequency	String	False		If the alert is of type stats_notification, this indicates how frequently the stats notifications will be sent.(daily, weekly, monthly).

ApiKeys

Query, Create, Update and Delete the available ApiKeys in SendGrid.

Table-Specific Information

Select

To get all the Api Keys

SELECT * FROM ApiKeys

To get information about a specific Api Key

SELECT * FROM Apikeys WHERE ApiKeyId = 'Dqdvdvdvdv4yLeuVQqO3rFxN5ktUxA'

Insert

The following columns are required in an insert: Name. VALUES for Scopes are: alerts.create, alerts.read, alerts.update, alerts.delete, asm.groups.create, asm.groups.read, asm.groups.update, asm.groups.delete, ips.pools.ips.read, mail.send, mail_settings.bcc.read, mail_settings.bcc.update, mail_settings.address_whitelist.read, mail_settings.address_whitelist.update, mail_settings.footer.read, mail_settings.footer.update, mail_settings.forward_spam.read, mail_settings.forward_spam.update, mail_settings.plain_content.read, mail_settings.plain_content.update, mail_settings.spam_check.read, mail_settings.spam_check.update, mail_settings.bounce_purge.read, mail_settings.bounce_purge.update, mail_settings.forward_bounce.read, mail_settings.forward_bounce.update, partner_settings.new_relic.read, partner_settings.new_relic.update, partner_settings.sendwithus.read, partner_settings.sendwithus.update, tracking_settings.click.read, tracking_settings.click.update, tracking_settings.subscription.read, tracking_settings.subscription.update, tracking_settings.open.read, tracking_settings.open.update, tracking_settings.google_analytics.read, tracking_settings.google_analytics.update, user.webhooks.event.settings.read, user.webhooks.event.settings.update, user.webhooks.event.test.create, user.webhooks.event.test.read, user.webhooks.event.test.update, user.webhooks.parse.settings.create, user.webhooks.parse.settings.read, user.webhooks.parse.settings.update, user.webhooks.parse.settings.delete, stats.read, stats.global.read, categories.stats.read, categories.stats.sums.read, devices.stats.read, clients.stats.read, clients.phone.stats.read, clients.tablet.stats.read, clients.webmail.stats.read, clients.desktop.stats.read, geo.stats.read, mailbox_providers.stats.read, browsers.stats.read, user.webhooks.parse.stats.read, templates.create, templates.read, templates.update, templates.delete, templates.versions.create, templates.versions.read, templates.versions.update, templates.versions.delete, templates.versions.activate.create, user.timezone.read, user.timezone.update, user.settings.enforced_tls.read, user.settings.enforced_tls.update, api_keys.create, api_keys.read, api_keys.update, api_keys.delete, email_activity.read, categories.create, categories.read, categories.update, categories.delete, mail_settings.template.read, mail_settings.template.update, marketing_campaigns.create, marketing_campaigns.read, marketing_campaigns.update, marketing_campaigns.delete, mail.batch.create, mail.batch.read, mail.batch.update, mail.batch.delete, user.scheduled_sends.create, user.scheduled_sends.read, user.scheduled_sends.update, user.scheduled_sends.delete, access_settings.whitelist.create, access_settings.whitelist.read, access_settings.whitelist.update, access_settings.whitelist.delete, access_settings.activity.read, whitelabel.create, whitelabel.read, whitelabel.update, whitelabel.delete, suppression.create, suppression.read, suppression.update, suppression.delete, messages.read

INSERT INTO Apikeys (Name) VALUES ('apiname')

INSERT INTO Apikeys (Name, Scopes, Sample) VALUES ('apiname', 'alerts.create,alerts.read', 'test')

Update

You can update ApiKeys by specifying an ApiKeyid.You need to specify both the name and the scopes during the UPDATE even if you don't want to update it.

UPDATE Apikeys SET Name = 'apiname2', Scopes = 'alerts.create,alerts.read' WHERE apikeyid = 'Dqdvdvdvdv4yLeuVQqO3rFxN5kscsctUxA-P_Q'

Delete

You can Delete ApiKeys by specifying an ApiKeyid.

DELETE FROM ApiKeys WHERE apikeyid = 'CMp4C8klscscscsQ0GxqvxtK2-P_Q'

Columns


Name	Type	ReadOnly	References	Description
ApiKeyId [KEY]	String	False		The ID of your API Key.
Name	String	False		The name of your API Key.
Scopes	String	False		The permissions this API Key has access to.

Blocks

Query and Delete the available Blocks in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Blocks WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Blocks by specifying an Email.

DELETE FROM Blocks WHERE Email = '[email protected]'

Columns


Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the block was created.
Reason	String	False		The reason for the block.
Email [KEY]	String	False		The email that blocked.
Status	String	False		The enhanced SMTP block response.

Bounces

Query and Delete the available Bounces in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Bounces WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Bounces by specifying an Email.

DELETE FROM Bounces WHERE Email = '[email protected]'

Columns


Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the bounce was created.
Reason	String	False		The reason for the bounce.
Email [KEY]	String	False		The email that bounced.
Status	String	False		The enhanced SMTP bounce response.

CancelScheduledSends

Update, delete, and query Cancel Scheduled Sends in SendGrid.

Table-Specific Information

Select

This table only supports filtering by BatchId. The following are the only possible SELECT queries:

SELECT * FROM CancelScheduledSends
SELECT * FROM CancelScheduledSends WHERE BatchId = 148825

Update

You can update CancelScheduledSends by specifying an BatchId.

Allowed Values : cancel, pause

UPDATE CancelScheduledSends SET Status = 'cancel'  WHERE BatchId = 1310789

Delete

You can only delete from CancelScheduledSends by specifying an BatchId.

DELETE FROM CancelScheduledSends WHERE BatchId = 1310789

Columns


Name	Type	ReadOnly	References	Description
BatchId [KEY]	String	False		The Id of the batch.
Status	String	False		The status of the scheduled send.Allowed Values: cancel, pause

GlobalUnsubscribes

Query Global Unsubscribes in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. However this filter is optional. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalUnsubscribes WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

INSERT

Performing an Insert operation in this table you need to specify an email which will be added to the GlobalUnsubscribes list. For example:

INSERT INTO GlobalUnsubscribes (Email) VALUES ('[email protected]')

DELETE

Performing a Delete operation in this table you need to specify an email which will be removed from the GlobalUnsubscribes list. For example:

DELETE FROM GlobalUnsubscribes WHERE Email = '[email protected]'

Columns


Name	Type	ReadOnly	References	Description
Email [KEY]	String	False		Email of the unsubscriber.
CreatedAt	Datetime	True		The time the unsubscriber was created.

InvalidEmails

Query and Delete invalid emails in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM InvalidEmails
SELECT * FROM InvalidEmails WHERE Email = '[email protected]'

Delete

You can only delete from InvalidEmails by specifying an Email.

DELETE FROM InvalidEmails WHERE Email = '[email protected]'

Columns


Name	Type	ReadOnly	References	Description
Created	Datetime	True		The time the when the email address was added to the invalid emails list.
Reason	String	False		A reason explaining why the email address was marked as invalid..
Email	String	False		The email address that was marked as invalid.

NMCCustomFields

Create, delete, and query New Marketing Campaign Custom Fields in SendGrid.

Table Specific Information

New Marketing Campaign Custom fields are filters for a Contact that are defined by the user. NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the NMCCustomFields or filter them by Id:

SELECT * FROM NMCCustomFields WHERE Id = '1'

Use quotations to filter on NMC Custom Fields regardless of type.

Insert

To insert on NMCCustomFields specify the Name of the field and its Type. The Type can be text, date, or number.

INSERT INTO NMCCustomFields (name, type) VALUES ('age', 'number')

Update

UPDATE NMCCustomFields SET name = 'updated name' WHERE Id = '123'

Delete

You can only delete from NMCCustomFields by specifying an Id:

DELETE FROM NMCCustomFields WHERE Id = '1'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the custom field.
Name	String	False		The name of the custom field.
Type	String	False		The type of the custom field. Allowed values are number, text, or date.

NMCLists

Create, update, delete, and query the available New Marketing Campaign Lists in SendGrid.

Table-Specific Information

NMC prefix stands for New Marketing Campaign.

Select

The NMCLists table can only filtered by its Id.

SELECT * FROM NMCLists WHERE Id = '123'

Insert

Only the Name for the NMCLists is required to insert into this table.

INSERT INTO NMCLists (Name) VALUES ('TestList')

Update

You can only update from NMCLists by specifying an Id.

UPDATE NMCLists SET Name = 'New Name' WHERE Id = '123'

Delete

You can only delete from NMCLists by specifying an Id.

DELETE FROM NMCLists WHERE Id = '123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the list.
Name	String	False		The name of the list.
ContactCount	Integer	True		The recipient count of the list.

NMCSegments

Create, update, delete, and query New Marketing Campaign Segments in SendGrid.

Table-Specific Information

NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the segments or filter them by Id.

SELECT * FROM NMCSegments WHERE Id = '1'

Insert

A Segment divides NMCContacts by certain criteria. To specify these criteria use the QueryDSL filter.

INSERT INTO NMCSegments (Name, QueryDSL) VALUES ("New Segment", "Segment Cdata", "first_name LIKE '%test%'"))

The statement above creates NMCSegments containing NMCContacts with FirstName containing the value "test".

Update

You can update the name or the conditions. Since the whole set will be overwritten, specify a full set.

UPDATE NMCSegments SET Name = "Name Updated", QueryDSL = "first_name LIKE '%test update%'" WHERE Id = '123'

Delete

You can only delete NMCSegments by Id:

DELETE FROM NMCSegments WHERE Id = '123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the segment.
Name	String	False		The name of the segment.
ParentListId	String	False		The Id of the list associated with the segment.
ContactsCount	Integer	True		The recipient count of the segment.
CreatedAt	Datetime	True		Field name for the first condition.
SampleUpdatedAt	Datetime	False		Field name for the first condition.
UpdatedAt	Datetime	True		Field name for the first condition.
QueryDSL	String	False		Field name for the first condition.
NextSampleUpdate	Datetime	True		The last updated samples date.

NMCSenders

Create, update, delete, and query New Marketing Campaign Senders in SendGrid.

Table-Specific Information

NMC stands for New Marketing Campaign.

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM NMCSenders
SELECT * FROM NMCSenders WHERE Id = '148825'

Insert

The following columns are required in an insert: Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country.

INSERT INTO NMCSenders (Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country) VALUES ('Nickname', 'FromEmail', 'FromName', 'ReplytoEmail', 'Address', 'City', 'Country')

Update

You can update NMCSenders by specifying an Id.

UPDATE NMCSenders SET Nickname = 'Nickname', FromEmail = 'FromEmail', FromName = 'FromName', ReplytoEmail = 'ReplytoEmail', Address = 'Address', City = 'City', Country = 'Country'  WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM NMCSenders WHERE Id = 1310789

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The unique identifier of the sender identity.
Nickname	String	False		A nickname for the sender identity. Not used for sending..
FromEmail	String	False		This is where the email will appear to originate from for your recipient.
FromName	String	False		This is the name appended to the from email field. IE - Your name or company name.
ReplytoEmail	String	False		This is the email that your recipient will reply to.
ReplytoName	String	False		This is the name appended to the reply to email field. IE - Your name or company name.
Address	String	False		The physical address of the sender identity.
Address2	String	False		Additional sender identity address information.
City	String	False		The city of the sender identity.
State	String	False		The state of the sender identity.
Zip	String	False		The zipcode of the sender identity.
Locked	String	False		Indicator if the sender is locked.
VerifiedStatus	String	False		Verified status of the sender.
VerifiedReason	String	False		Verified reason of the sender.
Country	String	False		The country of the sender identity.
CreatedAt	Datetime	True		The end of the time range when the bounce was created.
UpdatedAt	Datetime	True		The end of the time range when the bounce was created.

NMCSingleSends

Create, update, delete, and query the available New Marketing Campaign Single Sends in SendGrid.

Table-Specific Information

NMC stands for New Marketing Campaign.

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM NMCSingleSends
SELECT * FROM NMCSingleSends WHERE Id = '148825'

Insert

The following columns are required in an insert: Name.

INSERT INTO NMCSingleSends (Name) VALUES ('SingleSend Name')

Update

You can update NMCSingleSends by specifying an Id.

UPDATE NMCSingleSends SET Name = 'SingleSend Updated' WHERE Id = 1310789

Delete

You can only delete from NMCSingleSends by specifying an Id.

DELETE FROM NMCSingleSends WHERE Id = 1310789

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the single send.
Name	String	False		The name of the single send.
Status	String	False		The status of the single send.
CreatedAt	String	True		The created timestamp of the single send.
UpdatedAt	String	True		The updated timestamp of the single send.
IsABTest	Boolean	False		Indicator showing if the single send is an AB Test.
SenderId	Integer	False		The ID of the verified sender identity.
CustomUnsubscribeUrl	String	False		The URL allowing contacts to unsubscribe.
SuppressionGroupId	Integer	False		The ID of the suppression group to allow customers to unsubscribe.
SendAt	Datetime	False		The ISO 8601 time at which to send the single send.
TemplateId	String	False		Id of the template used.
IpPool	String	False		The name of the IP pool from which the single send emails appear to be sent.

Spam

Query and delete Spam reports in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM Spam
SELECT * FROM Spam WHERE Email = '[email protected]'

Delete

Delete from Spam

You can delete from Spam by specifying an Email.

DELETE FROM Spam WHERE Email = '[email protected]'

Columns


Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time when the spam report was made.
Email [KEY]	String	False		The email address of the recipient who marked your message as spam..
Ip	String	False		The IP address that the message was sent on.

Teammates

Query Teammates in SendGrid.

View-Specific Information

Select

The Cloud will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

Username supports the '=,IN' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Teammates WHERE Username = 'test'

SELECT * FROM Teammates WHERE Username IN ('test', 'test2')

Insert

The following columns are required in an insert: Email and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

The IsAdmin is optional and by default is accepted as false from the API.

INSERT INTO Teammates (Email, Scopes) VALUES ('[email protected]', 'user.profile.read,user.profile.update')

INSERT INTO Teammates (Email, Scopes, IsAdmin) VALUES ('[email protected]', 'user.profile.read,user.profile.update', 'true')

Update

The following columns are required in an update: Username and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

UPDATE Teammates SET Scopes = 'user.profile.read' WHERE Username = 'testuser'

Delete

Only the username column is required to delete a record from Teammates table. For example:

DELETE FROM Teammates WHERE Username = 'testuser'

Columns


Name	Type	ReadOnly	References	Description
Username [KEY]	String	False		The username of the teammate.
Email	String	False		Email of the teammate.
FirstName	String	False		First name of the teammate.
LastName	String	False		Last name of the teammate.
UserType	String	False		User type of the teammate.
IsAdmin	Boolean	False		Indicator if the teammate is admin.
Phone	String	False		Phone of the teammate.
Website	String	False		Website of the teammate.
Company	String	False		Company of the teammate.
Address	String	False		Address of the teammate.
Address2	String	False		Address 2 of the teammate.
City	String	False		City of the teammate.
State	String	False		State of the teammate.
Country	String	False		Country of the teammate.
Zip	String	False		Zip of the teammate.
Scopes	String	False		Scope of the permissions for the new teammate. Multiple values are allowed and should be split by a comma symbol. Possible values are: user.account.read,user.credits.read,user.email.create,user.email.read,user.email.update,user.email.delete,user.profile.create,user.profile.read,user.profile.update,user.profile.delete

UnsubscribeGroups

Create, update, delete, and query Unsubscribe Groups in SendGrid.

Table-Specific Information

Select

UnsubscribeGroups can only be filtered by Id.

SELECT * FROM UnsubscribeGroups WHERE Id = '1'

Insert

Provide a Name and a Description to insert into UnsubscribeGroups:

INSERT INTO UnsubscribeGroups (Name, Description, IsDefault) VALUES ('Unsubscribe1', 'General Unsubscribe', true)

Update

You can only update UnsubscribeGroups by specifying an Id.

UPDATE UnsubscribeGroups SET Name = 'New Name', Description = 'New Description' WHERE Id = '1'

Delete

You can only delete from UnsubscribeGroups by specifying an Id.

DELETE FROM UnsubscribeGroups WHERE Id = '1'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the unsubscribe group.
Name	String	False		The name of the unsubscribe group.
Description	String	False		The description of the unsubscribe group.
LastEmailSentAt	Datetime	True		The last time an email was sent to the unsubscribe group.
IsDefault	Boolean	False		If the unsubscribe group is the default group or not.
Unsubscribes	Integer	True		The number of unsubscribes from this group.

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - SendGrid Views


Name	Description
Categories	Query the available Categories in SendGrid.
CategoryStats	Query the available Category Stats in SendGrid.
GlobalStats	Query the available Global Stats in SendGrid.
GroupSuppressions	Query group suppressions in SendGrid.
IPAccessManagement	Query Global Unsubscribes in SendGrid.
MessageEvents	List of events related to email message in SendGrid.
Messages	Query Email Activities in SendGrid.
NMCContacts	Query New Marketing Campaign Contacts in SendGrid.
NMCReservedFields	Query the available New Marketing Campaign Reserved Fields in SendGrid.
StatsByBrowser	Query Browser Statistics in SendGrid.
StatsByClient	Query Client Statistics in SendGrid.
StatsByDevice	Query Device Statistics in SendGrid.
StatsByLocation	Query Location Statistics in SendGrid.
StatsByMailboxProvider	Query Mailbox Provider Statistics in SendGrid.
SubuserStats	Query Subuser Statistics in SendGrid.
Users	Query the available Users in SendGrid.

CategoryStats

Query the available Category Stats in SendGrid.

View-Specific Information

Select

The following is an example to query the CategoryStats view:

 
SELECT * FROM CategoryStats WHERE Name = 'news'

You can also filter by Date. If you do not specify one, a default span of the past 1 year is used. The operators =, >=, <=, <, and > are supported for this column.

SELECT * FROM CategoryStats WHERE Name = 'news' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns


Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the category.
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.
UnsubscribeDrops	Integer		The number of emails in this category dropped by SendGrid because the recipient unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails.

GlobalStats

Query the available Global Stats in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalStats

SELECT * FROM GlobalStats WHERE Date > '2016-01-01' AND Date < '2016-03-30'

Columns


Name	Type	References	Description
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
BounceDrops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.
UnsubscribeDrops	Integer		The number of emails globally dropped by SendGrid because the recipient unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails.

GroupSuppressions

Query group suppressions in SendGrid.

Table-Specific Information

Select

The following are examples to query the GroupSuppressions table:

SELECT * FROM GroupSuppressions WHERE GroupId = 391

SELECT * FROM GroupSuppressions WHERE GroupId IN (391, 392, 393)

Columns


Name	Type	References	Description
GroupId [KEY]	Integer		Email of the unsubscriber.
Emails	String		The time the unsubscriber was created.

IPAccessManagement

Query Global Unsubscribes in SendGrid.

View-Specific Information

Select

The Cloud will use the SendGrid API to retrieve information about the IP addresses which have accessed the account. However there is no filtering supported server-side for this view.

Only the following query is processed server side:

SQL

SELECT * FROM IPAccessManagement

Columns


Name	Type	References	Description
IP	String		IP Address.
Allowed	Boolean		Indicator if this IP is allowed for access.
AuthMethod	String		Authentication method.
FirstAt	Timestamp		First time when IP has accessed the account.
LastAt	Timestamp		Last time when IP has accessed the account.
Location	String		Location of the IP.

MessageEvents

List of events related to email message in SendGrid.

NOTE: If you want access to Email events, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

The Cloud will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

MessageId supports the '=' comparisons.

SQL

SELECT * FROM MessageEvents WHERE MessageId = '1234'

Columns


Name	Type	References	Description
MessageId	String		The Id of the message.
EventName	String		Name of event. The allowed values are bounced, opened, clicked, processed, dropped, delivered, deferred, spam_report, unsubscribe, group_unsubscribe, group_resubscribe.
Processed	Datetime		The date when the event was processed.
Reason	String		Explanation of what caused the message to be 'bounced', 'deferred', or 'blocked'.
AttemptNum	Integer		Used with 'deferred' events to indicate the attempt number out of 10.
Url	String		Used with 'clicked' event to indicate which url the user clicked.
BounceType	String		Use to distinguish between types of bounces. The allowed values are soft, hard.
HttpUserAgent	String		Client recipient used to click or open message.
MXServer	String		The MX server that received the email.
ApiKeyId	String		Id of the API Key.
AsmGroupId	String		ASM Group Id.
Categories	String		Categories users associated to the message.
UniqueArgs	String		JSON hash of key-value pairs associated with the message.
OriginatingIp	String		This is the IP address of the person sending the message
OutboundIp	String		This is the SendGrid dedicated IP address used to send the email
OutboundIpType	String		The type of the outbound IP.
Teammate	String		Username of the teammate.
TemplateId	String		Template ID of the email.

Messages

Query Email Activities in SendGrid.

NOTE: If you want access to Email activities, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

The Cloud will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

Note: The API will return only 1000 records. Please choose from the available filters to restrict the search.

Id supports the '=,IN' comparisons.
Status supports the '=,!=,LIKE,NOT LIKE' comparisons.
Subject supports the '=,!=,LIKE,NOT LIKE' comparisons.
FromEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
ToEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
Clicks supports the '>,<,<=,>=,=,!=' comparisons.
LastEventTime supports the '>=, <=, BETWEEN' comparisons.
ApiKeyId supports the '=,!=,LIKE,NOT LIKE' comparisons.
AsmGroupId supports the '=,!=,LIKE,NOT LIKE' comparisons.
MarketingCampaignId supports the '=,!=,LIKE,NOT LIKE' comparisons.
MarketingCampaignName supports the '=,!=,LIKE,NOT LIKE' comparisons.
OriginatingIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
OutboundIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
Teammate supports the '=,!=,LIKE,NOT LIKE' comparisons.
TemplateId supports the '=,!=,LIKE,NOT LIKE' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Messages WHERE Id = '1234'

SELECT * FROM Messages WHERE Id IN ('1234', '4567')

SELECT * FROM Messages WHERE Status = 'not_delivered'

SELECT * FROM Messages WHERE LastEventTime BETWEEN '2020-01-01' AND '2020-01-15'

Columns


Name	Type	References	Description
Id [KEY]	String		The Id of the message.
Status	String		Status of the email.
Subject	String		Subject of the email.
FromEmail	String		The email of the sender.
ToEmail	String		The email of the receiver.
ClicksCount	Integer		The number of clicks.
OpensCount	Integer		The number of times that the email was opened.
LastEventTime	Datetime		The datetime of the event.
ApiKeyId	String		Id of the API Key.
AsmGroupId	String		ASM Group Id.
MarketingCampaignId	String		The id of the marketing campaign.
MarketingCampaignName	String		The name of the marketing campaign.
MarketingCampaignSplitId	String		Split id of the campaign.
MarketingCampaignVersion	String		Version of the campaign.
OriginatingIp	String		This is the IP address of the person sending the message
OutboundIp	String		This is the SendGrid dedicated IP address used to send the email
OutboundIpType	String		The type of the outbound IP.
Teammate	String		Username of the teammate.
TemplateId	String		Template ID of the email.
Events	String		List of events related to email message in SendGrid.

NMCContacts

Query New Marketing Campaign Contacts in SendGrid.

View-Specific Information

New Marketing Campaign contacts are recepients that are managed by the user. NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the NMCContacts or filter them by Id, ListId or SegmentId:

SELECT * FROM NMCCustomFields WHERE Id = '1'
SELECT * FROM NMCCustomFields WHERE Id IN ('1', '2', '3')
SELECT * FROM NMCCustomFields WHERE ListId = '1'
SELECT * FROM NMCCustomFields WHERE ListId IN ('1', '2', '3')
SELECT * FROM NMCCustomFields WHERE SegmentId = '1'
SELECT * FROM NMCCustomFields WHERE SegmentId IN ('1', '2', '3')

Columns


Name	Type	References	Description
Id [KEY]	String		This column is dynamic and maps to the corresponding field in the API.
AddressLine1	String		This column is dynamic and maps to the corresponding field in the API.
AddressLine2	String		This column is dynamic and maps to the corresponding field in the API.
City	String		This column is dynamic and maps to the corresponding field in the API.
Country	String		This column is dynamic and maps to the corresponding field in the API.
PostalCode	String		This column is dynamic and maps to the corresponding field in the API.
StateProvinceRegion	String		This column is dynamic and maps to the corresponding field in the API.
Line	String		This column is dynamic and maps to the corresponding field in the API.
Facebook	String		This column is dynamic and maps to the corresponding field in the API.
PhoneNumber	String		This column is dynamic and maps to the corresponding field in the API.
UniqueName	String		This column is dynamic and maps to the corresponding field in the API.
WhatsApp	String		This column is dynamic and maps to the corresponding field in the API.
Email	String		This column is dynamic and maps to the corresponding field in the API.
FirstName	String		This column is dynamic and maps to the corresponding field in the API.
LastName	String		This column is dynamic and maps to the corresponding field in the API.
CreatedAt	Datetime		This column is dynamic and maps to the corresponding field in the API.
UpdatedAt	Datetime		This column is dynamic and maps to the corresponding field in the API.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
SegmentId	String		This column is dynamic and maps to the corresponding field in the API.
ListId	String		This column is dynamic and maps to the corresponding field in the API.

NMCReservedFields

Query the available New Marketing Campaign Reserved Fields in SendGrid.

View-Specific Information

Select

This table does not support filtering. NMC prefix stands for New Marketing Campaign.

SELECT * FROM NMCReservedFields

Columns


Name	Type	References	Description
Id	String		The Id of the reserved field.
Name	String		The name of the reserved field.
Type	String		The type of the reserved field.

StatsByBrowser

Query Browser Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByBrowser WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Browser Statistics by name.

SELECT * FROM StatsByBrowser WHERE Name = 'Firefox'

Columns


Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Clicks	Integer		The number of links clicked in your emails.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.

StatsByClient

Query Client Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByClient WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Client Statistics by name.

SELECT * FROM StatsByClient WHERE Name = 'Other'

Columns


Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

StatsByDevice

Query Device Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByDevice WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Device Statistics by name.

SELECT * FROM StatsByDevice WHERE Name = 'Phone'

Columns


Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

StatsByLocation

Query Location Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByLocation WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Location Statistics by name.

SELECT * FROM StatsByLocation WHERE Name = 'US'

To query Location Statistics by type.

SELECT * FROM StatsByLocation WHERE Type = 'country'

Columns


Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Clicks	Integer		The number of links clicked in your emails.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

StatsByMailboxProvider

Query Mailbox Provider Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByMailboxProvider WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Mailbox Provider Statistics by name.

SELECT * FROM StatsByMailboxProvider WHERE Name = 'Microsoft Outlook Live'

Columns


Name	Type	References	Description
Type	String		The type of the statistic. Avaible values (categorie, device, client, mailbox_provider, browser, subuser)
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

SubuserStats

Query Subuser Statistics in SendGrid.

View-Specific Information

Select

You can filter by SubuserName to retrieve stats of a specific subuser.

SELECT * FROM SubuserStats WHERE SubuserName = 'Example'

You can also filter by Date. Operators =, >=, <=, <, and > are supported for this column. If you do not specify one, a default span of the past 1 year is used.

SELECT * FROM SubuserStats WHERE SubuserName = 'News' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns


Name	Type	References	Description
Id [KEY]	String		The Id of the subuser statistic.
SubuserName	String		The name of the subuser.
Date	Datetime		The time the subuser statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links that were clicked in the emails from this subuser.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails from this subsuer that were confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times emails from this subuser were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid by this subuser.
Requests	Integer		The number of emails that you requested to send via SendGrid from this subuser.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails from this subuser that were marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked on links in your emails from this subuser.
UniqueOpens	Integer		The number of unique recipients who opened your emails from this subuser.
UnsubscribeDrops	Integer		The number of emails from this subuser dropped by SendGrid because the recipients unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails from this subuser.
Name	String		The name of the statistic.
Type	String		The type of the statistic.

Users

Query the available Users in SendGrid.

View-Specific Information

Select

Users can only be filtered by Id.

SELECT * FROM Users WHERE Id = '1'

Columns


Name	Type	References	Description
Id [KEY]	String		The Id of the user.
Address	String		The address of the user.
Address2	String		The secondary address field for the user.
City	String		The city of the user.
Company	String		The company of the user.
Country	String		The country of the user.
FirstName	String		The first name of the user.
LastName	String		The last name of the user.
Phone	String		The phone number of the user.
State	String		The state the user is located in.
Website	String		The Web site URL of the user.
Zip	String		The ZIP code of the user.

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with SendGrid.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from SendGrid, along with an indication of whether the procedure succeeded or failed.

CData Cloud - SendGrid Stored Procedures


Name	Description
AddRecipientToList	Adds a recipient to a list.
GetImportContactsStatus	Gets the Import contacts status
ImportMarketingContacts	Import contacts to sendgrid account from CSV file.
SendMail	Sends a mail.
SendMail2	Sends a mail. This Stored Procedure is used to handle aggregates.
SendTestCampaign	Sends a test campaign.

AddRecipientToList

Adds a recipient to a list.

Input


Name	Type	Required	Description
ListId	String	False	The unique identifier for the list.
RecipientId	String	False	The Id of the recipient.

Result Set Columns


Name	Type	Description
Success	String	Whether or not the recipient was successfully added to the list.

GetImportContactsStatus

Gets the Import contacts status

Stored Procedure Specific Information

The GetImportContactsStatus Stored Procedure requires JobId of the Import Contacts, which we can get by executing ImportMarketingContacts stored procedure.

Example:

EXEC GetImportContactsStatus JobId= '1234'

Input


Name	Type	Required	Description
JobId	String	True	The Id of the Import Contacts Job

Result Set Columns


Name	Type	Description
Success	String	The Staus of the corresponding Import Contacts Job.
RequestedCount	String
CreatedCount	String
UpdatedCount	String
DeletedCount	String

ImportMarketingContacts

Import contacts to sendgrid account from CSV file.

Stored Procedure Specific Information

The ImportMarketingContacts Stored Procedure requires Field Mapping Ids and path of the File which contains contacts data.

We can Use custom_field ids or reserved_field ids for Field Mapping and We can get these ids using "Get All Field Definitions" endpoint.

NOTE:

1. Email field mapping is mandatory to import contacts from file.

2. As of now only csv file format is supported for this operation.

Example:

EXEC ImportMarketingContacts FileType= 'csv' , FieldMappings= '_rf2_T,_rf9_T' , FilePath = 'C:\Dev\Tests\v20\ProviderSendgrid\example.csv'

Input


Name	Type	Required	Description
FileType	String	False	The type of the file The allowed values are csv.
FieldMappings	String	True	Comma separated list of field_definition IDs
ListIds	String	False	Comma separated list of List id's
FilePath	String	False	The absolute path of the File to import contacts

Result Set Columns


Name	Type	Description
JobId	String	The Id of the Import Contacts Job

SendMail

Sends a mail.

Procedure-Specific Information

The DynamicTemplateData input requires information about dynamic data of the template. Value for the different variables put into templates.

Example: {"fullname":"Test","paymenttotal":"Test","description":"Test"}

NOTE: The TemplateId column is required when using Dynamic Templates.

Process of adding Persanalizations object to SendMail Stored Procedure

SendMail Requires atleast one personalizations object in the request body and it can be added using the aggregate function.

For Example:

Using TEMP Tables.

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'abc')

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'xyz')

INSERT INTO To#TEMP (ToEmail) VALUES ('[email protected]')

INSERT INTO ToCC#TEMP (ToCCName, ToCCEmail) VALUES ('[email protected]', 'xyz')

INSERT INTO ToBCC#TEMP (ToBCCEmail, ToBCCName) VALUES ('[email protected]', 'abc')

INSERT INTO Content#TEMP (ContentValue, ContentType) VALUES ('[email protected]', 'text/plain')

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2', 'To#TEMP', 'ToCC#TEMP', 'ToBCC#TEMP')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content = 'Content#TEMP'

Using Aggregate values.

Send Mail to single Mail Address.

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2',
         '[
		   {
              "ToEmail": "[email protected]",
              "ToName": "abc"
           }
		  ]',
		  '[
            {
              "ToCCName": "xyz",
              "ToCCEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToBCCEmail": "[email protected]",
              "ToBCCName": "abcd"
            }
          ]')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content =
'{
  "ContentValue":"content",
  "ContentType":"text/plain"
}'

Send Mail to Multiple Mail Addresses.

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2',
          '[
		    {
              "ToEmail": "[email protected]",
              "ToName": "abc"
            },
            {
              "ToEmail": "[email protected]",
              "ToName": "xyz"
            },
            {
              "ToEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToCCName": "sac",
              "ToCCEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToBCCEmail": "xyz",
              "ToBCCName": "[email protected]"
            }
          ]')
		  
EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content =
'{
  "ContentValue":"content",
  "ContentType":"text/html"
}'

Send Mail using attachment.

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'abc')

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'xyz')

INSERT INTO To#TEMP (ToEmail) VALUES ('[email protected]')

INSERT INTO ToCC#TEMP (ToCCName, ToCCEmail) VALUES ('[email protected]', 'xyz')

INSERT INTO ToBCC#TEMP (ToBCCEmail, ToBCCName) VALUES ('[email protected]', 'abc')

INSERT INTO Content#TEMP (ContentValue, ContentType) VALUES ('[email protected]', 'text/plain')

INSERT INTO Attachments#TEMP (AttachmentContent, AttachmentFilename) VALUES ('abcdefghijklmn', 'Test2.png')

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2', 'To#TEMP', 'ToCC#TEMP', 'ToBCC#TEMP')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content = 'Content#TEMP', Attachments = 'Attachments#TEMP'

Input


Name	Type	Required	Description
Subject	String	True	The subject of the mail to be sent.
ContentValue	String	False	The content of the mail to be sent.
ContentType	String	False	The type of content to be sent. supported values are text/plain and text/html
Content	String	True	The aggregate for Content object
SendAt	String	False	The date and time when you want your email to be delivered. Scheduling more than 72 hours in advance is forbidden.
FromEmail	String	True	The email address of the sender.
FromName	String	False	The name of the sender.
ToEmail	String	False	Email address to send the mail to
ToName	String	False	Email's respective names to send the mail to.
To	String	False	The aggregate for To recipients
ToCCEmail	String	False	Email address of the recipients who will receive a copy of your email
ToCCName	String	False	Name of the recipients who will receive a copy of your email.
ToCC	String	False	The aggregate for ToCC recipients.
ToBCCEmail	String	False	Email address of the recipients who will receive a blind carbon copy of your email.
ToBCCName	String	False	Name of the recipients who will receive a blind carbon copy of your email.
ToBCC	String	False	The aggregate for ToBCC recipients.
ReplyToEmail	String	False	The email address of the person to whom you are replying to.
ReplyToName	String	False	The name of the person to whom you are replying to.
AttachmentPaths	String	False	Path of the file to attach to the mail to be sent
TemplateId	String	False	The Id of the template that you may want to send
DynamicTemplateData	String	False	The data of the dynamic template.
Personalizations	String	True	An array of messages and their metadata. Each object within personalizations can be thought of as an envelope - it defines who should receive an individual message and how that message should be handled.
AttachmentFilename	String	False	The attachment's filename. Specify only when you don't specify the AttachmentPaths.
Attachments	String	False	An array of objects where you can specify any attachments you want to include. Specify only when you don't specify the AttachmentPaths.

Result Set Columns


Name	Type	Description
Success	String	Whether or not the mail was sent successfully.

SendMail2

Sends a mail. This Stored Procedure is used to handle aggregates.

Procedure-Specific Information

SendMail2 Stored Procedure is specifically made for handling the aggregates. User has to provide the whole json structure in order to execute this Stored Procedure.

For Example:

EXECUTE SendMail2 Personalizations = '[{"to": [{"email": "[email protected]", "name": "John Doe"}, {"email": "[email protected]", "name": "Julia Doe"}], "cc": [{"email": "[email protected]", "name": "Jane Doe"}], "bcc": [{"email": "[email protected]", "name": "Jim Doe"}]}, {"from": {"email": "[email protected]", "name": "Example Sales Team"}, "to": [{"email": "[email protected]", "name": "Janice Doe"}], "bcc": [{"email": "[email protected]", "name": "Jordan Doe"}]}]', FromEmail = '[email protected]', FromName = 'Example Order Confirmation', Subject = 'Test Subject', Content = '[{"type": "text/html", "value": "test value"}]', Categories = '["cake", "pie"]'

Input


Name	Type	Required	Description
Personalizations	String	True	An array of messages and their metadata. Each object within personalizations can be thought of as an envelope - it defines who should receive an individual message and how that message should be handled.
FromEmail	String	True	The email address of the sender.
FromName	String	True	The name of the sender.
ReplyToEmail	String	False	The email address of the person to whom you are replying to.
ReplyToName	String	False	The name of the person to whom you are replying to.
Subject	String	True	The global or 'message level' subject of your email.
Content	String	True	An array where you can specify the content of your email.
Attachments	String	False	An array of objects where you can specify any attachments you want to include.
Categories	String	False	An array of category names for this message. Each category name may not exceed 255 characters.
SendAt	String	False	A unix timestamp allowing you to specify when you want your email to be delivered.
BatchId	String	False	An ID representing a batch of emails to be sent at the same time.
AsmGroupId	String	False	The unsubscribe group to associate with this email.
AsmGroupsToDisplay	String	False	An array containing the unsubscribe groups that you would like to be displayed on the unsubscribe preferences page.
IpPoolName	String	False	The IP Pool that you would like to send this email from.
MailSettingsByPassListManagementEnable	String	False	A collection of different mail settings that you can use to specify how you would like this email to be handled. Indicates if this setting is enabled.
MailSettingsFooterEnable	String	False	The default footer that you would like included on every email. Indicates if this setting is enabled.
MailSettingsSandboxModeEnable	String	False	Sandbox Mode allows you to send a test email to ensure that your request body is valid and formatted correctly. Indicates if this setting is enabled.
TrackingSettingsClickTrackingEnable	String	False	Allows you to track if a recipient clicked a link in your email. Indicates if this setting is enabled.
TrackingSettingsClickTrackingEnableText	String	False	Allows you to track if a recipient clicked a link in your email. Indicates if this setting should be included in the text/plain portion of your email.
TrackingSettingsOpenTrackingEnable	String	False	Allows you to track if the email was opened by including a single pixel image in the body of the content. When the pixel is loaded, Twilio SendGrid can log that the email was opened. Indicates if this setting is enabled.
TrackingSettingsOpenTrackingSubstitutionTag	String	False	Allows you to track if the email was opened by including a single pixel image in the body of the content. When the pixel is loaded, Twilio SendGrid can log that the email was opened. Allows you to specify a substitution tag that you can insert in the body of your email at a location that you desire. This tag will be replaced by the open tracking pixel.
TrackingSettingsSubscriptionTrackingEnable	String	False	Allows you to insert a subscription management link at the bottom of the text and HTML bodies of your email. Indicates if this setting is enabled.

Result Set Columns


Name	Type	Description
Success	String	Whether or not the mail was sent successfully.

SendTestCampaign

Sends a test campaign.

Input


Name	Type	Required	Description
To	String	False	The email address to send the campaign to.
Campaign_ID	String	False	The Id of the campaign to send.

Result Set Columns


Name	Type	Description
Success	String	Whether or not the campaign was sent successfully.

Legacy Data Model

The CData Cloud models the Legacy API as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files.

However, the Recipients table is dynamic: The Cloud connects to SendGrid and gets the metadata for this table by calling the appropriate Web services. Any changes you make to the custom filters you define for Recipients are available when you connect; you can use these custom filters as columns in the WHERE clause. These custom filters are surfaced in the CustomFields table.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

To use the Legacy Data Model, simply set Schema to Legacy.

Tables

Tables describes the available tables that are easy to query and update.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to Sendgrid. They can be used to search, update, and modify information.

Tables

The Cloud models the data in SendGrid as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - SendGrid Tables


Name	Description
Alerts	Create, update, delete, and query Alerts in SendGrid.
ApiKeys	Query, Create, Update and Delete the available ApiKeys in SendGrid.
Blocks	Query and Delete the available Blocks in SendGrid.
Bounces	Query and Delete the available Bounces in SendGrid.
CancelScheduledSends	Update, delete, and query Cancel Scheduled Sends in SendGrid.
CustomFields	Create, delete, and query Custom Fields in SendGrid.
GlobalUnsubscribes	Query Global Unsubscribes in SendGrid.
InvalidEmails	Query and Delete invalid emails in SendGrid.
Lists	Create, update, delete, and query the available Lists in SendGrid.
MarketingCampaigns	Create, update, delete, and query Marketing Campaigns in SendGrid.
Recipients	The SendGrid table Recipients.
Schedules	Create, update, delete, and query Campaign Schedules in SendGrid.
Segments	Create, update, delete, and query Segments in SendGrid.
SenderIdentities	Create, update, delete, and query Sender Identities in SendGrid.
Spam	Query and delete Spam reports in SendGrid.
Teammates	Query Teammates in SendGrid.
UnsubscribeGroups	Create, update, delete, and query Unsubscribe Groups in SendGrid.

Alerts

Create, update, delete, and query Alerts in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM Alerts WHERE Id = '148825'

Insert

The following columns are required in an insert for Stats Notification: Type, EmailTo, Frequency.

INSERT INTO Alerts (Type, EmailTo, Frequency) VALUES ('stats_notification', '[email protected]', 'daily')

The following columns are required in an insert for Usage Limit: Type, EmailTo, Percentage.

INSERT INTO Alerts (Type, EmailTo, Percentage) VALUES ('usage_limit', '[email protected]', 20)

Update

You can only update EmailTo by specifying an Id.

UPDATE Alerts SET EmailTo = '[email protected]' WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM Alerts WHERE Id = 1310789

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		The ID of the alert.
CreatedAt	Datetime	True		Date when the alert was created.
UpdatedAt	Datetime	True		Date when the alert was updated.
EmailTo	String	False		The email address that the alert will be sent to.
Percentage	Integer	False		The email address that the alert will be sent to.
Type	String	False		The type of alert. Allowed values (usage_limit, stats_notification).
Frequency	String	False		If the alert is of type stats_notification, this indicates how frequently the stats notifications will be sent.(daily, weekly, monthly).

ApiKeys

Query, Create, Update and Delete the available ApiKeys in SendGrid.

Table-Specific Information

Select

To get all the Api Keys

SELECT * FROM ApiKeys

To get information about a specific Api Key

SELECT * FROM Apikeys WHERE ApiKeyId = 'Dqdvdvdvdv4yLeuVQqO3rFxN5ktUxA'

Insert

The following columns are required in an insert: Name. VALUES for Scopes are: alerts.create, alerts.read, alerts.update, alerts.delete, asm.groups.create, asm.groups.read, asm.groups.update, asm.groups.delete, ips.pools.ips.read, mail.send, mail_settings.bcc.read, mail_settings.bcc.update, mail_settings.address_whitelist.read, mail_settings.address_whitelist.update, mail_settings.footer.read, mail_settings.footer.update, mail_settings.forward_spam.read, mail_settings.forward_spam.update, mail_settings.plain_content.read, mail_settings.plain_content.update, mail_settings.spam_check.read, mail_settings.spam_check.update, mail_settings.bounce_purge.read, mail_settings.bounce_purge.update, mail_settings.forward_bounce.read, mail_settings.forward_bounce.update, partner_settings.new_relic.read, partner_settings.new_relic.update, partner_settings.sendwithus.read, partner_settings.sendwithus.update, tracking_settings.click.read, tracking_settings.click.update, tracking_settings.subscription.read, tracking_settings.subscription.update, tracking_settings.open.read, tracking_settings.open.update, tracking_settings.google_analytics.read, tracking_settings.google_analytics.update, user.webhooks.event.settings.read, user.webhooks.event.settings.update, user.webhooks.event.test.create, user.webhooks.event.test.read, user.webhooks.event.test.update, user.webhooks.parse.settings.create, user.webhooks.parse.settings.read, user.webhooks.parse.settings.update, user.webhooks.parse.settings.delete, stats.read, stats.global.read, categories.stats.read, categories.stats.sums.read, devices.stats.read, clients.stats.read, clients.phone.stats.read, clients.tablet.stats.read, clients.webmail.stats.read, clients.desktop.stats.read, geo.stats.read, mailbox_providers.stats.read, browsers.stats.read, user.webhooks.parse.stats.read, templates.create, templates.read, templates.update, templates.delete, templates.versions.create, templates.versions.read, templates.versions.update, templates.versions.delete, templates.versions.activate.create, user.timezone.read, user.timezone.update, user.settings.enforced_tls.read, user.settings.enforced_tls.update, api_keys.create, api_keys.read, api_keys.update, api_keys.delete, email_activity.read, categories.create, categories.read, categories.update, categories.delete, mail_settings.template.read, mail_settings.template.update, marketing_campaigns.create, marketing_campaigns.read, marketing_campaigns.update, marketing_campaigns.delete, mail.batch.create, mail.batch.read, mail.batch.update, mail.batch.delete, user.scheduled_sends.create, user.scheduled_sends.read, user.scheduled_sends.update, user.scheduled_sends.delete, access_settings.whitelist.create, access_settings.whitelist.read, access_settings.whitelist.update, access_settings.whitelist.delete, access_settings.activity.read, whitelabel.create, whitelabel.read, whitelabel.update, whitelabel.delete, suppression.create, suppression.read, suppression.update, suppression.delete, messages.read

INSERT INTO Apikeys (Name) VALUES ('apiname')

INSERT INTO Apikeys (Name, Scopes, Sample) VALUES ('apiname', 'alerts.create,alerts.read', 'test')

Update

You can update ApiKeys by specifying an ApiKeyid.You need to specify both the name and the scopes during the UPDATE even if you don't want to update it.

UPDATE Apikeys SET Name = 'apiname2', Scopes = 'alerts.create,alerts.read' WHERE apikeyid = 'Dqdvdvdvdv4yLeuVQqO3rFxN5kscsctUxA-P_Q'

Delete

You can Delete ApiKeys by specifying an ApiKeyid.

DELETE FROM ApiKeys WHERE apikeyid = 'CMp4C8klscscscsQ0GxqvxtK2-P_Q'

Columns


Name	Type	ReadOnly	References	Description
ApiKeyId [KEY]	String	False		The ID of your API Key.
Name	String	False		The name of your API Key.
Scopes	String	False		The permissions this API Key has access to.

Blocks

Query and Delete the available Blocks in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Blocks WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Blocks by specifying an Email.

DELETE FROM Blocks WHERE Email = '[email protected]'

Columns


Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the block was created.
Reason	String	False		The reason for the block.
Email [KEY]	String	False		The email that blocked.
Status	String	False		The enhanced SMTP block response.

Bounces

Query and Delete the available Bounces in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Bounces WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Bounces by specifying an Email.

DELETE FROM Bounces WHERE Email = '[email protected]'

Columns


Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the bounce was created.
Reason	String	False		The reason for the bounce.
Email [KEY]	String	False		The email that bounced.
Status	String	False		The enhanced SMTP bounce response.

CancelScheduledSends

Update, delete, and query Cancel Scheduled Sends in SendGrid.

Table-Specific Information

Select

This table only supports filtering by BatchId. The following are the only possible SELECT queries:

SELECT * FROM CancelScheduledSends
SELECT * FROM CancelScheduledSends WHERE BatchId = 148825

Update

You can update CancelScheduledSends by specifying an BatchId.

Allowed Values : cancel, pause

UPDATE CancelScheduledSends SET Status = 'cancel' WHERE BatchId = 1310789

Delete

You can only delete from CancelScheduledSends by specifying an BatchId.

DELETE FROM CancelScheduledSends WHERE BatchId = 1310789

Columns


Name	Type	ReadOnly	References	Description
BatchId [KEY]	String	False		The Id of the batch.
Status	String	False		The status of the scheduled send.Allowed Values: cancel, pause

CustomFields

Create, delete, and query Custom Fields in SendGrid.

Table Specific Information

Custom fields are filters for a Recipient that are defined by the user.

Select

You can retrieve all the CustomFields or filter them by Id:

SELECT * FROM CustomFields WHERE Id = '1'

Use quotations to filter on Custom Fields regardless of type.

Insert

To insert on CustomFields specify the Name of the field and its Type. The Type can be text, date, or number.

INSERT INTO CustomFields (name, type) VALUES ('age', 'number')

Update

This table does not support updating.

Delete

You can only delete from CustomFields by specifying an Id:

DELETE FROM CustomFields WHERE Id = '1'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The Id of the custom field.
Name	String	False		The name of the custom field.
Type	String	False		The type of the custom field. Allowed values are number, text, or date.

GlobalUnsubscribes

Query Global Unsubscribes in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. However this filter is optional. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalUnsubscribes WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

INSERT

Performing an Insert operation in this table you need to specify an email which will be added to the GlobalUnsubscribes list. For example:

INSERT INTO GlobalUnsubscribes (Email) VALUES ('[email protected]')

DELETE

Performing a Delete operation in this table you need to specify an email which will be removed from the GlobalUnsubscribes list. For example:

DELETE FROM GlobalUnsubscribes WHERE Email = '[email protected]'

Columns


Name	Type	ReadOnly	References	Description
Email [KEY]	String	False		Email of the unsubscriber.
CreatedAt	Datetime	True		The time the unsubscriber was created.

InvalidEmails

Query and Delete invalid emails in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM InvalidEmails
SELECT * FROM InvalidEmails WHERE Email = '[email protected]'

Delete

You can only delete from InvalidEmails by specifying an Email.

DELETE FROM InvalidEmails WHERE Email = '[email protected]'

Columns


Name	Type	ReadOnly	References	Description
Created	Datetime	True		The time the when the email address was added to the invalid emails list.
Reason	String	False		A reason explaining why the email address was marked as invalid..
Email	String	False		The email address that was marked as invalid.

Lists

Create, update, delete, and query the available Lists in SendGrid.

Table-Specific Information

Select

The Lists table can only filtered by its Id.

SELECT * FROM Lists WHERE Id = '123'

Insert

Only the Name for the List is required to insert into this table.

INSERT INTO Lists (Name) VALUES ('TestList')

Update

You can only update from Lists by specifying an Id.

UPDATE Lists SET Name = 'New Name' WHERE Id = '123'

Delete

You can only delete from Lists by specifying an Id.

DELETE FROM Lists WHERE Id = '123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The Id of the list.
Name	String	False		The name of the list.
RecipientCount	Integer	True		The recipient count of the list.

MarketingCampaigns

Create, update, delete, and query Marketing Campaigns in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM MarketingCampaigns
SELECT * FROM MarketingCampaigns WHERE Id = '1'

Insert

The following columns are required in an insert: Title, Subject, SenderId, ListIds, SegmentIds, Categories, SuppressionGroupId, HtmlContent, and PlainContent.

Note that a CustomUnsubscribeUrl can be used in place of a SuppressionGroupId. However, SendGrid highly recommends to use SuppressionGroupId.

Use the check points below to find the SenderId and SuppressionGroupId inputs:

To find the SenderId, navigate to Marketing -> MarketingCampaigns -> Senders. Create a new sender if you do not have one already. Then click the gear on the Action column and select Edit. The Id is displayed on the navigation bar after /senders/id.
To find the SuppressionGroupId, insert into the UnsubscribeGroups table; the created Id is the SuppressionGroupId.

INSERT INTO MarketingCampaigns (Title, Subject, SenderId, ListIds, Categories, SuppressionGroupId, HtmlContent, PlainContent) VALUES ('Test Campaign', 'March Newsletter', '123', '1,2', 'fruits, health', '12', '<b>Eat Healthy</b>', 'Eat healthy')

Update

You can only update MarketingCampaigns by specifying an Id.

UPDATE MarketingCampaigns SET PlainContent = 'Eat healthier' WHERE Id = '123'

Delete

You can only delete from MarketingCampaigns by specifying an Id.

DELETE FROM MarketingCampaigns WHERE Id = '123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the marketing campaign.
Title	String	False		The title of the marketing campaign.
Subject	String	False		The subject of the marketing campaign.
SenderId	Integer	False		The sender Ids of the marketing campaign, separated by a comma.
ListIds	String	False		The list of list Ids of the marketing campaign, separated by a comma.
SegmentIds	String	False		The list of segment Ids of the marketing campaign, separated by a comma.
Categories	String	False		The categories of the marketing campaign.
SuppressionGroupId	Integer	False		The suppression group Id of the marketing campaign.
CustomUnsubscribeUrl	String	False		The custom unsubscribe URL of the marketing campaign.
IpPool	String	False		The IP pool of the marketing campaign.
HtmlContent	String	False		The HTML content of the marketing campaign.
PlainContent	String	False		The content of the marketing campaign.
Status	String	True		The status of the marketing campaign.

Recipients

The SendGrid table Recipients.

Table Specific Information

SendGrid has the ability to categorize Recipients with filters defined by the user. Accordingly, you can execute statements to the Recipients table to search by and insert these filters. However, these filters are reported as attributes of the Custom Fields table.

Select

The following query retrieves only the default fields of the Recipients table, not the Custom Fields that you have created. To retrieve the values of the Custom Fields of a Recipient, query the Custom Fields table.

You can also retrieve all Recipients inside a certain List or a Segment, but it is not possible to filter by both. Use multiple queries instead:

SELECT * FROM Recipients WHERE SegmentId = '1'
SELECT * FROM Recipients WHERE ListId = '2'

You can build criteria depending on a Custom Field. When querying use quotations regardless of type.This kind of filtering is done client side. In this case Age is a CustomField created by the user.

SELECT * FROM Recipients WHERE Age = '30'

Insert

To insert into Recipients Email is required.

You can insert Custom Fields into the Recipients table. See the CustomFields section for more information about how to create custom fields.

INSERT INTO Recipients (Email, FirstName, LastName, Age, Department ... Other Custom Fields as necessary) VALUES ('[email protected]', 'name', 'surname', '30', 'Education' ... Other Custom Field values)

Update

You can only update from this table by specifying an Id. Additionally, the Email column cannot be updated.

UPDATE Recipients SET Age = '12' WHERE Id = '1'

Delete

You can only delete from this table by specifying an Id.

DELETE FROM Recipients WHERE Id = '1'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		This column is dynamic and maps to the corresponding field in the API.
SegmentId	Int32	False		This column is dynamic and maps to the corresponding field in the API.
ListId	Int32	False		This column is dynamic and maps to the corresponding field in the API.
Email	String	False		This column is dynamic and maps to the corresponding field in the API.
FirstName	String	False		This column is dynamic and maps to the corresponding field in the API.
LastName	String	False		This column is dynamic and maps to the corresponding field in the API.
CreatedAt	Datetime	True		This column is dynamic and maps to the corresponding field in the API.
LastClicked	Datetime	True		This column is dynamic and maps to the corresponding field in the API.
LastEmailed	Datetime	False		This column is dynamic and maps to the corresponding field in the API.
LastOpened	Datetime	True		This column is dynamic and maps to the corresponding field in the API.
UpdatedAt	Datetime	True		This column is dynamic and maps to the corresponding field in the API.
Age	String	False		This column is dynamic and maps to the corresponding field in the API.
Department	String	False		This column is dynamic and maps to the corresponding field in the API.
University	String	False		This column is dynamic and maps to the corresponding field in the API.

Schedules

Create, update, delete, and query Campaign Schedules in SendGrid.

Table-Specific Information

Select

The following is an example to query the Schedules table:

SELECT * FROM Schedules WHERE MarketingCampaignId = '123'

Insert

Provide MarketingCampaignId and SendAt to schedule a campaign at a specific time. You can simply specify SendAt to immediately send a campaign. SendGrid requires that both the html and plain text content have an [unsubscribe] tag in them for a campaign to be sent.

INSERT INTO Schedules (MarketingCampaignId) VALUES (3091692)

INSERT INTO Schedules (MarketingCampaignId, SendAt) VALUES (1, '11/11/2018')

Update

You can only update Schedules by specifying a MarketingCampaignId.

UPDATE Schedules SET SendAt = '01-01-2016' WHERE MarketingCampaignId = '123'

Delete

You can only delete Schedules by specifying a MarketingCampaignId.

DELETE FROM Schedules WHERE MarketingCampaignId = '123'

Columns


Name	Type	ReadOnly	References	Description
MarketingCampaignId [KEY]	Integer	True		Id of the campaign.
Status	String	True		Status of the campaign.
SendAt	Datetime	False		Date to start the campaign.

Segments

Create, update, delete, and query Segments in SendGrid.

Table-Specific Information

Select

You can retrieve all the segments or filter them by Id.

SELECT * FROM Segments WHERE Id = '1'

Insert

A Segment divides Recipients by certain criteria. To specify these criteria use the ConditionsField columns and the Id of the List to apply these criteria to.

The ConditionsAndOr columns connect the given Field with the previous one.

INSERT INTO Segments (Name, ListId, ConditionsField1, ConditionsValue1, ConditionsOperator1, ConditionsAndOr1, ConditionsField2, ConditionsValue2, ConditionsOperator2, ConditionsAndOr2) VALUES ("New Segment", 123, 'age', '20', '>', '', 'age', '30', '<', 'and')

The statement above creates Segments containing Recipients between age 20 and 30.

Update

You can update the name or the conditions. Since the whole set will be overwritten, specify a full set.

UPDATE Segments SET Name = 'Name Updated', ConditionsField1 = 'age', ConditionsOperator1 = '<=', ConditionsAndOr1 = '', ConditionsValue1 = '30' WHERE Id = '123'

Delete

You can only delete Segments by Id:

DELETE FROM Segments WHERE Id = '123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The Id of the segment.
Name	String	False		The name of the segment.
ListId	Integer	False		The Id of the list associated with the segment.
RecipientCount	Integer	True		The recipient count of the segment.
ConditionsField1	String	False		Field name for the first condition.
ConditionsValue1	String	False		Field value for the first condition.
ConditionsOperator1	String	False		Condition operator for the first condition.
ConditionsAndOr1	String	False		How to connect to the previous condition. Allowed values are AND or OR.
ConditionsField2	String	False		Field name for the second condition.
ConditionsValue2	String	False		Field value for the second condition.
ConditionsOperator2	String	False		Condition operator for the second condition.
ConditionsAndOr2	String	False		How to connect to the previous condition. Allowed values are AND or OR.
ConditionsField3	String	False		Field name for the third condition.
ConditionsValue3	String	False		Field value for the third condition.
ConditionsOperator3	String	False		Condition operator for the third condition.
ConditionsAndOr3	String	False		How to connect to the previous condition. Allowed values are AND or OR.
ConditionsField4	String	False		Field name for the fourth condition.
ConditionsValue4	String	False		Field value for the fourth condition.
ConditionsOperator4	String	False		Condition operator for the fourth condition.
ConditionsAndOr4	String	False		How to connect to the previous condition. Allowed values are AND or OR.
ConditionsField5	String	False		Field name for the fifth condition.
ConditionsValue5	String	False		Field value for the fifth condition.
ConditionsOperator5	String	False		Condition operator for the fifth condition.
ConditionsAndOr5	String	False		How to connect to the previous condition. Allowed values are AND or OR.

SenderIdentities

Create, update, delete, and query Sender Identities in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM SenderIdentities
SELECT * FROM SenderIdentities WHERE Id = '148825'

Insert

The following columns are required in an insert: Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country.

INSERT INTO SenderIdentities (Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country) VALUES ('Nickname', 'FromEmail', 'FromName', 'ReplytoEmail', 'Address', 'City', 'Country')

Update

You can update SenderIdentities by specifying an Id.

UPDATE SenderIdentities SET Nickname = 'Nickname', FromEmail = 'FromEmail', FromName = 'FromName', ReplytoEmail = 'ReplytoEmail', Address = 'Address', City = 'City', Country = 'Country' WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM SenderIdentities WHERE Id = 1310789

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		The unique identifier of the sender identity.
Nickname	String	False		A nickname for the sender identity. Not used for sending..
FromEmail	String	False		This is where the email will appear to originate from for your recipient.
FromName	String	False		This is the name appended to the from email field. IE - Your name or company name.
ReplytoEmail	String	False		This is the email that your recipient will reply to.
ReplytoName	String	False		This is the name appended to the reply to email field. IE - Your name or company name.
Address	String	False		The physical address of the sender identity.
Address2	String	False		Additional sender identity address information.
City	String	False		The city of the sender identity.
State	String	False		The state of the sender identity.
Zip	String	False		The zipcode of the sender identity.
Country	String	False		The country of the sender identity.
CreatedAt	Datetime	True		The end of the time range when the bounce was created.
UpdatedAt	Datetime	True		The end of the time range when the bounce was created.

Spam

Query and delete Spam reports in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM Spam
SELECT * FROM Spam WHERE Email = '[email protected]'

Delete

Delete from Spam

You can delete from Spam by specifying an Email.

DELETE FROM Spam WHERE Email = '[email protected]'

Columns


Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time when the spam report was made.
Email [KEY]	String	False		The email address of the recipient who marked your message as spam..
Ip	String	False		The IP address that the message was sent on.

Teammates

Query Teammates in SendGrid.

View-Specific Information

Select

The Cloud will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

Username supports the '=,IN' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Teammates WHERE Username = 'test'

SELECT * FROM Teammates WHERE Username IN ('test', 'test2')

Insert

The following columns are required in an insert: Email and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

The IsAdmin is optional and by default is accepted as false from the API.

INSERT INTO Teammates (Email, Scopes) VALUES ('[email protected]', 'user.profile.read, user.profile.update')

INSERT INTO Teammates (Email, Scopes, IsAdmin) VALUES ('[email protected]', 'user.profile.read, user.profile.update', 'true')

Update

The following columns are required in an update: Username and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

UPDATE Teammates SET Scopes = 'user.profile.read' WHERE Username = 'testuser'

Delete

Only the username column is required to delete a record from Teammates table. For example:

DELETE FROM Teammates WHERE Username = 'testuser'

Columns


Name	Type	ReadOnly	References	Description
Username [KEY]	String	False		The username of the teammate.
Email	String	False		Email of the teammate.
FirstName	String	False		First name of the teammate.
LastName	String	False		Last name of the teammate.
UserType	String	False		User type of the teammate.
IsAdmin	Boolean	False		Indicator if the teammate is admin.
Phone	String	False		Phone of the teammate.
Website	String	False		Website of the teammate.
Company	String	False		Company of the teammate.
Address	String	False		Address of the teammate.
Address2	String	False		Address 2 of the teammate.
City	String	False		City of the teammate.
State	String	False		State of the teammate.
Country	String	False		Country of the teammate.
Zip	String	False		Zip of the teammate.
Scopes	String	False		Scope of the permissions for the new teammate. Multiple values are allowed and should be split by a comma symbol. Possible values are: user.account.read,user.credits.read,user.email.create,user.email.read,user.email.update,user.email.delete,user.profile.create,user.profile.read,user.profile.update,user.profile.delete

UnsubscribeGroups

Create, update, delete, and query Unsubscribe Groups in SendGrid.

Table-Specific Information

Select

UnsubscribeGroups can only be filtered by Id.

SELECT * FROM UnsubscribeGroups WHERE Id = '1'

Insert

Provide a Name and a Description to insert into UnsubscribeGroups:

INSERT INTO UnsubscribeGroups (Name, Description, IsDefault) VALUES ('Unsubscribe1', 'General Unsubscribe', true)

Update

You can only update UnsubscribeGroups by specifying an Id.

UPDATE UnsubscribeGroups SET Name = 'New Name', Description = 'New Description' WHERE Id = '1'

Delete

You can only delete from UnsubscribeGroups by specifying an Id.

DELETE FROM UnsubscribeGroups WHERE Id = '1'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the unsubscribe group.
Name	String	False		The name of the unsubscribe group.
Description	String	False		The description of the unsubscribe group.
LastEmailSentAt	Datetime	True		The last time an email was sent to the unsubscribe group.
IsDefault	Boolean	False		If the unsubscribe group is the default group or not.
Unsubscribes	Integer	True		The number of unsubscribes from this group.

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - SendGrid Views


Name	Description
Categories	Query the available Categories in SendGrid.
CategoryStats	Query the available Category Stats in SendGrid.
GlobalStats	Query the available Global Stats in SendGrid.
GroupSuppressions	Query group suppressions in SendGrid.
IPAccessManagement	Query Global Unsubscribes in SendGrid.
MessageEvents	List of events related to email message in SendGrid.
Messages	Query Email Activities in SendGrid.
ReservedFields	Query the available Reserved Fields in SendGrid.
StatsByBrowser	Query Browser Statistics in SendGrid.
StatsByClient	Query Client Statistics in SendGrid.
StatsByDevice	Query Device Statistics in SendGrid.
StatsByLocation	Query Location Statistics in SendGrid.
StatsByMailboxProvider	Query Mailbox Provider Statistics in SendGrid.
SubuserStats	Query Subuser Statistics in SendGrid.
Users	Query the available Users in SendGrid.

CategoryStats

Query the available Category Stats in SendGrid.

View-Specific Information

Select

The following is an example to query the CategoryStats view:

SELECT * FROM CategoryStats WHERE Name = 'news'

You can also filter by Date. If you do not specify one, a default span of the past 1 year is used. The operators =, >=, <=, <, and > are supported for this column.

SELECT * FROM CategoryStats WHERE Name = 'news' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns


Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the category.
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.
UnsubscribeDrops	Integer		The number of emails in this category dropped by SendGrid because the recipient unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails.

GlobalStats

Query the available Global Stats in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalStats

SELECT * FROM GlobalStats WHERE Date > '2016-01-01' AND Date < '2016-03-30'

Columns


Name	Type	References	Description
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
BounceDrops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.
UnsubscribeDrops	Integer		The number of emails globally dropped by SendGrid because the recipient unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails.

GroupSuppressions

Query group suppressions in SendGrid.

Table-Specific Information

Select

The following are examples to query the GroupSuppressions table:

SELECT * FROM GroupSuppressions WHERE GroupId = 391

SELECT * FROM GroupSuppressions WHERE GroupId IN (391, 392, 393)

Columns


Name	Type	References	Description
GroupId [KEY]	Integer		Email of the unsubscriber.
Emails	String		The time the unsubscriber was created.

IPAccessManagement

Query Global Unsubscribes in SendGrid.

View-Specific Information

Select

The Cloud will use the SendGrid API to retrieve information about the IP addresses which have accessed the account. However there is no filtering supported server-side for this view.

Only the following query is processed server side:

SQL

SELECT * FROM IPAccessManagement

Columns


Name	Type	References	Description
IP	String		IP Address.
Allowed	Boolean		Indicator if this IP is allowed for access.
AuthMethod	String		Authentication method.
FirstAt	Timestamp		First time when IP has accessed the account.
LastAt	Timestamp		Last time when IP has accessed the account.
Location	String		Location of the IP.

MessageEvents

List of events related to email message in SendGrid.

NOTE: If you want access to Email events, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

The Cloud will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

MessageId supports the '=' comparisons.

SQL

SELECT * FROM MessageEvents WHERE MessageId = '1234'

Columns


Name	Type	References	Description
MessageId	String		The Id of the message.
EventName	String		Name of event. The allowed values are bounced, opened, clicked, processed, dropped, delivered, deferred, spam_report, unsubscribe, group_unsubscribe, group_resubscribe.
Processed	Datetime		The date when the event was processed.
Reason	String		Explanation of what caused the message to be 'bounced', 'deferred', or 'blocked'.
AttemptNum	Integer		Used with 'deferred' events to indicate the attempt number out of 10.
Url	String		Used with 'clicked' event to indicate which url the user clicked.
BounceType	String		Use to distinguish between types of bounces. The allowed values are soft, hard.
HttpUserAgent	String		Client recipient used to click or open message.
MXServer	String		The MX server that received the email.
ApiKeyId	String		Id of the API Key.
AsmGroupId	String		ASM Group Id.
Categories	String		Categories users associated to the message.
UniqueArgs	String		JSON hash of key-value pairs associated with the message.
OriginatingIp	String		This is the IP address of the person sending the message
OutboundIp	String		This is the SendGrid dedicated IP address used to send the email
OutboundIpType	String		The type of the outbound IP.
Teammate	String		Username of the teammate.
TemplateId	String		Template ID of the email.

Messages

Query Email Activities in SendGrid.

NOTE: If you want access to Email activities, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

The Cloud will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

Note: The API will return only 1000 records. Please choose from the available filters to restrict the search.

Id supports the '=,IN' comparisons.
Status supports the '=,!=,LIKE,NOT LIKE' comparisons.
Subject supports the '=,!=,LIKE,NOT LIKE' comparisons.
FromEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
ToEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
Clicks supports the '>,<,<=,>=,=,!=' comparisons.
LastEventTime supports the '>=, <=, BETWEEN' comparisons.
ApiKeyId supports the '=,!=,LIKE,NOT LIKE' comparisons.
AsmGroupId supports the '=,!=,LIKE,NOT LIKE' comparisons.
MarketingCampaignId supports the '=,!=,LIKE,NOT LIKE' comparisons.
MarketingCampaignName supports the '=,!=,LIKE,NOT LIKE' comparisons.
OriginatingIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
OutboundIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
Teammate supports the '=,!=,LIKE,NOT LIKE' comparisons.
TemplateId supports the '=,!=,LIKE,NOT LIKE' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Messages WHERE Id = '1234'

SELECT * FROM Messages WHERE Id IN ('1234', '4567')

SELECT * FROM Messages WHERE Status = 'not_delivered'

SELECT * FROM Messages WHERE LastEventTime BETWEEN '2020-01-01' AND '2020-01-15'

Columns


Name	Type	References	Description
Id [KEY]	String		The Id of the message.
Status	String		Status of the email.
Subject	String		Subject of the email.
FromEmail	String		The email of the sender.
ToEmail	String		The email of the receiver.
ClicksCount	Integer		The number of clicks.
OpensCount	Integer		The number of times that the email was opened.
LastEventTime	Datetime		The datetime of the event.
ApiKeyId	String		Id of the API Key.
AsmGroupId	String		ASM Group Id.
MarketingCampaignId	String		The id of the marketing campaign.
MarketingCampaignName	String		The name of the marketing campaign.
MarketingCampaignSplitId	String		Split id of the campaign.
MarketingCampaignVersion	String		Version of the campaign.
OriginatingIp	String		This is the IP address of the person sending the message
OutboundIp	String		This is the SendGrid dedicated IP address used to send the email
OutboundIpType	String		The type of the outbound IP.
Teammate	String		Username of the teammate.
TemplateId	String		Template ID of the email.
Events	String		List of events related to email message in SendGrid.

ReservedFields

Query the available Reserved Fields in SendGrid.

View-Specific Information

Select

This table does not support filtering.

SELECT * FROM ReservedFields

Columns


Name	Type	References	Description
Name	String		The name of the reserved field.
Type	String		The type of the reserved field.

StatsByBrowser

Query Browser Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByBrowser WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Browser Statistics by name.

SELECT * FROM StatsByBrowser WHERE Name = 'Firefox'

Columns


Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Clicks	Integer		The number of links clicked in your emails.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.

StatsByClient

Query Client Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByClient WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Client Statistics by name.

SELECT * FROM StatsByClient WHERE Name = 'Other'

Columns


Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

StatsByDevice

Query Device Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByDevice WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Device Statistics by name.

SELECT * FROM StatsByDevice WHERE Name = 'Phone'

Columns


Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

StatsByLocation

Query Location Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByLocation WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Location Statistics by name.

SELECT * FROM StatsByLocation WHERE Name = 'US'

To query Location Statistics by type.

SELECT * FROM StatsByLocation WHERE Type = 'country'

Columns


Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Clicks	Integer		The number of links clicked in your emails.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

StatsByMailboxProvider

Query Mailbox Provider Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByMailboxProvider

SELECT * FROM StatsByMailboxProvider WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Mailbox Provider Statistics by name.

SELECT * FROM StatsByMailboxProvider WHERE Name = 'Microsoft Outlook Live'

Columns


Name	Type	References	Description
Type	String		The type of the statistic. Avaible values (categorie, device, client, mailbox_provider, browser, subuser)
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

SubuserStats

Query Subuser Statistics in SendGrid.

View-Specific Information

Select

You can filter by SubuserName to retrieve stats of a specific subuser.

SELECT * FROM SubuserStats WHERE SubuserName = 'Example'

You can also filter by Date. Operators =, >=, <=, <, and > are supported for this column. If you do not specify one, a default span of the past 1 year is used.

SELECT * FROM SubuserStats WHERE SubuserName = 'News' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns


Name	Type	References	Description
Id [KEY]	String		The Id of the subuser statistic.
SubuserName	String		The name of the subuser.
Date	Datetime		The time the subuser statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links that were clicked in the emails from this subuser.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails from this subsuer that were confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times emails from this subuser were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid by this subuser.
Requests	Integer		The number of emails that you requested to send via SendGrid from this subuser.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails from this subuser that were marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked on links in your emails from this subuser.
UniqueOpens	Integer		The number of unique recipients who opened your emails from this subuser.
UnsubscribeDrops	Integer		The number of emails from this subuser dropped by SendGrid because the recipients unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails from this subuser.
Name	String		The name of the statistic.
Type	String		The type of the statistic.

Users

Query the available Users in SendGrid.

View-Specific Information

Select

Users can only be filtered by Id.

SELECT * FROM Users WHERE Id = '1'

Columns


Name	Type	References	Description
Id [KEY]	String		The Id of the user.
Address	String		The address of the user.
Address2	String		The secondary address field for the user.
City	String		The city of the user.
Company	String		The company of the user.
Country	String		The country of the user.
FirstName	String		The first name of the user.
LastName	String		The last name of the user.
Phone	String		The phone number of the user.
State	String		The state the user is located in.
Website	String		The Web site URL of the user.
Zip	String		The ZIP code of the user.

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with SendGrid.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from SendGrid, along with an indication of whether the procedure succeeded or failed.

CData Cloud - SendGrid Stored Procedures


Name	Description
AddRecipientToList	Adds a recipient to a list.
GetImportContactsStatus	Gets the Import contacts status
ImportMarketingContacts	Import contacts to sendgrid account from CSV file.
SendMail	Sends a mail.
SendMail2	Sends a mail. This Stored Procedure is used to handle aggregates.
SendTestCampaign	Sends a test campaign.

AddRecipientToList

Adds a recipient to a list.

Input


Name	Type	Required	Description
ListId	String	False	The unique identifier for the list.
RecipientId	String	False	The Id of the recipient.

Result Set Columns


Name	Type	Description
Success	String	Whether or not the recipient was successfully added to the list.

GetImportContactsStatus

Gets the Import contacts status

Stored Procedure Specific Information

The GetImportContactsStatus Stored Procedure requires JobId of the Import Contacts, which we can get by executing ImportMarketingContacts stored procedure.

Example:

EXEC GetImportContactsStatus JobId= '1234'

Input


Name	Type	Required	Description
JobId	String	True	The Id of the Import Contacts Job

Result Set Columns


Name	Type	Description
Success	String	The Staus of the corresponding Import Contacts Job.
RequestedCount	String
CreatedCount	String
UpdatedCount	String
DeletedCount	String

ImportMarketingContacts

Import contacts to sendgrid account from CSV file.

Stored Procedure Specific Information

The ImportMarketingContacts Stored Procedure requires Field Mapping Ids and path of the File which contains contacts data.

We can Use custom_field ids or reserved_field ids for Field Mapping and We can get these ids using "Get All Field Definitions" endpoint.

NOTE:

1. Email field mapping is mandatory to import contacts from file.

2. As of now only csv file format is supported for this operation.

Example:

EXEC ImportMarketingContacts FileType= 'csv' , FieldMappings= '_rf2_T,_rf9_T' , FilePath = 'C:\Dev\Tests\v20\ProviderSendgrid\example.csv'

Input


Name	Type	Required	Description
FileType	String	False	The type of the file The allowed values are csv.
FieldMappings	String	True	Comma separated list of field_definition IDs
ListIds	String	False	Comma separated list of List id's
FilePath	String	False	The absolute path of the File to import contacts

Result Set Columns


Name	Type	Description
JobId	String	The Id of the Import Contacts Job

SendMail

Sends a mail.

Procedure-Specific Information

The DynamicTemplateData input requires information about dynamic data of the template. Value for the different variables put into templates.

Example: {"fullname":"Test","paymenttotal":"Test","description":"Test"}

NOTE: The TemplateId column is required when using Dynamic Templates.

Process of adding Persanalizations object to SendMail Stored Procedure

SendMail Requires atleast one personalizations object in the request body and it can be added using the aggregate function.

For Example:

Using TEMP Tables.

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'abc')

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'xyz')

INSERT INTO To#TEMP (ToEmail) VALUES ('[email protected]')

INSERT INTO ToCC#TEMP (ToCCName, ToCCEmail) VALUES ('[email protected]', 'xyz')

INSERT INTO ToBCC#TEMP (ToBCCEmail, ToBCCName) VALUES ('[email protected]', 'abc')

INSERT INTO Content#TEMP (ContentValue, ContentType) VALUES ('[email protected]', 'text/plain')

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2', 'To#TEMP', 'ToCC#TEMP', 'ToBCC#TEMP')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content = 'Content#TEMP'

Using Aggregate values.

Send Mail to single Mail Address.

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2',
         '[
		   {
              "ToEmail": "[email protected]",
              "ToName": "abc"
           }
		  ]',
		  '[
            {
              "ToCCName": "xyz",
              "ToCCEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToBCCEmail": "[email protected]",
              "ToBCCName": "abcd"
            }
          ]')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content =
'{
  "ContentValue":"content",
  "ContentType":"text/plain"
}'

Send Mail to Multiple Mail Addresses.

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2',
          '[
		    {
              "ToEmail": "[email protected]",
              "ToName": "abc"
            },
            {
              "ToEmail": "[email protected]",
              "ToName": "xyz"
            },
            {
              "ToEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToCCName": "sac",
              "ToCCEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToBCCEmail": "xyz",
              "ToBCCName": "[email protected]"
            }
          ]')
		  
EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content =
'{
  "ContentValue":"content",
  "ContentType":"text/html"
}'

Send Mail using attachment.

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'abc')

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'xyz')

INSERT INTO To#TEMP (ToEmail) VALUES ('[email protected]')

INSERT INTO ToCC#TEMP (ToCCName, ToCCEmail) VALUES ('[email protected]', 'xyz')

INSERT INTO ToBCC#TEMP (ToBCCEmail, ToBCCName) VALUES ('[email protected]', 'abc')

INSERT INTO Content#TEMP (ContentValue, ContentType) VALUES ('[email protected]', 'text/plain')

INSERT INTO Attachments#TEMP (AttachmentContent, AttachmentFilename) VALUES ('abcdefghijklmn', 'Test2.png')

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2', 'To#TEMP', 'ToCC#TEMP', 'ToBCC#TEMP')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content = 'Content#TEMP', Attachments = 'Attachments#TEMP'

Input


Name	Type	Required	Description
Subject	String	True	The subject of the mail to be sent.
ContentValue	String	False	The content of the mail to be sent.
ContentType	String	False	The type of content to be sent. supported values are text/plain and text/html
Content	String	True	The aggregate for Content object
SendAt	String	False	The date and time when you want your email to be delivered. Scheduling more than 72 hours in advance is forbidden.
FromEmail	String	True	The email address of the sender.
FromName	String	False	The name of the sender.
ToEmail	String	False	Email address to send the mail to
ToName	String	False	Email's respective names to send the mail to.
To	String	False	The aggregate for To recipients
ToCCEmail	String	False	Email address of the recipients who will receive a copy of your email
ToCCName	String	False	Name of the recipients who will receive a copy of your email.
ToCC	String	False	The aggregate for ToCC recipients.
ToBCCEmail	String	False	Email address of the recipients who will receive a blind carbon copy of your email.
ToBCCName	String	False	Name of the recipients who will receive a blind carbon copy of your email.
ToBCC	String	False	The aggregate for ToBCC recipients.
ReplyToEmail	String	False	The email address of the person to whom you are replying to.
ReplyToName	String	False	The name of the person to whom you are replying to.
AttachmentPaths	String	False	Path of the file to attach to the mail to be sent
TemplateId	String	False	The Id of the template that you may want to send
DynamicTemplateData	String	False	The data of the dynamic template.
Personalizations	String	True	An array of messages and their metadata. Each object within personalizations can be thought of as an envelope - it defines who should receive an individual message and how that message should be handled.
AttachmentFilename	String	False	The attachment's filename. Specify only when you don't specify the AttachmentPaths.
Attachments	String	False	An array of objects where you can specify any attachments you want to include. Specify only when you don't specify the AttachmentPaths.

Result Set Columns


Name	Type	Description
Success	String	Whether or not the mail was sent successfully.

SendMail2

Sends a mail. This Stored Procedure is used to handle aggregates.

Procedure-Specific Information

SendMail2 Stored Procedure is specifically made for handling the aggregates. User has to provide the whole json structure in order to execute this Stored Procedure.

For Example:

EXECUTE SendMail2 Personalizations = '[{"to": [{"email": "[email protected]", "name": "John Doe"}, {"email": "[email protected]", "name": "Julia Doe"}], "cc": [{"email": "[email protected]", "name": "Jane Doe"}], "bcc": [{"email": "[email protected]", "name": "Jim Doe"}]}, {"from": {"email": "[email protected]", "name": "Example Sales Team"}, "to": [{"email": "[email protected]", "name": "Janice Doe"}], "bcc": [{"email": "[email protected]", "name": "Jordan Doe"}]}]', FromEmail = '[email protected]', FromName = 'Example Order Confirmation', Subject = 'Test Subject', Content = '[{"type": "text/html", "value": "test value"}]', Categories = '["cake", "pie"]'

Input


Name	Type	Required	Description
Personalizations	String	True	An array of messages and their metadata. Each object within personalizations can be thought of as an envelope - it defines who should receive an individual message and how that message should be handled.
FromEmail	String	True	The email address of the sender.
FromName	String	True	The name of the sender.
ReplyToEmail	String	False	The email address of the person to whom you are replying to.
ReplyToName	String	False	The name of the person to whom you are replying to.
Subject	String	True	The global or 'message level' subject of your email.
Content	String	True	An array where you can specify the content of your email.
Attachments	String	False	An array of objects where you can specify any attachments you want to include.
Categories	String	False	An array of category names for this message. Each category name may not exceed 255 characters.
SendAt	String	False	A unix timestamp allowing you to specify when you want your email to be delivered.
BatchId	String	False	An ID representing a batch of emails to be sent at the same time.
AsmGroupId	String	False	The unsubscribe group to associate with this email.
AsmGroupsToDisplay	String	False	An array containing the unsubscribe groups that you would like to be displayed on the unsubscribe preferences page.
IpPoolName	String	False	The IP Pool that you would like to send this email from.
MailSettingsByPassListManagementEnable	String	False	A collection of different mail settings that you can use to specify how you would like this email to be handled. Indicates if this setting is enabled.
MailSettingsFooterEnable	String	False	The default footer that you would like included on every email. Indicates if this setting is enabled.
MailSettingsSandboxModeEnable	String	False	Sandbox Mode allows you to send a test email to ensure that your request body is valid and formatted correctly. Indicates if this setting is enabled.
TrackingSettingsClickTrackingEnable	String	False	Allows you to track if a recipient clicked a link in your email. Indicates if this setting is enabled.
TrackingSettingsClickTrackingEnableText	String	False	Allows you to track if a recipient clicked a link in your email. Indicates if this setting should be included in the text/plain portion of your email.
TrackingSettingsOpenTrackingEnable	String	False	Allows you to track if the email was opened by including a single pixel image in the body of the content. When the pixel is loaded, Twilio SendGrid can log that the email was opened. Indicates if this setting is enabled.
TrackingSettingsOpenTrackingSubstitutionTag	String	False	Allows you to track if the email was opened by including a single pixel image in the body of the content. When the pixel is loaded, Twilio SendGrid can log that the email was opened. Allows you to specify a substitution tag that you can insert in the body of your email at a location that you desire. This tag will be replaced by the open tracking pixel.
TrackingSettingsSubscriptionTrackingEnable	String	False	Allows you to insert a subscription management link at the bottom of the text and HTML bodies of your email. Indicates if this setting is enabled.

Result Set Columns


Name	Type	Description
Success	String	Whether or not the mail was sent successfully.

SendTestCampaign

Sends a test campaign.

Input


Name	Type	Required	Description
To	String	False	The email address to send the campaign to.
Campaign_ID	String	False	The Id of the campaign to send.

Result Set Columns


Name	Type	Description
Success	String	Whether or not the campaign was sent successfully.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for SendGrid:

sys_catalogs: Lists the available databases.
sys_schemas: Lists the available schemas.
sys_tables: Lists the available tables and views.
sys_tablecolumns: Describes the columns of the available tables and views.
sys_procedures: Describes the available stored procedures.
sys_procedureparameters: Describes stored procedure parameters.
sys_keycolumns: Describes the primary and foreign keys.
sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

sys_connection_props: Returns information on the available connection properties.
sys_sqlinfo: Describes the SELECT queries that the Cloud can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations::

sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns


Name	Type	Description
CatalogName	String	The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas

Columns


Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables

Columns


Name	Type	Description
CatalogName	String	The database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view.
TableType	String	The table type (table or view).
Description	String	A description of the table or view.
IsUpdateable	Boolean	Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the MarketingCampaigns table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='MarketingCampaigns'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The storage size of the column.
DisplaySize	Int32	The designated column's normal maximum width in characters.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsHidden	Boolean	Whether the column is hidden.
IsArray	Boolean	Whether the column is an array.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures

Columns


Name	Type	Description
CatalogName	String	The database containing the stored procedure.
SchemaName	String	The schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure.
Description	String	A description of the stored procedure.
ProcedureType	String	The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the AddRecipientToList stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='AddRecipientToList' AND Direction=1 OR Direction=2

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure containing the parameter.
ColumnName	String	The name of the stored procedure parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName	String	The name of the data type.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
IsRequired	Boolean	Whether the parameter is required for execution of the procedure.
IsArray	Boolean	Whether the parameter is an array.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the MarketingCampaigns table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='MarketingCampaigns'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.
ForeignKeyType	String	Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
KeySeq	String	The sequence number of the primary key.
KeyName	String	The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:sendgrid:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns


Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.
Sensitivity	String	The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName	String	A camel-cased truncated form of the connection property name.
Ordinal	Int32	The index of the parameter.
CatOrdinal	Int32	The index of the parameter category.
Hierarchy	String	Shows dependent properties associated that need to be set alongside this one.
Visible	Boolean	Informs whether the property is visible in the connection UI.
ETC	String	Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.


Name	Description	Possible Values
AGGREGATE_FUNCTIONS	Supported aggregation functions.	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	Whether COUNT function is supported.	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	The opening character used to escape an identifier.	[
IDENTIFIER_QUOTE_CLOSE_CHAR	The closing character used to escape an identifier.	]
SUPPORTED_OPERATORS	A list of supported SQL operators.	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	Whether GROUP BY is supported, and, if so, the degree of support.	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	Supported string functions.	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	Supported numeric functions.	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	Supported date/time functions.	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS	A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN	String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION	Indicates if the provider supports transactions such as commit and rollback.	YES, NO
DIALECT	Indicates the SQL dialect to use.
KEY_PROPERTIES	Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS	Indicates if multiple schemas may exist for the provider.	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	Indicates if multiple catalogs may exist for the provider.	YES, NO
DATASYNCVERSION	The CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData Data Sync category of this driver.	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	Whether enhanced SQL functionality beyond what is offered by the API is supported.	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	Whether batch operations are supported.	YES, NO
SQL_CAP	All supported SQL capabilities for this driver.	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY	Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.	YES, NO
PSEUDO_COLUMNS	A string array indicating the available pseudo columns.
MERGE_ALWAYS	If the value is true, The Merge Mode is forcibly executed in Data Sync.	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION	Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE	Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY	A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION	Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID	Indicates whether the CheckCache statement should be done against the parent key column.	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns


Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity

Columns


Name	Type	Description
Id	String	The database-generated Id returned from a data modification operation.
Batch	String	An identifier for the batch. 1 for a single operation.
Operation	String	The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message	String	SUCCESS or an error message if the update in the batch failed.

Connection String Options

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

Property	Description
APIKey	The API key of the currently authenticated user.

SSL

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

Logging

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

Schema

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Schema	The type of schema to use.

Miscellaneous

Property	Description
IncludeCustomFields	A boolean indicating if you would like to include custom fields in the column listing.
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.

Authentication

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

Property	Description
APIKey	The API key of the currently authenticated user.

APIKey

The API key of the currently authenticated user.

Data Type

string

Default Value

""

Remarks

The API key of the currently authenticated user. Note that it is a manadatory parameter to authenticate through SendGrid Cloud.

SSL

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:


Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Logging

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

Verbosity

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.

Schema

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Schema	The type of schema to use.

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Schema

The type of schema to use.

Possible Values

NMC, Legacy

Data Type

string

Default Value

"NMC"

Remarks

The schemas available are NMC and Legacy.

Miscellaneous

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

Property	Description
IncludeCustomFields	A boolean indicating if you would like to include custom fields in the column listing.
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Cloud throws an exception.

Cloud

SendGrid - CData Cloud

Overview

Key Features

Getting Started

Connecting to SendGrid

Accessing Data from CData Cloud Services

Establishing a Connection

Obtaining an API Key

SSL Configuration

Customizing the SSL Configuration

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

Other Proxies

Data Model

New Marketing Campaigns Data Model

Legacy Data Model

New Marketing Campaigns Data Model

Tables

Views

Stored Procedures

Tables

CData Cloud - SendGrid Tables

Alerts

Table-Specific Information

Select

Insert

Update

Delete

Columns

ApiKeys

Table-Specific Information

Select

Insert

Update

Delete

Columns

Blocks

Table-Specific Information

Select

Delete

Columns

Bounces

Table-Specific Information

Select

Delete

Columns

CancelScheduledSends

Table-Specific Information

Select

Update

Delete

Columns

GlobalUnsubscribes

Table-Specific Information

Select

INSERT

DELETE

Columns

InvalidEmails

Table-Specific Information

Select

Delete

Columns

NMCCustomFields

Table Specific Information

Select

Insert

Update

Delete

Columns

NMCLists

Table-Specific Information

Select

Insert

Update

Delete

Columns

NMCSegments