SQL Server 7 (OLEDB)
SQL Server 7 is a database application that you can connect to natively through an OLE DB interface or through ODBC. With this OLEDB connector, the integration platform reads and writes to SQL Server 7 tables.
Connectivity Pointers
• The SQL Server engine must be running in the background to connect to SQL Server 7.
• Under Server Name, enter the name of your server. If SQL Server is installed locally, leave this line blank.
• If your database has many tables, selecting the table from a list may take more time than typing the UserID.Tablename value in the Source Table/View box. If you enter the table name without the user ID and then connect to the database, you can reselect the table in the source data browser to view it.
• If possible, create query statements using the source database application, so that syntax and validity can be checked by the native application. You can then copy the query statement and paste it in the Query Statement Box.
Connector-Specific Notes
• When a SQL passthrough query is used in place of a table name, the integration platform does not correctly identify identity (for example int identity) fields, but can use the nonidentity version of the data type (for example a field of type int identity is considered an int field). The problem identifying identity is due to limitations in the SQL Server OLEDB provider. OLEDB has no way to directly identify whether a column is an identity type and the SQL Server OLEDB provider does not return the base column/table/owner information needed to determine this from the system catalog.
• You can use a SQL passthrough query as a source and the integration platform reads the data correctly. If this fix causes identity fields to be mapped to non-identity fields in the Target, you change the data type in the Target to fix the fields.
• No Millisecond Support - 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.
• Cursor Considerations - This limitation is due to OLE DB bugs in SQL Server 7 and SQL Server 2000. After connecting to views and changing cursor type from the default (static) to another cursor type, the integration platform may stop functioning. If this occurs, use the Task Manager to close the integration platform, then reopen the integration platform and the transformation, and choose SQL Server v. 6.5 as your source connector.
• Client Cursors and Sessions - According to Microsoft, there is a limitation to keep in mind when using client cursors:
• "…support only one active statement at a time on a connection. After a statement has been executed, no other statements can be executed on the connection until all of the results have been retrieved by the consumer, or the statement has been cancelled."
• This limitation could cause problems if you are using SQL Server sessions in process designs. Sessions allow you to execute multiple SQL statements under the umbrella of a single connection/transaction.
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.
For a list of all parts for source connectors, see
Specifying Source Connector, Parts, and Properties.
For a list of all parts for target connectors, see
Specifying Target Connector, Parts, and Properties.
Property Options
You can specify the following source (S) and target (T) properties:
CursorType
• client – This cursor is the default because it supports all Transact-SQL statements. The other cursor options (Static, Dynamic, Keyset) do not. Client cursors should be used only to alleviate the restriction that server cursors do not support all Transact-SQL statements or batches. One obvious benefit of the client-side cursor is quick response. After the result set has been downloaded to the client computer, browsing through the rows is fast. Your application is generally more scalable with client-side cursors because the cursor's resource requirements are placed on each separate client and not on the server.
• fast forward – A cursor that cannot be scrolled; rows are read in sequence from the first row to the last row only. In SQL Server, this cursor is called forward-only, and is the default. Note that the default for this Connector is client.
The fast forward cursor can only move forward through the result set. To return to a previous row, the application must close and reopen the cursor and then read rows from the beginning of the result set until it reaches the required row. Forward-only cursors provide a fast mechanism for making a single pass through a result set. This cursor is less useful for screen-based applications, in which the user scrolls backward and forward through the data. Such applications can use a forward-only cursor by reading the result set once, caching the data locally and performing scrolling themselves. This works well only with small amounts of data.
• static – A cursor that shows the result set exactly as it was at the time the cursor was opened. Sometimes called snapshot cursors.
Static cursors never detect other updates, deletes and inserts made to underlying data while the cursor is open. For example, suppose a static cursor fetches a row and another application then updates that row. If the application re-fetches the row from the static cursor, the values it sees are unchanged, despite the changes made by the other application. All types of scrolling are supported, but providers may or may not support bookmarks.
• Keyset – A cursor that shows the effects of updates made to its member rows by other users while the cursor is open, but does not show the effects of inserts or deletes.
Keyset-Driven cursors take note of a key you may have in your database. Under normal circumstances, when you request records from a source, you get the entire package. However, when a Keyset cursor is selected, only the keys are returned, giving a performance boost. Then, when you scroll through the recordset, the data for the keys is retrieved. However, when you first invoke the cursor, both keys and data are returned so you can see something in your bound control. When you scroll and the data is not in the current batch of data, the Keyset fetches the next block. This way, it only has to manage small keys rather than large chunks of data. Keyset cursors are similar to dynamic cursors, except you cannot see records others add. If another user deletes a record, it is inaccessible from your recordset.
• Dynamic – A cursor that can reflect data modifications made to the underlying data while the cursor is open. Updates, deletes and inserts made by users are reflected in the dynamic cursor.
The dynamic cursor can detect any changes made to the rows, order and values in the result set after the cursor is opened. Updates made outside the cursor are not visible until they are committed (unless the cursor transaction isolation level is set to "uncommitted").
Choosing a CursorType
Every cursor uses temporary resources to hold its data. These resources can be memory, a disk paging file, temporary disk files, or even temporary storage in the database. The cursor is called a client-side cursor when these resources are located on the client computer and a server-side cursor when these resources are on the server.
Your cursor choice depends upon whether you need to change, or simply view the data. The following are recommendations from Microsoft:
• If you just need to scroll through a set of results, but not change data, use a fast forward (called forward-only in SQL Server) or use the static cursor, OR
• If you have a large result set and need to select a few rows, use a keyset cursor, OR
• If you want to synchronize a result set with recent adds, changes and deletes by all concurrent users, use a dynamic cursor.
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
ID field types are not supported.