SQL Server guide

Download attachments

This example set demonstrates how to use the download_attachments endpoint. This endpoint downloads attachment files directly from the ServiceNow sys_attachment table based on server-side filters. It retrieves and saves files to a specified folder, allowing fast filtering on attachment-level attributes. For parent-level filtering (slower per-parent processing), use the Download Table Attachments By Parent Row Search endpoint instead.

Use the Query parameter for 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). You can also specify a ParentSysId to limit to a single parent record or use SysId for a specific attachment. 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 all attachments for the parent table 'problem' to c:\temp (always overwrite existing files)
SELECT * FROM download_attachments WITH(TableName='problem', SaveFolder='c:\temp', OverwriteFile='True')

-- Download attachments where file name ends with .png or .zip to c:\temp (fail if file exists)
SELECT * FROM download_attachments WITH(TableName='problem', Query='file_nameENDSWITH.png^ORfile_nameENDSWITH.zip', SaveFolder='c:\temp', OverwriteFile='False')

-- Download all attachments for a specific parent record (by parent sys_id) to c:\temp
SELECT * FROM download_attachments WITH(TableName='problem', ParentSysId='62304320731823002728660c4cf6a7e8', SaveFolder='c:\temp', OverwriteFile='True')

-- Download a single attachment by exact attachment sys_id to c:\temp
SELECT * FROM download_attachments WITH(SysId='4b2d41168396b21032ddb9f6feaad38f', SaveFolder='c:\temp', OverwriteFile='True')

-- Download attachments uploaded in the last 2 days to c:\temp
SELECT * FROM download_attachments WITH(TableName='problem', Query='sys_created_on>=<<today-2d,FUN_TO_DATETIME>>', SaveFolder='c:\temp', OverwriteFile='True')

-- Download only image attachments larger than 1 MB to c:\temp
SELECT * FROM download_attachments WITH(TableName='problem', Query='content_typeSTARTSWITHimage/^size_bytes>1048576', SaveFolder='c:\temp', OverwriteFile='False')

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_SERVICENOW_IN_GATEWAY], '-- Download all attachments for the parent table ''problem'' to c:\temp (always overwrite existing files)
SELECT * FROM download_attachments WITH(TableName=''problem'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Download attachments where file name ends with .png or .zip to c:\temp (fail if file exists)
SELECT * FROM download_attachments WITH(TableName=''problem'', Query=''file_nameENDSWITH.png^ORfile_nameENDSWITH.zip'', SaveFolder=''c:\temp'', OverwriteFile=''False'')

-- Download all attachments for a specific parent record (by parent sys_id) to c:\temp
SELECT * FROM download_attachments WITH(TableName=''problem'', ParentSysId=''62304320731823002728660c4cf6a7e8'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Download a single attachment by exact attachment sys_id to c:\temp
SELECT * FROM download_attachments WITH(SysId=''4b2d41168396b21032ddb9f6feaad38f'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Download attachments uploaded in the last 2 days to c:\temp
SELECT * FROM download_attachments WITH(TableName=''problem'', Query=''sys_created_on>=<<today-2d,FUN_TO_DATETIME>>'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Download only image attachments larger than 1 MB to c:\temp
SELECT * FROM download_attachments WITH(TableName=''problem'', Query=''content_typeSTARTSWITHimage/^size_bytes>1048576'', 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 all attachments for the parent table ''problem'' to c:\temp (always overwrite existing files)
SELECT * FROM download_attachments WITH(TableName=''problem'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Download attachments where file name ends with .png or .zip to c:\temp (fail if file exists)
SELECT * FROM download_attachments WITH(TableName=''problem'', Query=''file_nameENDSWITH.png^ORfile_nameENDSWITH.zip'', SaveFolder=''c:\temp'', OverwriteFile=''False'')

-- Download all attachments for a specific parent record (by parent sys_id) to c:\temp
SELECT * FROM download_attachments WITH(TableName=''problem'', ParentSysId=''62304320731823002728660c4cf6a7e8'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Download a single attachment by exact attachment sys_id to c:\temp
SELECT * FROM download_attachments WITH(SysId=''4b2d41168396b21032ddb9f6feaad38f'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Download attachments uploaded in the last 2 days to c:\temp
SELECT * FROM download_attachments WITH(TableName=''problem'', Query=''sys_created_on>=<<today-2d,FUN_TO_DATETIME>>'', SaveFolder=''c:\temp'', OverwriteFile=''True'')

-- Download only image attachments larger than 1 MB to c:\temp
SELECT * FROM download_attachments WITH(TableName=''problem'', Query=''content_typeSTARTSWITHimage/^size_bytes>1048576'', SaveFolder=''c:\temp'', OverwriteFile=''False'')'
EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY]