SQL Server guide

Read attachments by parent record

This example set demonstrates how to use the get_attachments_by_parent_search endpoint. This endpoint first finds parent rows (for example, records in the problem table) using a parent-level Query, then for each parent record fetches attachments using the AttachmentQuery filter. Because it performs a per-parent lookup it can be slower than direct attachment-table queries, 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>>). Replace sample sys_id and table names with values 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. 

-- Find attachments for specific parent records (multiple parent IDs)
SELECT * FROM get_attachments_by_parent_search
WITH(TableName='problem', Query='sys_idIN62304320731823002728660c4cf6a7e8,6632130c730123002728660c4cf6a734')

-- Parent-level filter: problems updated in last 2 days; attachment-level filter: file name ends with .png or .zip
SELECT * FROM get_attachments_by_parent_search
WITH(
  TableName='problem',
  Query='sys_updated_on>=<<today-2d,FUN_TO_DATETIME>>',
  AttachmentQuery='file_nameENDSWITH.png^ORfile_nameENDSWITH.zip'
)

-- Parent-level filter by sys_id and attachment filter by exact file name
SELECT * FROM get_attachments_by_parent_search
WITH(TableName='problem', Query='sys_id=62304320731823002728660c4cf6a7e8', AttachmentQuery='file_name=dump.png')

-- Attachments uploaded by a specific user for parents matched by parent query
SELECT * FROM get_attachments_by_parent_search
WITH(TableName='incident', Query='opened_by=jsmith', AttachmentQuery='sys_created_by=jsmith')

-- Attachments larger than 1 MB for problems updated in the last month
SELECT * FROM get_attachments_by_parent_search
WITH(
  TableName='problem',
  Query='sys_updated_on>=<<today-1m,FUN_TO_DATETIME>>',
  AttachmentQuery='size_bytes>1048576'
)

-- Only image attachments (content type starts with "image/") for a given parent
SELECT * FROM get_attachments_by_parent_search
WITH(TableName='problem', Query='sys_id=62304320731823002728660c4cf6a7e8', AttachmentQuery='content_typeSTARTSWITHimage/')

-- Order attachments by creation date (per-parent results ordered by attachment date)
SELECT * FROM get_attachments_by_parent_search
WITH(TableName='problem', Query='sys_updated_on>=<<today-2d,FUN_TO_DATETIME>>', AttachmentQuery='ORDERBYsys_created_on')

-- Combine parent IN-list and attachment name contains filter
SELECT * FROM get_attachments_by_parent_search
WITH(
  TableName='problem',
  Query='sys_idIN62304320731823002728660c4cf6a7e8,6632130c730123002728660c4cf6a734',
  AttachmentQuery='file_nameLIKEerror'
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_SERVICENOW_IN_GATEWAY], '-- Find attachments for specific parent records (multiple parent IDs)
SELECT * FROM get_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_idIN62304320731823002728660c4cf6a7e8,6632130c730123002728660c4cf6a734'')

-- Parent-level filter: problems updated in last 2 days; attachment-level filter: file name ends with .png or .zip
SELECT * FROM get_attachments_by_parent_search
WITH(
  TableName=''problem'',
  Query=''sys_updated_on>=<<today-2d,FUN_TO_DATETIME>>'',
  AttachmentQuery=''file_nameENDSWITH.png^ORfile_nameENDSWITH.zip''
)

-- Parent-level filter by sys_id and attachment filter by exact file name
SELECT * FROM get_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_id=62304320731823002728660c4cf6a7e8'', AttachmentQuery=''file_name=dump.png'')

-- Attachments uploaded by a specific user for parents matched by parent query
SELECT * FROM get_attachments_by_parent_search
WITH(TableName=''incident'', Query=''opened_by=jsmith'', AttachmentQuery=''sys_created_by=jsmith'')

-- Attachments larger than 1 MB for problems updated in the last month
SELECT * FROM get_attachments_by_parent_search
WITH(
  TableName=''problem'',
  Query=''sys_updated_on>=<<today-1m,FUN_TO_DATETIME>>'',
  AttachmentQuery=''size_bytes>1048576''
)

-- Only image attachments (content type starts with "image/") for a given parent
SELECT * FROM get_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_id=62304320731823002728660c4cf6a7e8'', AttachmentQuery=''content_typeSTARTSWITHimage/'')

-- Order attachments by creation date (per-parent results ordered by attachment date)
SELECT * FROM get_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_updated_on>=<<today-2d,FUN_TO_DATETIME>>'', AttachmentQuery=''ORDERBYsys_created_on'')

-- Combine parent IN-list and attachment name contains filter
SELECT * FROM get_attachments_by_parent_search
WITH(
  TableName=''problem'',
  Query=''sys_idIN62304320731823002728660c4cf6a7e8,6632130c730123002728660c4cf6a734'',
  AttachmentQuery=''file_nameLIKEerror''
)')

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) = '-- Find attachments for specific parent records (multiple parent IDs)
SELECT * FROM get_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_idIN62304320731823002728660c4cf6a7e8,6632130c730123002728660c4cf6a734'')

-- Parent-level filter: problems updated in last 2 days; attachment-level filter: file name ends with .png or .zip
SELECT * FROM get_attachments_by_parent_search
WITH(
  TableName=''problem'',
  Query=''sys_updated_on>=<<today-2d,FUN_TO_DATETIME>>'',
  AttachmentQuery=''file_nameENDSWITH.png^ORfile_nameENDSWITH.zip''
)

-- Parent-level filter by sys_id and attachment filter by exact file name
SELECT * FROM get_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_id=62304320731823002728660c4cf6a7e8'', AttachmentQuery=''file_name=dump.png'')

-- Attachments uploaded by a specific user for parents matched by parent query
SELECT * FROM get_attachments_by_parent_search
WITH(TableName=''incident'', Query=''opened_by=jsmith'', AttachmentQuery=''sys_created_by=jsmith'')

-- Attachments larger than 1 MB for problems updated in the last month
SELECT * FROM get_attachments_by_parent_search
WITH(
  TableName=''problem'',
  Query=''sys_updated_on>=<<today-1m,FUN_TO_DATETIME>>'',
  AttachmentQuery=''size_bytes>1048576''
)

-- Only image attachments (content type starts with "image/") for a given parent
SELECT * FROM get_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_id=62304320731823002728660c4cf6a7e8'', AttachmentQuery=''content_typeSTARTSWITHimage/'')

-- Order attachments by creation date (per-parent results ordered by attachment date)
SELECT * FROM get_attachments_by_parent_search
WITH(TableName=''problem'', Query=''sys_updated_on>=<<today-2d,FUN_TO_DATETIME>>'', AttachmentQuery=''ORDERBYsys_created_on'')

-- Combine parent IN-list and attachment name contains filter
SELECT * FROM get_attachments_by_parent_search
WITH(
  TableName=''problem'',
  Query=''sys_idIN62304320731823002728660c4cf6a7e8,6632130c730123002728660c4cf6a734'',
  AttachmentQuery=''file_nameLIKEerror''
)'
EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY]