User Guide : Map Connectors : Source and Target Map Connectors : SQL Server 7 Multimode (OLEDB)
 
Share this page                  
SQL Server 7 Multimode (OLEDB)
SQL Server 7 is a database application that you can connect directly to, or through an OLE DB interface or ODBC. With this connector, the integration platform can write SQL Server 7 data tables and it is not available as a source. The integration platform integration platformsupports concurrent writes to multiple Target Tables within a SQL RDBMS, such as with this SQL Server 7 multimode connector.
Multimode connectors allow you to perform multiple operations (change Source or Target, table drops, table inserts, etc.) directly on your target database.
Tip:  To connect to SQL Server 7 Multimode (OLEDB), you must enter a supported User ID and Password. The connection is limited to the access rights of the User ID that you use. For most purposes, you should use a User ID with an Administrator profile. We also suggest that you use a separate User ID that is not used for normal logins.
Connector-Specific Notes
Target Schema modification: In multimode targets, modifications to column names, data types, and sizes are not permitted.
There is a SQL Server 7 provider bug that causes difficulty with inserting identity fields with IdentityInsert enabled. To get around this limitation, use the Mass Insert Target type.
Note:  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.
Property Options
You can set the following source (S) and target (T) properties.
Property
S/T
Description
SQL Log
T
The default is sql.log in the default installation directory. To use a different log, either browse to the file or enter the path and file name.
SQL Output
T
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. Target Only is the default.
• 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, 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. If set to False, null values are not sent to the database when inserting or updating records and the connector is forced 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
If set to true (default), allows you to see views. The view names appear in the table list along with the tables.
TransactionIsolation
T
Allows you to specify an 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.
Encoding
T
Type of encoding to use with source and target files. Default is OEM. For details, see Encoding Notes.
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 data 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
The only data type available for SQL Server 7 (OLEDB) is Text.