Delete multiple messages with search condition

Deletes multiple messages that match a search condition. Supply User Id, MailFolder Id, and search criteria (e.g. has attachments, sent after date, subject contains text, from address). Only messages matching all conditions are deleted.

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

SELECT * FROM delete_messages_by_search
--To delete max N messages set limit below
--LIMIT 5 
WITH(
 	  MailFolderId='INBOX', --or use mailbox ID
	  UserId='me', --or use "user-id" or use "email"
	  --UserId='1487fe8b-f09f-4015-a817-b8b9fe2a3edc',
	  --search mail with attachments, sent after 2023-03-01, subject contains string "invoice" and  from email is microsoft-noreply@microsoft.com
	  --Your Microsoft invoice G073237208 is ready
	  SearchCriteria='hasAttachments eq true and sentDateTime gt 2025-07-01 and contains(subject, ''Your Microsoft invoice'') and from/emailAddress/address eq ''microsoft-noreply@microsoft.com''  '
)
--Check this link to learn more about search conditions: https://learn.microsoft.com/en-us/graph/filter-query-parameter?tabs=http

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_OUTLOOK_MAIL_OFFICE_365_IN_GATEWAY], 'SELECT * FROM delete_messages_by_search
--To delete max N messages set limit below
--LIMIT 5 
WITH(
 	  MailFolderId=''INBOX'', --or use mailbox ID
	  UserId=''me'', --or use "user-id" or use "email"
	  --UserId=''1487fe8b-f09f-4015-a817-b8b9fe2a3edc'',
	  --search mail with attachments, sent after 2023-03-01, subject contains string "invoice" and  from email is microsoft-noreply@microsoft.com
	  --Your Microsoft invoice G073237208 is ready
	  SearchCriteria=''hasAttachments eq true and sentDateTime gt 2025-07-01 and contains(subject, ''''Your Microsoft invoice'''') and from/emailAddress/address eq ''''microsoft-noreply@microsoft.com''''  ''
)
--Check this link to learn more about search conditions: https://learn.microsoft.com/en-us/graph/filter-query-parameter?tabs=http')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_OUTLOOK_MAIL_OFFICE_365_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM delete_messages_by_search
--To delete max N messages set limit below
--LIMIT 5 
WITH(
 	  MailFolderId=''INBOX'', --or use mailbox ID
	  UserId=''me'', --or use "user-id" or use "email"
	  --UserId=''1487fe8b-f09f-4015-a817-b8b9fe2a3edc'',
	  --search mail with attachments, sent after 2023-03-01, subject contains string "invoice" and  from email is microsoft-noreply@microsoft.com
	  --Your Microsoft invoice G073237208 is ready
	  SearchCriteria=''hasAttachments eq true and sentDateTime gt 2025-07-01 and contains(subject, ''''Your Microsoft invoice'''') and from/emailAddress/address eq ''''microsoft-noreply@microsoft.com''''  ''
)
--Check this link to learn more about search conditions: https://learn.microsoft.com/en-us/graph/filter-query-parameter?tabs=http'
EXEC (@MyQuery) AT [LS_TO_OUTLOOK_MAIL_OFFICE_365_IN_GATEWAY]