User Guide > Map Connectors > Source and Target Map Connectors > SQL Server 2008 Multimode
Was this helpful?
SQL Server 2008 Multimode
SQL Server 2008 is a database application that you can connect to directly or through ODBC 3.x. The integration platform supports concurrent writes to multiple target tables within a SQL RDBMS, such as with this SQL Server 2008 Multimode connector.
Multimode connectors allow you to perform multiple operations (Change Source or Target, table drops, table inserts, etc.) directly on your target database within the same transformation.
You can quickly setup a multimode connector using the multimode wizard. For more information, see Setting Up Multimode Target Connector Using Multimode Wizard.
Tip...  Under Server Name, enter the name of your server. If SQL Server is installed locally, leave this line blank.
Connector-Specific Notes
• Target Schema modification: In multimode targets, modifications to column names, data types, and sizes are not permitted.
• Transaction Control: SQL Server 2008 does not allow mixing of API-level transaction control and Transact-SQL control. If you want explicit control of transactions, use Target Only (Unbound) mode, which does not use bind variables. The Target Only mode uses bind variables for speed (the side effect is less control of transactions).
• Transaction Support: When a transformation starts, it is in AutoCommit mode (for example, every statement is considered a separate unit of work and is automatically committed). To operate in Explicit mode, do a BEGIN TRANSACTION statement. For Implicit mode, use a SET IMPLICIT_TRANSACTIONS ON Statement.
Connector Parts
Connector parts are the fields you configure to connect with a data source or target. The fields that are available depend on the connector you select. Multimode connectors are target only connectors. For a list of all parts for target connectors, see Specifying Target Connector, Parts, and Properties.
Property Options
You can specify the following target properties:
Property
Description
Encoding
The connector uses encoding to translate user names and query statements to Unicode before passing them on to SQL Server. The default is OEM.
SQL Log
The default is sql.log in the default installation directory. To use a different log, browse to the file, or enter the path and file name.
Note:  SQL statements are sent to the SQL Log file only if the SQL Output property is set to either Target and SQL Log or SQL Log Only.
SQL Output
Allows you to select bound or unbound mode and whether or not to write SQL statements to a SQL log. Keep in mind that bound mode is faster as bind variables are used. Select from the following options:
• Target Only (default) - Uses bound mode, which uses bind variables. SQL statements are sent to the target and not to the SQL log specified in the SQL Log property.
• Target Only (Unbound Mode) - Uses unbound mode, which does not use bind variables and sends the literal SQL statement to the database engine. SQL statements are sent to the target and not to the SQL log specified in the SQL Log property.
• Target and SQL Log - Sends SQL statements to the target and to the SQL log specified in the SQL Log property.
• SQL Log Only - Sends SQL statements only to the SQL log file specified in the SQL Log property.
Synonyms
If set to true, allows you to see synonyms. The alias names appear in the table list along with the tables. Default is false.
SystemTables
If set to true, allows you to see all tables created by the DBA in the database. The system table names appear in the table list. Default is false.
Note:  This property is applicable only if the user is logged onto the database as the database administrator. Only the DBA has access to system tables.
TransactionIsolation
Allows you to specify isolation level when reading from or writing to a database table with ODBC. The default is Serializable.
The ANSI SQL 2 standard defines three ways in which serializability of a transaction may be violated: P1 (Dirty Read), P2 (Nonrepeatable Read), and P3 (Phantoms).
The isolation levels are as follows:
• READ_UNCOMMITTED - Permits P1, P2, and P3.
• READ_COMMITTED - Permits P2 and P3. Does not permit P1.
• REPEATABLE_READ - Permits P3. Does not permit P1 and P2.
• SERIALIZABLE - Does not permit any of P1, P2, and P3.
• NONE- Does not start a transaction.
For further details about TransactionIsolation levels, see the Microsoft ODBC SDK documentation.
UpdateNullFields
Best Practice — Null values are sent to the database when inserting or updating records. The default is true. If you select False, null values are not sent to the database when inserting or updating record and the connector is forced to operate in unbound mode, which may cause slower performance.If fields in the target record are not mapped, then the null values are passed to the target. If you do not want to write to these fields, then it is recommended to set the value for UpdateNullFields to False.
Views
If set to True, this property allows you to see the view names in the table list along with the table names. Default is True.
Note:  This property supports only Append and DeleteAndAppend output modes and does not support the Replace output mode.
MultiSubnetFailover
Configuration where each failover cluster node is connected to a different subnet or different set of subnets.
Available options:
• Yes - Use this when connecting to the availability group listener of a SQL Server availability group.
• No - (Default) Use this when not connecting to the availability group listener of a SQL Server availability group.
Note:  When using an OLE DB Driver for SQL Server application that uses database mirroring to a multi-subnet scenario, you should set MultiSubnetFailover to Yes.
ApplicationIntent
Application workload type when connecting to a server. This property is only applied when the MultiSubnetFailover = Yes.
Available options:
• ReadWrite: (Default) Use this, when it is required to connect to a primary replica of the database, during a failover.
• ReadOnly: Use this, when it is required to connect to a read-only replica of the database, during a failover. Read-only routing might take longer than connecting to the primary. This is because read-only routing first connects to the primary, and then looks for the best available readable secondary.
Encoding Notes
Shift-JIS encoding is meaningful only in Japanese operating systems.
UCS-2 is no longer considered a valid encoding name, but you may use UCS2. In the XML file, change UCS-2 to UCS2.
The Encoding property is not the encoding of the database that you connect to, but rather the encoding in which the connector expects to receive SQL query statements to be sent to the database.
Supported Data Types
ID field types are not supported.
Note:  When your target is SQL Server 2008, you can set the maximum length of variable length data types (such as varchar, nvarchar, varbinary) beyond the maximum 8000 KB. The maximum can be up to 231–1 (2 GB – 1). If the length supplied is more than 8000, the engine automatically sets the MAX argument.
Last modified date: 07/26/2024