User Guide : Map Connectors : Source and Target Map Connectors : SQL Server 2000 Multimode
 
Share this page                  
SQL Server 2000 Multimode
SQL Server 2000 is a database application that you can either connect to directly or through ODBC 3.x. This multimode connector is available as a target only. The integration platform supports concurrent writes to multiple target tables within a SQL RDBMS, such as with this SQL Server 2000 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.
Connector-Specific Notes
• Milliseconds: The integration platform does not support milliseconds in DateValMask conversions. Although a mask is provided for them, the integration platform returns zeros in those places.
• Target Schema modification: In multimode targets, modifications to column names, data types, and sizes are not permitted.
• Transaction Control: SQL Server 2000 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.
Property Options
You can set the following source (S) and target (T) properties.
Property
S/T
Description
Encoding
T
Type of encoding to use with source and target files. The default is OEM.
SQL Log
T
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.
SQL Output
T
Allows you to select either 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:
• 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
T
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
T
If set to true, this property 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.
UpdateNullFields
T
Null values are sent to the database when inserting or updating records. The default is true. When set to False, null values are not sent to the database when you insert or update records. When set to false, this property forces the connector to operate in unbound mode, which may cause slower performance.
Best Practice — 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
T
When set to true (the default), this property allows you to see views. To disallow Views, change the setting to false. The view names appear in the table list along with the tables.
transactionalIsolation
T
The Translation Isolation option allows you to specify any one of five different isolation levels when reading from or writing to a database table with ODBC. The default for version 3.x, 3.5 and IBM DB2 Universal Database is Serializable.
The ANSI SQL 2 standard defines three specific ways in which serializability of a transaction may be violated: P1 (Dirty Read), P2 (Nonrepeatable Read) and P3 (Phantoms).
The five 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.
• VERSIONING – Provides SERIALIZABLE transactions, but does so without a significant impact on concurrency.
For further details about TransactionIsolation levels, see Microsoft ODBC SDK documentation.
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.
Data Types
ID field types are not supported.