SQL Server Select

Overview

This Snap executes a SELECT query against a SQL Server database and outputs the result in a document format.

You can use this Snap to fetch data from an SQL Server database by providing the database and table details. Queries produced by the Snap have the following format:

SELECT [output fields] FROM [table] WHERE [where clause] ORDER BY [ordering] LIMIT [limit] OFFSET [offset]
Note:
  • JSON paths can be used in a query and will have values from an incoming document substituted into the query. However, documents missing values for a given JSON path will be written to the Snap's error view. After a query is executed, the query's results are merged into the incoming document overwriting any existing keys' values. The original document is output if there are no results from the query.
  • This Snap supports SQL Server 2008 or higher version.
  • The sub-millisecond time accuracy for all date/time data types is not supported.


Supported Accounts

  • This is a Read-type Snap.

Snap views

Type Description Examples of upstream and downstream Snaps
Input Optional. This Snap has at most one document input view. If an input view is defined, then values from the incoming document can be used as substitution variables in the SQL query.
Output This Snap has exactly one document output view.
Learn more about Error handling.

Snap settings

Note: Learn about the common controls in the Snap settings dialog.
Field/Field set Description

Label*

String 		Specify a unique name for the Snap. Modify this to be more appropriate, especially if there are more than one of the same Snap in the pipeline.

Default value: SQL Server - Select

Example: SQL Server - Select
Schema Name

String/Expression/ Suggestion

Specify the database schema name. In case it is not defined, then the suggestion for the Table Name will retrieve all tables names of all schemas. The property is suggestible and will retrieve available database schemas during suggest values.

Note: The values can be passed using the pipeline parameters but not the upstream parameter.

Default value: N/A

Example: dbo
Table Name*

String/Expression/ Suggestion

Specify the name of the table from which to fetch data. The table name is suggestible and requires an account setting.

Note: The values can be passed using the pipeline parameters but not the upstream parameter.

Default value: N/A

Example: Customers
Where Clause

String/Expression/ Suggestion		 Specify the WHERE clause of the SELECT statement. This supports document value substitution (such as $person.firstname is substituted with the value found in the incoming document at the path). However, you cannot use a value substitution after "IS" or "is" word.

Default value: N/A

Example:

Without using expressions:

Using expressions:
  • "EMPNO=$EMPNO and ENAME=$EMPNAME"
  • email = $email
  • emp=$emp
  • "emp='" + $emp + "'"
  • "EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"
Order By

Use this field set to specify the columns in the order in which you want to sort the database. The default database sort order will be used.
Column Names

String/Expression		 Specify the column names.

Default value: N/A

Example: email, address, first, last
Limit Offset

Integer/Expression		 Specify the offset for the limit clause. This is where the result set should start. Starting row for the query.
Note: Some databases do not support OFFSET, such as Teradata, and the Limit offset field is ignored.

Default value: N/A

Example: 0
Limit Rows

Integer/Expression		 Specify the number of rows to return from the query.

Default value: N/A

Example: 10
Query Hints

Dropdown list
Specify the hints to customize and optimize the database engine in processing the query statement.
Note: Query hints that are supported are dependent on the SQL database version. For instance, SELECT * from sys.dm_exec_valid_use_hints query helps you list out the hints that are supported by a specific database.

Default value: N/A

Example: OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'))
Output Fields

Use this field set to specify the output fields for SQL SELECT statement.
Output Fields

String/Expression

Specify or select output field names for SQL SELECT statement to include in the output document.

Default value: N/A

Example: CustomerID, CustomerName, City
Fetch Output Fields In Schema

Checkbox

Select this checkbox to fetch the output fields in the schema.

Default status: Selected
Pass through

Checkbox

Select this checkbox to pass the input document through to the output view under the key 'original'.

Default status: Deselected
Ignore empty result

Checkbox		 Select this checkbox if you do not want to write to the output view when a SELECT operation does not produce any result or is empty. If you do not select this property and select the Pass through checkbox, the input document is passed through to the output view.

Default status: Deselected
Auto commit

Dropdown list

Select this checkbox to commit each SQL statement as a separate transaction. If not selected, all statements will be committed together.

Default value: False

Example: True
Match data types

Checkbox

This property applies only when the Output fields property is provided with any field value(s).

If this property is selected, the Snap tries to match the output data types same as when the Output fields property is empty (SELECT * FROM ...). The output preview would be in the same format as the one when SELECT * FROM is implied and all the contents of the table are displayed.

Default status: Deselected
Number of retries

Integer/Expression

Specify the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response.

Default value: 0

Example: 3
Retry interval (seconds)

Integer/Expression

Specify the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception.

Default value: 1

Example: 10
Staging mode

Dropdown list

Required when the value in the Number of retries field is greater than 0.

Specify the location from the following options to store input documents between retries:

  • In memory: The query results are stored in the Snaplex memory. If the query is too large to store in the memory, it might cause the Snap to fail, so choose the On disk option.
  • On disk: The query results are stored on the disk in a temporary (tmp) directory that is managed by the SnapLogic platform. This directory is deleted automatically when the Snap terminates.

Option for Unique Identifier Column

Dropdown list

This property applies only when the Snap returns a unique identifier (GUID) in the output.

SQL Server stores the unique identifier in an internal format. Select an option to control the case of hexadecimal characters when it is converted to a string in the output:

  • Convert to lower case: Modifies the output string to lower case.
  • Convert to upper case: Modifies the output string to upper case.

Default Value: Convert to lower case

Example: Convert to upper case
Convert row version datatype to integer

Checkbox		 Select this checkbox to enable the Snap to display row versin column vaues as numbers instead of binary arrays.

Default status: Deselected

Snap execution

Dropdown list
Choose one of the three modes in which the Snap executes. Available options are:
  • Validate & Execute: Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime.
  • Execute only: Performs full execution of the Snap during pipeline execution without generating preview data.
  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Default value: Validate & Execute