User Guide > Map Connectors > Source and Target Map Connectors > SQL Server 7 Multimode (OLEDB)
Was this helpful?
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.
You can quickly setup a multimode connector using the multimode wizard. For more information, see Setting Up Multimode Target Connector Using Multimode Wizard.
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.
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
SQL Log
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.
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. 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
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.
UpdateNullFields
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
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.
TransactionIsolation
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
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.
Supported Data Types
The only data type available for SQL Server 7 (OLEDB) is Text.
Last modified date: 10/22/2024