Skip to main contentSkip to search
Powered by Zoomin Software. For more details please contactZoomin
Progress DocumentationProgress Documentation
Progress Documentation
  • Home
  • Home
  • EnglishČeštinaDeutsch (Germany)Español (Spain)Français (France)Italiano (Italy)Português (Brasil)日本語Русский (Russia)中文 (简体) (China)中文 (繁體, 台灣) (Taiwan)ar-AR
  • Login

OpenEdge Data Management: DataServer for Microsoft® SQL Server

DataServer connection management

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
Table of Contents
  • 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 OpenEdge AppServer or Progress 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
        • TLS-enabled DataServer
    • TLS enabled DataServer for MS SQL
      • Distributed DataServer configuration with TLS for MSS
      • Distributed DataServer using Unified Broker
      • Distributed DataServer using ProBroker
    • 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
        • Legacy mapping based on MAP to MSS DateTime:
        • 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
      • Handling rollback of sub-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 SQL DB
      • Statement level lock upgrade for JOIN query
      • Improving join performance for queries with FIRST or LAST criteria
      • Performance Optimization
    • 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 AppServer
    • 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
          • Establishing the TLS protocol in a DataServer broker instance of the Unified Broker
        • Editing the ubroker.properties file
        • Validating ubroker.properties content
        • Configuring multiple brokers
        • Using the NameServer
    • Configuring a self-service DataServer
      • Configuring the OpenEdge AppServer agent as a self-service DataServer session
      • Configuring the Progress Application Server for OpenEdge agent as a self-service DataServer session
    • Creating a schema holder
      • Before you begin
        • General schema holder creation tasks
      • Authorization and authentication
        • Database authentication during foreign database connect
        • Permissions for creating schema holder with constraints
        • 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
    • Starting ProBroker distributed DataServer broker with TLS-enabled Progress Networking
      • Non-default TLS certificates
    • 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
        • 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
          • Accessing the log file using the host machine
  • 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
      • Verifying a table
    • 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 contstraint definitions
        • Deleting one existing constraint definition
        • Deleting all existing constraint definitions
      • 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
        • Running the conversion in batch mode
    • Column width
    • Adjust schema utility
    • Delta df to MS SQL Server Incremental Schema Migration utility
      • Running the Incremental Schema Migration utility interactively
      • Running the Incremental Schema Migration utility silently (batch mode)
      • 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
        • Selecting an index to support ROWID
        • Dumping and Loading Schema Holder Information
    • Independent OpenEdge MS SQL Server Schema Pull
      • Running the Independent schema pull utility in batch mode
    • 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/ROWID format: the variable length option
          • Old format 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
Table of Contents

DataServer connection management

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
  • Last Updated: March 30, 2020
  • 1 minute read
    • OpenEdge
    • Version 12.2
    • Documentation

DataServer connection management

When an ABL session is established, the DataServer for Microsoft SQL Server opens the number of read-only connections specified in the PRGRS_CACHE_CONN value. The DataServer maintains these connections for the duration of an ABL session, reusing them for subsequent queries.

Contents
  • Enabling connection management
  • Disabling connection management
  • Connections exclusively for stored procedures
  • Impact on commits in stored procedures
TitleResults for “How to create a CRG?”Also Available inAlert