SQL Server 2005
SQL Server 2005 is a database application that you can connect natively to through an OLE DB interface, or through ODBC. In this newest version of the SQL Server line, Microsoft has enhanced the database platform for large-scale online transactional processing (OLTP), data warehousing, XML integration of relational data and e-commerce applications. With this connector, the integration platform reads and writes to SQL Server 2005 tables.
Connectivity Pointers
• 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
Identity Columns with SQL Passthrough Query
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 non-identity 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 nonidentity fields in the Target, you change the data type in the Target to fix the fields.
Property Options
You can set 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.
This 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 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.
Note: When your target is SQL Server 2005, you can set the maximum length of variable length data types (such as varchar, nvarchar, varbinary) beyond the maximum 8000 KB. The maximum can be up to 231–1 (2 GB – 1). If the length supplied is more than 8000, the engine automatically sets the MAX argument.