•  Preface
  • Purpose
  • Audience
  • Organization
  • Using this manual
  • Typographical conventions
  • Examples of syntax descriptions
  • OpenEdge messages
•  Introduction
  • MS SQL Server DataServer overview
  •  DataServer components
    • DataServer for MS SQL Server logic
    • Schema holder
    •  Security
      • OpenEdge security
      • MS SQL Server database security
      • Additional security considerations
  • DataServer utilities
  • DataServer demonstration databases
  •  DataServer configurations
    • The local, self-service, DataServer configuration
    • The remote, client/server, DataServer configuration
    • Configuring distributed DataServer applications using ProBroker
    • Configuring DataServer applications with a self-service AppServer
    •  Configuring distributed DataServer applications using the Unified Broker Framework
      • A closer look at the OpenEdge Management or OpenEdge Explorer interface
      • Using the mergeprop utility
      • Typical configuration scenarios
  • Software requirements
  •  Guidelines for using the DataServer
    • Some functionality highlights
    • Using the DataServer for MS SQL Server for the first time
    • Migrating an OpenEdge database to an MS SQL Server data source
  • Documentation resources
•  Initial Programming Considerations
  •  Database design issues
    • OpenEdge database objects and MS SQL Server database objects
    • Naming conventions
    • MS SQL Server DataServer data source limits
    •  Code pages
      • Client code page
      • Code page, collation, and case conflicts
    •  Support for Unicode
      • Configuration requirements
      • Data type support
    •  Indexes and sorting
      • USE-INDEX and BY clause considerations
      • Dummy indexes for sort order
      • Unique indexes
      • Large key entry support
    • Case-sensitivity
    • Interaction of code page, collation, and case sensitivity
    • Initial value
    • MS SQL Server data source views
    • Triggers
    •  OpenEdge sequences
      • Native Sequence generator
      • Support for 64-bit sequences
  •  Data types
    • Working with unsupported data types
    • Working with non-updatable data types
    •  Processing considerations for 32-bit and 64-bit data types
      • Processing activities that require additional action
      • Determining your application needs
    •  DataServer for MS SQL Server support for datetime data types
      • Working with MS SQL Server and ABL datetime data types
      • Performing data type conversions
      • Enabling Datetime data types using the Data Administration tool
      • Update/Add Table definitions utility
      • Schema Migration > OpenEdge DB to MS SQL Server
      • Verify Table Definitions and Adjust Schema utilities
      • Generate delta.sql OpenEdge to MS SQL Server utility
      • Dump as Create Table Statement
      • Default and Special Datetime Default Values
      • Using Datetime Data Types with Stored Procedures
      • Datetime index components
    • Using datetime data types in a WHERE clause
    •  Support for OpenEdge ABL CLOB data type
      • ABL-to-MS SQL Server data type mapping
    •  Support for OpenEdge ABL BLOB data type
      • ABL-to-MS SQL Server data type mapping
    • User-defined data types
    • Arrays
    • Unknown value (?)
    •  Zero-length character strings
      • _BLANKDEFAULT
  • Record creation
  •  Data source record locking
    • Share locks
    • Exclusive locks
    • Handling lock timeouts
    • Additional record locking details
    • NO–LOCK
    • Locking impact on queries
    • Locking examples
    • The NO–WAIT option
  •  Transactions
    • Transaction scoping and buffer management
  •  Error handling
    • Overflow checking
  •  Cursors
    •  Forward and backward scrolling
      • Impact of MAX-ROWS
    •  Block cursors
      • Considering when to use block cursors and firehose block cursors
      • Additional details about block cursor behavior
      • Block cursors on versus off
      • Block versus lookahead cursors
  •  ABL issues
    • ROWID function
    • RECID function
    • DEFINE BROWSE statement
    • Field lists
    •  FIND statements
      • FIND NEXT statement
      • FIND PREV and FIND LAST statements
    •  Compiling OpenEdge procedures
      • r‑code
    •  Relaxing CRC Validation Rules for pre-compiled r-code
      • Adding a New Field
      • Changing the Precision of a Character Column
    • FOR statements
    • Features and language differences
  • RDBMS stored procedures
•  RDBMS Stored Procedure Details
  •  Overview
    • Defining native stored procedures to ABL
  • RDBMS stored procedure basics
  •  Run Stored-Procedure details
    • Complete syntax reference for Run Stored-Procedure
    •  Run Stored-Procedure statement
      • LOAD-RESULT-INTO phrase
      • PROC-STATUS phrase
      • PROC-HANDLE phrase
      • NO-ERROR option
      • PARAM phrase
    • Close Stored-Procedure statement
    • Run Stored-Proc statement execution without the LOAD-RESULT-INTO phrase
    • Run Stored-Proc statement execution with the LOAD-RESULT-INTO phrase
    •  Run Stored-Proc statement execution using the send-sql-statement option
      • Without the Load-Result-Into option
      • With the Load-Result-Into option
  •  Data output and retrieval options
    • Return codes
    • Values of output parameters defined when creating a procedure
    •  Results retrieved from a database
      • Using the OpenEdge-supplied proc-text-buffer for row results
      • Defining a special view on the MS SQL Server data source to use as a buffer for row results
    • Loading results into a temp-table
  •  Interfacing with RDBMS stored procedures
    • Retrieving return codes
    • Retrieving output parameter values
    •  Retrieving result sets using proc-text-buffer
      • Technique to use proc-text-buffer
      • Assessing result sets obtained from the proc-text-buffer technique
    •  Defining a view to use as a buffer
      • Technique to define a view to use as a buffer
      • Assessing result sets obtained by defining a view as buffer technique
    •  Loading result sets into temp-tables
      • Getting started
      • Employing additional enhancements
      • Creating a temp-table layout plan
      • Using a temp-table handle with an unprepared dynamic temp-table
      • Details about a dynamic temp-table in an unprepared state
      • Details about a dynamic temp-table in a prepared state
      • Additional temp-table examples
  •  Handling errors
    • Trapping errors when using Load-Result-Into
    • Error messages related to using the Load-Result-Into phrase
  •  ROWID Support
    • Understanding the ROWID implementation
    •  ROWID characteristics
      • ROWID—Standard ABL behavior
    • Using ROWID with RUN STORED-PROCEDURE and LOAD-RESULT-INTO
    • Additional ProDataSet support
•  Additional Features to Enhance DataServer Performance
  • Enhancements overview
  •  Connection pooling
    •  Main components
      • Considerations when using ODBC connection pooling and DataServer connection management
    • ODBC Connection Pooling
    •  DataServer connection management
      • Enabling connection management
      • Disabling connection management
      • Connections exclusively for stored procedures
      • Impact on commits in stored procedures
    •  Firehose, Firehose block, and Fast Forward-Only Cursors
      • Firehose cursors
      • Firehose block cursors
      • Firehose and Firehose block exclusions
      • Fast Forward-Only cursors
    •  Monitoring cursor and connection use
      • Monitoring connections with qt_debug
      • Cursor downgrades
      • Statement cache
  • OpenEdge query types
  • Query tuning
  • Caching records
  • Join by SQLDB
  • Writing queries for performance
  •  Modifying the run-time schema check
    • Skip schema verification
  • Replacing FIND FIRST for performance
•  Configuring the DataServer
  •  DataServer components
    • DataServer configuration prerequisites
  •  Configuring an ODBC driver and registering the data source
    • Configuring the ODBC driver
    • Registering your data source
  • Configuring a local DataServer
  •  Configuring a remote DataServer
    •  Configuring with OpenEdge Management or OpenEdge Explorer
      • Connecting to a MS SQL Server DataServer broker using the NameServer
    •  Configuring from the command line
      • The ubroker.properties file
      • Editing the ubroker.properties file
      • Validating ubroker.properties content
      • Configuring multiple brokers
      • Using the NameServer
  •  Creating a schema holder
    •  Before you begin
      • General schema holder creation tasks
    •  Authorization and Authentication
      • Database authentication during foreign database connect
      • Permissions for creating or updating a schema holder
      • Permissions for connecting a schema holder
      • Application‑specific permissions
    • Preparing to create the schema holder
    • Creating a new empty OpenEdge database
    • Creating a schema holder
  •  Maintaining a schema holder
    • Updating a deployed schema holder
  •  Typical configuration for a remote client to connect to a remote DataServer
    • Configuring an ODBC data source
    • Starting a broker
    • Starting the schema holder to accept remote client connections
    • Connecting remote clients to the MS SQL Server DataServer broker
•  Connecting the DataServer
  • Starting a local DataServer
  •  Starting a remote DataServer
    •  Starting and stopping a broker process from OpenEdge Management/OpenEdge Explorer and connecting a client
      • Performing from the Windows host
      • Performing on the client
    • Starting and stopping a broker process using MSSMAN
    • Starting and stopping a broker process from the command line
    • Starting the Windows client process
    • Starting the UNIX client process
  •  Connection guidelines
    • Connections and security in Windows
  •  Connecting a schema holder
    •  Connecting a schema holder at startup
      • Using a local DataServer configuration
      • Using a remote DataServer configuration
    • Unsupported connection parameters
    • Special connection issues
    • Binding with connection and startup parameters
    • Query tuning with connection and startup parameters
    •  Analyzing application execution with Enhanced Logger
      • LOG-MANAGER system handle attributes and methods
      • DSLOG-MANAGER system handle attributes and methods
      • Log entry types
      • Logging levels
      • Setting the DataServer logging levels and log entry types
      • Aggregate logging using performance and interface log entry types at run-time
      • Dynamic performance and interface logging does not affect the aggregate performance and interface logging at the session level.
      • Server Context Log File Naming
      • Creating a unique log file per connected client
      • Enabling ABL to SQL Correlation in Logging
    • Local schema caching
  •  Connection failures and OpenEdge responses
    • Connection troubleshooting
    • Managing connections to an MS SQL Server database
    •  Accessing the DataServer log
      • Naming the log file
      • Setting the log file location
      • Handling log file creation errors
      • Restrictions on dynamic changes to log file
      • Using the $UNIQUE_DSLOG environment variable
•  The DataServer Tutorial
  • Demonstration databases for DataServer for MS SQL Server
  • Preparing to create demonstration databases
  • DataServer utilities
  •  Prime to ROWID identification
    •  ROWID sub-section of MS SQL Server Conversion
      • Best index selection
    • Recommendation for choosing best clustered index
  • Creating a schema holder
  • Updating a schema holder
  • Verifying a schema holder
  • Changing connection information in a schema holder
  • Changing a code page in a schema holder
  • Deleting a schema holder
  •  Managing server attributes
    • Overview of foreign database constraint definitions
    • Constraint definition names
    • Defining constraint definitions in Data Administration
    • Defining a primary key constraint definition
    • Defining a unique key constraint definition
    • Defining a foreign key constraint definition
    • Defining a check constraint definition
    • Defining a default constraint definition
    • Modifying an existing constraint definition
    • Activating or deactivating a class of existing constraint definitions
    • Deleting an existing constraint definition
    • Generating constraint definitions from ROWID
  •  Migrating an OpenEdge database to MS SQL Server
    • Preparing a data source for the utility
    • Migrating defined constraint definitions to your foreign data source
    • Running the OpenEdge DB to MS SQL Server utility
  • Column width
  • Adjust schema utility
  •  Delta df to MS SQL Server Incremental Schema Migration utility
    • Adjusting field widths during migration
    • Updating the MS SQL Server database
  •  Modifying a schema holder
    • Modifying table-level information
    • Modifying field-level information
    •  Defining the ROWID
      • Dumping and Loading Schema Holder Information
  • Independent OpenEdge MS SQL Server Schema Pull
  •  Adding extended ABL support
    • Modifying tables to support arrays
    • Using triggers to enable PROGRESS_RECID support of the ROWID function
    • Using computed columns to enable PROGRESS_RECID support of the ROWID function
    • Migrating RECID-trigger mechanism to RECID computed column mechanism
•  Troubleshooting
  • Tuning your environment with the –Dsrv startup parameter
  •  ODBC options
    • DataServer options
    •  Using MS SQL Server and DataServer options
      • Transaction Management: the AUTOCOMMIT option
      • Query Result Order—the PRGRS_ALWAYS_INDEX option
      • Concurrent query execution—the PRGRS_CACHE_CONN option
      • Connection problems—the PRGRS_CONNECT option
      • Key‑buffer size—the PRGRS_IDBUF option
      • Locking error messages—the PRGRS_LOCK_ERRORS option
      • Large rows—the PRGRS_MINBUF option
      • SQL statements cache—the PRGRS_PREPCACHE option
      • Concurrent procedure results—the PRGRS_PROC_TRAN option
      • Cursor characteristics—the PRGRS_STABLE_CURS option
      • Wait time for asynchronous connections—the PRGRS_WAIT_DELAY option
      • Preserve cursors—the -Dsrv MSS_PRESERVE_CURS,1 option
      • RECID Buffer size—the ZPRGRS_RECID_BUF_SIZE,nnn Option
      • SVUB,x—the Unified Broker Server Option
      • Logging options
    •  Using the block cursor switches
      • Block cursors—the -Dsrv PRGRS_BLOCK_CURS,1 option
      • Block cursor allocation by table—the -Dsrv PRGRS_TABLE_BLOCKSIZE,nnn option
      • Total Block cursor allocation—the -Dsrv PRGRS_MAX_BLOCKSIZE,nnn option
      • Impact of block cursors on cache size—the -Dsrv QT_CACHE_SIZE,nnn option
      • Adjusting values
    • ODBC driver problems
•  Migration Issues
  • Creating a schema holder
  • Modifying your application
  • Modifying your insert trigger
•  Server Related Command Line Utilities and Startup Parameters
  •  OpenEdge Management or OpenEdge Explorer command line utilities for the DataServer
    • MSSCONFIG utility
    • MSSMAN utility
    • NSCONFIG utility
    • NSMAN utility
  •  Other command line utilities for the DataServer
    • PROBRKR command
    • PROSHUT command
    • DataServer startup parameters
•  Data Type Details
  • Data type conversion details
• Using qt_debug to Analyze Performance