User Guide > Map Connectors > Source and Target Map Connectors > SQL Server 2008
Was this helpful?
SQL Server 2008
SQL Server 2008 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 2008 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.
• You can use an EZscript to auto-generate a GUID for a variable declared as the uniqueidentifier data type. See Generating a Random Unique Identifier.
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.
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:
Property
S/T
Description
CommitFrequency
T
Allows you to control how often data is committed to the database when the AutoCommit property is set to False.
The default value is zero that is, the data is committed at the end of the transformation, allowing rollback on error. This is the slowest setting. When performing large transformations, this is not practical as it may produce too many transaction log entries.
Specifying a nonzero value indicates that data is committed to the database after inserting or updating specified number of records.
ConstraintDDL
T
Pass-through mechanism that allows you to specify any additional data definition language (DDL) statements that need to be executed when a new table is created. DDL is used to define keys, indexes, constraints and integrity rules for SQL databases. There is no default.
CursorType
S
Type of cursor to use for retrieving items from the source table. Available options are client (default), fast forward, static, keyset, and dynamic. See CursorType for details.
Encoding
S/T
The SQL Server 2008 connector is Unicode-based. The encoding property specifies the encoding from which to translate user names and query statements to Unicode before passing them to SQL Server. Default is OEM.
Synonyms
S/T
If set to true, this property allows you to see synonyms. The alias names appear in the table list along with the tables. Default is false.
SystemTables
S/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.
TransactionIsolation
T
Allows you to specify one of five different isolation levels 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.
Views
S/T
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.
WhereStmt
S
Provides a pass-through mechanism for SQL connectors where advanced users can construct the Where clause of the SQL query themselves. It can be used as an alternative to writing a lengthy query statement. You may use this to instruct the SQL database server to filter the data based on a particular condition before sending it to the integration platform. There is no default value for this property.
Note:  This property is not applicable when the source connection is a query statement or file. This property enables data filtering when you select a table.
MultiSubnetFailover
S/T
Configuration where each failover cluster node is connected to a different subnet or different set of subnets.
Available options:
• Yes - Use this when connecting to the availability group listener of a SQL Server availability group.
• No - (Default) Use this when not connecting to the availability group listener of a SQL Server availability group.
Note:  When using an OLE DB Driver for SQL Server application that uses database mirroring to a multi-subnet scenario, you should set MultiSubnetFailover to Yes.
ApplicationIntent
S/T
Application workload type when connecting to a server. This property is only applied when the MultiSubnetFailover = Yes.
Available options:
• ReadWrite: (Default) Use this, when it is required to connect to a primary replica of the database, during a failover.
• ReadOnly: Use this, when it is required to connect to a read-only replica of the database, during a failover. Read-only routing might take longer than connecting to the primary. This is because read-only routing first connects to the primary, and then looks for the best available readable secondary.
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.
Supported Data Types
ID field types are not supported.
Note:  When your target is SQL Server 2008, 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.
Last modified date: 10/22/2024