Download an attachment by ID

This example demonstrates how to use the download_attachment endpoint to download a single attachment file by its exact sys_id. Unlike bulk download endpoints, this allows you to specify a custom TargetFilePath for precise control over the saved file name and location. It does not support filters, as it targets one specific attachment.

Specify the attachment's SysId, the full TargetFilePath (e.g., c:\temp\myfile.png), and FileOverwriteMode to control behavior if the file exists (0 or 'AlwaysOverwrite' to overwrite, 1 or 'FailIfExists' to fail, 2 or 'SkipIfExists' to skip). Replace the sample sys_id with a valid attachment ID from your instance.

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 a single attachment by sys_id to a custom file path (always overwrite if exists)
SELECT * FROM download_attachment 
WITH(
    SysId='4b2d41168396b21032ddb9f6feaad38f', 
    TargetFilePath='c:\temp\dump_saved.png', 
	
	--Set overwrite mode : 0=AlwaysOverwrite, 1=FailIfExists, 2=SkipIfExists
    FileOverwriteMode=0 
)

-- Download another attachment with a different name (fail if file exists)
SELECT * FROM download_attachment 
WITH(
    SysId='another_attachment_sys_id_here', 
    TargetFilePath='c:\downloads\report.pdf', 
    FileOverwriteMode='FailIfExists'
)

-- Download using numeric value for skip if exists
SELECT * FROM download_attachment 
WITH(
    SysId='third_attachment_sys_id_here', 
    TargetFilePath='c:\files\data.xlsx', 
    FileOverwriteMode=2
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_SERVICENOW_IN_GATEWAY], '-- Download a single attachment by sys_id to a custom file path (always overwrite if exists)
SELECT * FROM download_attachment 
WITH(
    SysId=''4b2d41168396b21032ddb9f6feaad38f'', 
    TargetFilePath=''c:\temp\dump_saved.png'', 
	
	--Set overwrite mode : 0=AlwaysOverwrite, 1=FailIfExists, 2=SkipIfExists
    FileOverwriteMode=0 
)

-- Download another attachment with a different name (fail if file exists)
SELECT * FROM download_attachment 
WITH(
    SysId=''another_attachment_sys_id_here'', 
    TargetFilePath=''c:\downloads\report.pdf'', 
    FileOverwriteMode=''FailIfExists''
)

-- Download using numeric value for skip if exists
SELECT * FROM download_attachment 
WITH(
    SysId=''third_attachment_sys_id_here'', 
    TargetFilePath=''c:\files\data.xlsx'', 
    FileOverwriteMode=2
)')

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 a single attachment by sys_id to a custom file path (always overwrite if exists)
SELECT * FROM download_attachment 
WITH(
    SysId=''4b2d41168396b21032ddb9f6feaad38f'', 
    TargetFilePath=''c:\temp\dump_saved.png'', 
	
	--Set overwrite mode : 0=AlwaysOverwrite, 1=FailIfExists, 2=SkipIfExists
    FileOverwriteMode=0 
)

-- Download another attachment with a different name (fail if file exists)
SELECT * FROM download_attachment 
WITH(
    SysId=''another_attachment_sys_id_here'', 
    TargetFilePath=''c:\downloads\report.pdf'', 
    FileOverwriteMode=''FailIfExists''
)

-- Download using numeric value for skip if exists
SELECT * FROM download_attachment 
WITH(
    SysId=''third_attachment_sys_id_here'', 
    TargetFilePath=''c:\files\data.xlsx'', 
    FileOverwriteMode=2
)'
EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY]