Connector for OLE DB Operational Considerations

Below are some important considerations when using the Connector for OLE DB with the Microsoft™ SQL server:

• A SQL server table that is the destination for a Replication activity must have a unique index. Submit the following command through ISQL or another SQL front end to create the index before attempting the Replication:

  create unique index <indexname> on <tablename> (<col1>, <col2>,..., <colN>)

  Note: You can use just one column or many columns, but for efficient replication, the replication keys should be included in the index. To remove an index, use the command:

  drop index <tablename>.<indexname> 

• When doing timestamp replication using the IBM® Enterprise Integrator (LEI) Replication activity, a Microsoft™ SQL server timestamp type column (an internal type) cannot be used as the timestamp; it must be a DATETIME or SMALLDATETIME type.
• Binary is a fixed-length data type. Use this type when you expect the data entries in a column to be exactly the same size. SQL server may append zero bits to entries where the binary data is less than the column length. When this occurs, the data can no longer be read by Notes®.

  For example, if you have an Notes® rich text field mapped to a SQL server binary type in an Advanced RealTime Virtual Fields activity, any data entered into Notes® will be saved in SQL server. When you attempt to retrieve the data from SQL server through Notes®, through the Virtual Fields activity, the data will be corrupted. In this case, use the image type instead of binary. Image is capable of handling data that is greater than 8KB without truncation errors.

• When creating either a DECS activity or an LEI Replication activity with the Connector for OLE DB, the key field should not be a TEXT, NTEXT or IMAGE datatype. These data types cannot be used in an ORDER BY clause or in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.
  Note: When working with stored procedures, explicitly map all input or output parameter field names. Also, the source field names and the stored procedure parameter names must match and be in the same sequential order.
• When creating a connection to Microsoft™ Access 2000, you must specify the database name as part of the path entered in the data source field on the OLE DB connection document. Entering the database name in the Catalog field is not acceptable. However, when creating a connection to SQL server, you must specify the database name in the Catalog field, not as part of the path entered in the data source field.

  Example: Correct specification to example SQL server name SQLServerN1 and table name testDB for a SQL server connection:

  

• There is a limitation on how Connectors interpret a Notes® NUMBER field. When you use the "Create Target Metadata" option from a Notes® source, heavy precision loss may occur with Notes® NUMBER fields.