Connecting to SQL Server

The following properties can be specified when loading data from a SQL Server connection.

Not all properties listed here may be necessary when creating the data connection. However, at a minimum, basic information must be provided.

Basic Information

• Title - The name of the connection.

• Description - The description (optional) of the connection.

• Host - The IP address or host name of the storage system.

• Port - The network port number through which the storage system connects.

• Database - The name of the database containing the data to be added.

• Username - The user account used to authenticate access to the storage system.

• Password - The password corresponding to the user account.

SQL Server Options

• Wsid - Workstation ID or client host name override. Will be stored in master.dbo.sysprocesses, column hostname.

• Lobbuffer - The amount of LOB data to buffer in memory before caching to disk.

• Prepare SQL - Use stored procedures for prepared statements.

• Charset - Server character set for non-Unicode character values.

• TDS - The database server protocol.

• Instance - The database server instance.

• Server Type - The type of database (1 is SQL Server, 2 is Sybase).

• Bufferminpackets - The minimum number of packets per statement to buffer to memory.

• Usejcifs - Force use of jCIFS library on Windows for connecting via named pipes.

• Usentlmv 2 - Set to true to send LMv2/NTLMv2 responses when using Windows authentication

• Language - The language for server messages.

• Sockettimeout - The TCP/IP socket timeout value in seconds or 0 for no timeout.

• Batchsize - The number of statements to submit at once. Batches are broken up in pieces this large.

• SSL - Set the SSL level.

• Buffermaxmemory - The global buffer memory limit for all connections (in kilobytes).

• Socketkeepalive - Use TCP/IP socket keep alive feature.

• Autocommit - Set the autocommit mode of newly created connections.

• Appname - The application name advertised by the driver.

• Packetsize - The network packet size (a multiple of 512).

• Macaddress - Hex-encoded client MAC address.

• Cachemetadata - Set to true to enable the caching of column meta data for prepared statements.

• Uselobs - Map large types (IMAGE and TEXT/NTEXT) to LOBs vs. String/byte[].

• Logfile - Set the name of a file for the capture of logging information.

• Usecursors - Use SQL Server fast forward only result sets for forward only result sets.

• Bufferdir - The directory to buffer data to.

• TCP Nodelay - Enable/disable TCP_NODELAY

• Lastupdatecount - Return only the last update count on executeUpdate.

• Logintimeout - The time to wait for a successful login before timing out.

• Usekerberos - Set to true to use Kerberos as the authentication mechanism.|N|true,false

• Maxstatements - The maximum number of statements to keep open. This is only a target for the cache.

• Xaemulation - Set to false to use the Microsoft Distributed Transaction Coordinator.

• Processid - The process ID reported by the driver.

• Domain - The domain used for authentication.

• Progname - The program name advertised by the driver.

• Bindaddress - The IP address of the local interface that jTDS should bind to when connecting to a database via TCP/IP.

• Namedpipe - Use named pipes to connect instead of TCP/IP sockets.

• Sendstringparametersasunicode - If strings should be sent as unicode values.

Additional Options

Connection Properties

• Property Name - The name of a connection property.
• Property Value - The value of the connection property.

Encrypted Properties

• Encrypted Property Name - The name of an encrypted property.
• Encrypted Property Value - The value of the encrypted connection property.

Advanced

Concurrency Control

• Maximum Allowed Concurrency - The overall limit on concurrent executions used to process the source.
• Number of Nodes - The number of nodes to use when processing the source.
• Executors per Node - The number of executors to use per node when processing the source.
• Maximum Request Rate - The maximum aggregate request rate against the source (e.g., 100 / s, 480 / minute).

Localization Settings

• Locale - The locale to use when parsing dates and numbers (e.g., ar-SA, en-US, ja-JP).
• Character Encoding - The default character encoding used by the connection (e.g., EBCDIC, UTF-8).
• Timezone - The timezone to use for dates and times that don't contain one (e.g., GMT+2, PST).

Data Formats

• Recognize only the configured formats - Specifies whether only those formats configured here should be recognized.
• DateTime Formats - How date and time values should be encoded (e.g., yyyy-MM-dd'T'HH:mm:ss, MM/dd/yyyy HH:mm, dd-MMM-yyyy HH:mm:ss).
• Date Formats - How calendar dates without time information should be encoded (e.g., yyyy-MM-dd, MM/dd/yyyy, dd-MMM-yyyy).
• Time Formats - How time-of-day values should be encoded (e.g., HH:mm:ss, hh:mm a, HH:mm:ss.SSS).
• Boolean Formats - How true/false or binary logical values should be encoded (e.g., true/false, 1/0, Y/N).
• Double Formats - How double-precision floating values should be encoded (e.g., ##,##0.00, $, %).
• Float Formats - How single-precision floating-point values should be encoded (e.g., ##,##0.00, %, e).
• Integer Formats - How whole numbers should be encoded (e.g., #, #,###,##0).
• Long Formats - How long integer values exceeding the range of standard integers should be encoded (e.g., #, #,###,##0).
• Month Formats - How standalone month values should be encoded (e.g., MM, MMMM).
• Year Month Formats - How combined month and year values should be encoded (e.g., yyyy MM, yyyy-MMM).
• Month Day Formats - How combined month/day values should be encoded (e.g., MM-dd, MMM dd, MM/dd)
• Year Formats - How standalone year values are encoded (e.g., yyyy, YY).