SQL Server guide

Download attachments by parent record

This example set demonstrates how to use the download_attachments_by_parent_search endpoint. This endpoint first finds parent rows (for example, records in the problem table) using a parent-level Query, then downloads attachments for each matching parent record using the AttachmentQuery filter. Because it performs a per-parent lookup, it can be slower than direct attachment-table downloads, but it is useful when you must filter by parent record attributes (for example, problems updated in the last 2 days).

Use the AttachmentQuery to apply server-side filters on the sys_attachment table (examples: file_nameENDSWITH.png, content_typeSTARTSWITHimage/, size_bytes>1048576, or date placeholders like <<today-2d,FUN_TO_DATETIME>>). Specify SaveFolder for the download directory and OverwriteFile to control file overwriting (True to always overwrite, False to fail if the file exists). Replace sample sys_id and table names with values from your instance.

Note: downloaded files are saved using a unique filename pattern {table_sys_id}_{attachment_sys_id}_{attachment-file_name}. This prevents overwriting when multiple parent records contain attachments with the same filename and you save them into the same download directory.

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.

-- Download attachments for specific parent records (multiple parent IDs) to c:\temp (always overwrite)
SELECT * FROM download_attachments_by_parent_search
WITH(TableName='problem', Query='sys_idIN62304320731823002728660c4cf6a7e8,6632130c730123002728660c4cf6a734', SaveFolder='c:\temp', OverwriteFile='True')

-- Parent-level filter: problems updated in last 2 days; attachment-level filter: file name ends with .png or .zip; save to c:\temp (fail if file exists)
SELECT * FROM download_attachments_by_parent_search
WITH(
  TableName='problem',
  Query='sys_updated_on>=<<today-2d,FUN_TO_DATETIME>>',
  AttachmentQuery='file_nameENDSWITH.png^ORfile_nameENDSWITH.zip',
  SaveFolder='c:\temp',
  OverwriteFile='False'
)

-- Parent-level filter by sys_id and attachment filter by exact file name; save to c:\temp
SELECT * FROM download_attachments_by_parent_search
WITH(TableName='problem', Query='sys_id=62304320731823002728660c4cf6a7e8', AttachmentQuery='file_name=dump.png', SaveFolder='c:\temp', OverwriteFile='True')

-- Attachments larger than 1 MB for problems updated in the last month; save to c:\temp
SELECT * FROM download_attachments_by_parent_search
WITH(
  TableName='problem',
  Query='sys_updated_on>=<<today-1m,FUN_TO_DATETIME>>',
  AttachmentQuery='size_bytes>1048576',
  SaveFolder='c:\temp',
  OverwriteFile='True'
)

-- Only image attachments for a given parent; save to c:\temp (fail if file exists)
SELECT * FROM download_attachments_by_parent_search
WITH(TableName='problem', Query='sys_id=62304320731823002728660c4cf6a7e8', AttachmentQuery='content_typeSTARTSWITHimage/', SaveFolder='c:\temp', OverwriteFile='False')

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_SERVICENOW_IN_GATEWAY], '-- Download attachments for specific parent records (multiple parent IDs) to c:\temp (always overwrite)
SELECT * FROM download_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_idIN62304320731823002728660c4cf6a7e8,6632130c730123002728660c4cf6a734'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Parent-level filter: problems updated in last 2 days; attachment-level filter: file name ends with .png or .zip; save to c:\temp (fail if file exists)
SELECT * FROM download_attachments_by_parent_search
WITH(
  TableName=''problem'',
  Query=''sys_updated_on>=<<today-2d,FUN_TO_DATETIME>>'',
  AttachmentQuery=''file_nameENDSWITH.png^ORfile_nameENDSWITH.zip'',
  SaveFolder=''c:\temp'',
  OverwriteFile=''False''
)

-- Parent-level filter by sys_id and attachment filter by exact file name; save to c:\temp
SELECT * FROM download_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_id=62304320731823002728660c4cf6a7e8'', AttachmentQuery=''file_name=dump.png'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Attachments larger than 1 MB for problems updated in the last month; save to c:\temp
SELECT * FROM download_attachments_by_parent_search
WITH(
  TableName=''problem'',
  Query=''sys_updated_on>=<<today-1m,FUN_TO_DATETIME>>'',
  AttachmentQuery=''size_bytes>1048576'',
  SaveFolder=''c:\temp'',
  OverwriteFile=''True''
)

-- Only image attachments for a given parent; save to c:\temp (fail if file exists)
SELECT * FROM download_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_id=62304320731823002728660c4cf6a7e8'', AttachmentQuery=''content_typeSTARTSWITHimage/'', SaveFolder=''c:\temp'', OverwriteFile=''False'')')

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_SERVICENOW_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = '-- Download attachments for specific parent records (multiple parent IDs) to c:\temp (always overwrite)
SELECT * FROM download_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_idIN62304320731823002728660c4cf6a7e8,6632130c730123002728660c4cf6a734'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Parent-level filter: problems updated in last 2 days; attachment-level filter: file name ends with .png or .zip; save to c:\temp (fail if file exists)
SELECT * FROM download_attachments_by_parent_search
WITH(
  TableName=''problem'',
  Query=''sys_updated_on>=<<today-2d,FUN_TO_DATETIME>>'',
  AttachmentQuery=''file_nameENDSWITH.png^ORfile_nameENDSWITH.zip'',
  SaveFolder=''c:\temp'',
  OverwriteFile=''False''
)

-- Parent-level filter by sys_id and attachment filter by exact file name; save to c:\temp
SELECT * FROM download_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_id=62304320731823002728660c4cf6a7e8'', AttachmentQuery=''file_name=dump.png'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Attachments larger than 1 MB for problems updated in the last month; save to c:\temp
SELECT * FROM download_attachments_by_parent_search
WITH(
  TableName=''problem'',
  Query=''sys_updated_on>=<<today-1m,FUN_TO_DATETIME>>'',
  AttachmentQuery=''size_bytes>1048576'',
  SaveFolder=''c:\temp'',
  OverwriteFile=''True''
)

-- Only image attachments for a given parent; save to c:\temp (fail if file exists)
SELECT * FROM download_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_id=62304320731823002728660c4cf6a7e8'', AttachmentQuery=''content_typeSTARTSWITHimage/'', SaveFolder=''c:\temp'', OverwriteFile=''False'')'
EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY]