using odbc with microsoft sql server.pdf
TRANSCRIPT
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
1/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 1/66
Using ODBC with Microsoft SQL Server31 out of 53 rated this helpful
Amrish Kumar and Alan Brewer
Microsoft Corporation
September 1997
Updated May 5, 2010
IntroductionAn Application Programming Interface (API) is a definition of the set of functions an application can use to
access a system resource. ODBC is a database API based on the Call Level Interface (CLI) API definition
published by the standards organizations X/Open and ISO/CAE. ODBC applications can access data in many
of today's leading database management systems (DBMSs) by using ODBC drivers written to access thoseDBMSs. The application calls the ODBC API functions using a standard ODBC SQL syntax, then the ODBC
driver makes any necessary translations to send the statement to the DBMS and presents the results back
to the application.
This paper describes how application programmers using the ODBC API can optimize access to Microsoft
SQL Server when using the Microsoft SQL Server ODBC driver. The paper also discusses issues commonly
raised by customers who have called Microsoft Support for help with the SQL Server ODBC driver. This
paper is not a tutorial on ODBC programming in general, nor is it a comprehensive discussion about
performance tuning on SQL Server. It assumes the reader is already familiar with ODBC programming and
the use of SQL Server. For more information about ODBC, see the Microsoft ODBC 2.0 Programmer'sReference and SDK Guide available on MSDN and from Microsoft Press, and Inside ODBCby Kyle Geiger,
also available from Microsoft Press. For more information about SQL Server, see the SQL Server
documentation.
Except where noted, users should assume that this paper is discussing the operation of Microsoft
SQL Server version 6.5 and its associated version 2.65 ODBC driver. This paper uses the ODBC version 2.5
API functions because version 2.5 is the version used by most existing applications and is also the version
of the ODBC SDK that ships with Microsoft SQL Server Workstation version 6.5. Programmers writing ODBC
3.0 applications should refer to the Microsoft ODBC 3.0 Software Development Kit and Programmer's
Reference.
Readers primarily interested in performance-related issues will find most of the useful information in the
following sections of this paper:
"General Good Practices"
"Choosing a Cursor Option"
"SQLExecDirect vs. SQLPrepare/SQLExecute"
"Batching Procedure Calls"
"Text and Image Data"
ArchitectureThe Microsoft SQL Server ODBC driver uses the standard SQL Server components for communicating from
a client application to the database server. Rather than being implemented as a new layer over
SQL Server's older native API, DB-Library, the ODBC driver writes directly to the same Network-Library (Net-
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
2/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 2/66
rary ayer use y - rary. e r ver s mp emen e as a na ve o erver an s a
functional replacement of the DB-Library DLL. The components involved in accessing a SQL Server from an
ODBC application are described in the following sections.
Application
The application makes calls to the ODBC API using SQL statements written in either ODBC SQL syntax or
SQL Server Transact-SQL syntax.
ODBC Driver Manager
The ODBC driver manager is a very thin layer that manages the communications between the application
and any ODBC drivers with which the application works. The driver manager primarily loads the modules
comprising the driver and then passes all ODBC requests to the driver. There are Win32 and Win16
application programming interface vers ions of the driver manager. The Win32 driver manager is
Odbc32.dll; the Win16 driver manager is Odbc.dll.
SQL Server ODBC Driver
The SQL Server ODBC driver is a single DLL that responds to all calls the application makes to the ODBC
API. If the SQL statements from the application contain ANSI or ODBC SQL syntax that is not supported bySQL Server, the driver translates the statements into Transact-SQL syntax (the amount of translation is
usually minimal) and then passes the statement to the server. The driver also presents all results back to the
application. The Win32 SQL Server ODBC driver is Sqlsrv32.dll; the Win16 driver is Sqlsrvr.dll.
SQL Server Client Network Library
The driver communicates with the server through the SQL Server Net-Libraries using the SQL Server
application-level protocol called Tabular Data Stream (TDS). The SQL Server TDS protocol is a half-duplex
protocol with self-contained result sets (that contain both metadata and data) optimized for database
access.
There is a different Net-Library for each protocol SQL Server supports. The job of the Net-Library is to
process TDS packets from the driver while insulating the driver from details of the underlying protocol
stack. A SQL Server Net-Library accesses a network protocol by calling a network API supported by the
protocol stack. The Net-Libraries supplied by SQL Server for use by SQL Server client applications are listed
in the following table.
Net-Library Win32 DLL Win16 DLL
TCP/IP Windows Sockets Dbmssocn.dll Dbmssoc3.dll
Named pipes Dbnmpntw.dll Dbnmp3.dll
Multiprotocol Dbmsrpcn.dll Dbmsrpc3.dll
Novell SPX/IPX Dbmsspxn.dll Dbmsspx3.dll
Banyan Vines Dbmsvinn.dll Dbmsvin3.dll
DECNet Dbmsdecn.dll N/A
AppleTalk Dbmsadsn.dll N/A
Network Protocol Stack
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
3/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 3/66
e networ protoco stac transports t e pac ets etween t e c ent an t e server. e protoco
stack has components on both the client and the server.
Server Net-Library
The server Net-Libraries work on the server, passing TDS packets back and forth between SQL Server and
its clients. Each SQL Server can work simultaneously with any of the server Net-Libraries installed on the
server.
Open Data Services
Open Data Services supports an API defined for writing server applications. An Open Data Services
application can either be a server that accepts connections and processes queries (such as SQL Server or a
gateway to another DBMS), or it can be an extended stored procedure that allows DLLs written to the Open
Data Services API to be run as stored procedures within SQL Server. Open Data Services receives the TDS
packets from the underlying Net-Libraries and then passes the information to SQL Server by calling specific
Open Data Services callback functions implemented in the SQL Server code. It also encapsulates the results
coming back from the server in TDS packets that the Net-Library then sends back to the client.
SQL ServerSQL Server is the server engine that processes all queries from SQL Server clients.
Overall ODBC and SQL Server Architecture
The following illustration shows the overall ODBC and SQL Server architecture. It shows both a Win16 client
using TCP/IP and a Win32 client using Novell connecting to the same server.
Performance of ODBC as a Native API
One of the persistent rumors about ODBC is that it is inherently slower than a native DBMS API. This
reasoning is based on the assumption that ODBC drivers must be implemented as an extra layer over a
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
4/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 4/66
,
functions and SQL syntax. This translation effort adds extra processing compared with having the
application call directly to the native API. This assumption is true for some ODBC drivers implemented over
a native DBMS API, but the Microsoft SQL Server ODBC driver is not implemented this way.
The Microsoft SQL Server ODBC driver is a functional replacement of DB-Library. The SQL Server ODBC
driver works with the underlying Net-Libraries in exactly the same manner as the DB-Library DLL. The
Microsoft SQL Server ODBC driver has no dependence on the DB-Library DLL, and the driver will function
correctly if DB-Library is not even present on the client.
Microsoft's testing has shown that the performance of ODBC-based and DB-Librarybased SQL Server
applications is roughly equal.
The following illustration compares the ODBC and DB-Library implementations.
Driver and SQL Server Versions
The following table shows which versions of the Microsoft SQL Server ODBC driver shipped with recent
versions and service packs (SP) of Microsoft SQL Server. It also lists the operating system versions under
which the drivers are certified to run and the versions of SQL Server against which they are certified to
work.
Newer drivers recognize the capabilities of older databases and adjust to work with the features that exist
in the older server. For example, if a user connects a version 2.65 driver to a version 4.21a server, the driver
does not attempt to use ANSI or other options that did not exist in SQL Server 4.21a. Conversely, older
drivers do not use the features available in newer servers.
For example, if a version 2.50 driver connects to a version 6.5 server, the driver has no code to use any new
features or options introduced in the 6.5 server.
Driver
version
Driver
date
Shipped with SQL Server
version
SQL Server
versions
supported
Operating systems
supported
2.65.0252 06/16/97 6.5 SP3 6.5
6.0
4.21a
Windows NT3.5, 3.51, 4.0
Windows 95
Windows for Workgroups
3.11
Windows 3.1
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
5/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 5/66
2.65.0240 12/30/96 6.5 SP2 6.5
6.0
4.21a
Windows NT3.5, 3.51, 4.0
Windows 95
Windows for Workgroups
3.11
Windows 3.1
2.65.0213 07/30/96 6.5 SP1 6.5
6.04.21a
Windows NT3.5, 3.51, 4.0
Windows 95Windows for Workgroups
3.11
Windows 3.1
2.65.0201 04/03/96 6.5 6.5
6.0
4.21a
Windows NT3.5, 3.51, 4.0
Windows 95
Windows for Workgroups
3.11
Windows 3.1
2.50.0126 08/17/95 6.0 SP3
6.0 SP2
6.0 SP1
6.0
4.21a
Windows NT3.5, 3.51
Windows 95
Windows for Workgroups
3.11
Windows 3.1
2.50.0121 06/07/95 6.0 6.0
4.21a
Windows NT3.5, 3.51
Windows 95
Windows for Workgroups
3.11
Windows 3.1
Note:None of the Microsoft SQL Server ODBC drivers listed is certified to work with Sybase SQL Servers .
Applications needing to connect to Sybase SQL Servers must get an ODBC driver certified for use with
Sybase from either Sybase or a third-party ODBC driver vendor.
For more information about versions and Instcat.sql, see "Instcat.sql."
Setup and ConnectingAn ODBC application has two methods of giving an ODBC driver the information the driver needs to
connect to the proper server and database. Either the application can connect using an existing ODBC datasource containing this information, or it can call either SQLDriverConnect or SQLBrowseConnect, which
provides the information in the connection string parameter.
Setting up a Data Source
ODBC data sources contain information that tells a driver how to connect to a database. ODBC data
sources can be created by using the ODBC Administrator application in Control Panel or by an application
calling the ODBC SQLConfigDataSource function.
Data source definitions are stored in C:\Windows\System\Odbc.ini for the Microsoft Windows version 3.x
and Windows for Workgroups version 3.xoperating systems.
Win32 data sources fall into one of two categories (for details, see Microsoft Knowledge Base article
Q136481):
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
6/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 6/66
Windows NTuser-specific data sources and Windows 95 data sources
On the Microsoft Windows NT operating system, user data sources are specific to the
Windows NTaccount under which they were defined. User-specific data sources are not always
visible to applications running as Windows NT services. Windows 95 data sources are stored in the
following registry key:
HKEY_CURRENT_USER\Software\ODBC\Odbc.ini.
Windows NTsystem data sources
On Windows NT, system data sources are visible to all Windows NTaccounts on the computer.System data sources are always visible to applications running as Windows NTservices. The ODBC
driver manager that ships with Microsoft Office 97 also supports system data sources on
Windows 95 clients. Windows NTsystem data sources are stored in the following registry key:
HKEY_LOCAL_MACHINE\Software\ODBC\Odbc.ini.
Information about the drivers installed on a client is stored in C:\Windows\System\Odbcinst.ini in Windows
3.xor Windows for Workgroups 3.xand in HKEY_LOCAL_MACHINE\Software\ODBC\Odbcinst.ini in
Windows NTand Windows 95.
Each driver needs to store driver-specific information in its data sources. When a user adds a data sourceusing ODBC Administrator, the driver displays a dialog box, where the user specifies data source
information. When a data source is defined with SQLConfigDataSource, the function accepts an attribute
string parameter that can contain driver-specific keywords. All of the SQLConfigDataSource driver-specific
keywords for the SQL Server ODBC driver have counterparts in the dialog box that displays when using
ODBC Administrator.
Here's an example SQLConfigDataSource call that sets up a SQL Server data source referencing a server
using DHCP on TCP/IP:
Driver-specific SQLConfigDataSource Keywords
The following sections describe the driver-specific keywords supported by the Microsoft SQL Server ODBC
driver.
SERVER, NETWORK, and ADDRESS
The SERVER, NETWORK, and ADDRESS parameters associate a data source with a specific instance of
SQL Server on the network. These parameters are directly related to the advanced entries created with the
SQL Server Client Configuration Utility:
The SERVER parameter specifies a name or label for the connection entry.
The NETWORK parameter is the name of the Net-Library module to use, without the .dll suffix (for
RETCODE retcode;
UCHAR *szDriver = "SQL Server";
UCHAR *szAttributes =
"DSN=my65dsn\0DESCRIPTION=SQLConfigDSN Sample\0"
"SERVER=my65server\0ADDRESS=HRServer\0NETWORK=dbmssocn\0"
"DATABASE=pubs\0";
retcode = SQLConfigDataSource(NULL,
ODBC_ADD_DSN,
szDriver,
szAttributes);
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
7/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 7/66
examp e, mssocn, no mssocn. .
The ADDRESS parameter is the network address of the Windows NTserver running SQL Server.
If ADDRESS is present, it is always used as the network address for the connection. If ADDRESS is not
present, then SERVER is used as the network address for the connection.
Here's an example entry to make a named pipes connection to a server:
The following entry evaluates to the same network address:
Here's an example entry to make a sockets connection to the same computer:
There are two special cases to consider:
Connecting to a SQL Server running on the same computer as the client.
The ODBC data source for this case is specified as:
When using this data source, the driver attempts to connect to a SQL Server on the same computer
using Windows NTlocal-named pipes instead of a network implementation of named pipes.
Setting up a data source that connects to a server using whatever Net-Library is currently set as the
default on the client.
An example of an entry for this case is:
The default Net-Library is set using the SQL Server Client Configuration Utility.
The SERVER, NETWORK, and ADDRESS parameters specified on SQL Server ODBC driver data sources
SERVER=xyz,NETWORK=dbnmpntw,ADDRESS=HRServer
SERVER=HRServer,NETWORK=dbnmpntw
SERVER=tcpxyz,NETWORK=dbmssocn,ADDRESS=123.123.123.123,1433
SERVER=(local),NETWORK=(default),ADDRESS=(default)
SERVER=HRServer,NETWORK=(default),ADDRESS=(default)
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
8/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 8/66
opera e e same way as e erver , , an onnec on r ng parame ers spec e or a vance en r es
made with the SQL Server Client Configuration Utility. For more information about the advanced-entry
parameters, see the Microsoft SQL Server Administrator's Companion. The same parameters can be specified
in the data source creation dialog box displayed in ODBC Administrator.
The relationship between the parameters is illustrated in the following table.
SQLConfigDataSource ODBC Administrator SQL Client Configuration Utility
SERVER Server Server
NETWORK Network Library DLL
ADDRESS Network Address Connection String
If a data source is defined with the SERVER, NETWORK, and ADDRESS parameters, a SQL Server advanced
connection entry is made in the registry, and can be viewed using the SQL Client Configuration Utility.
DATABASE
This parameter specifies the default database for the ODBC data source.
LANGUAGE
This parameter specifies the default national language to use.
OEMTOANSI
This parameter specifies whether to convert extended characters to OEM values.
SQL Server is usually run with one of three code pages:
437 code page.The default code page for U.S. MS-DOS computers.
850 code page.
The code page typically used by UNIX systems.
ISO 8859-1 (Lantin1 or ANSI) code page.
The code page defined as a standard by the ANSI and ISO standards organizations. The default
code page for U.S. Windows computers. Sometimes called the 1252 code page.
The 437 and 850 code pages are sometimes collectively referred to as the OEM code pages.
All three code pages define 256 different values to use in representing characters. The values from 0 to128
represent the same characters in all three code pages. The values from 129 to 255, which are known as the
extended characters, represent different characters in all three code pages.
Because ODBC applications are Windows applications, they generally use ANSI code page 1252. If they are
communicating with a SQL Server also running ANSI code page 1252, there is no need for character-set
conversion. If they connect to a server running a 437 or 850 code page however, the driver must be
informed that it should convert extended characters from their 1252 values to 437 or 850 values before
sending them to the server. In this case, the data source should have OEMTOANSI=YES. For a more in-
depth discussion of SQL Server code pages, see Microsoft Knowledge Base article Q153449.
TRANSLATIONDLL
This parameter specifies the name of the ODBC translation DLL to use with the data source.
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
9/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 9/66
This parameter specifies the name of the translator to use with the data source.
TRANSLATIONOPTION
This parameter specifies whether translation should be done on the data going to SQL Server. YES specifies
translation; NO specifies no translation. For more information about ODBC translation, see the ODBC 2.0
Programmer's Reference.
USEPROCFORPREPARE
This parameter specifies whether the driver generates stored procedures to support the ODBC SQLPrepare
function. For more information, see "SQLExecDirect vs. SQLPrepare/SQLExecute."
The following driver-specific SQLConfigDataSource keywords are new in SQL Server 6.5 SP2.
QuotedID
This parameter specifies whether the driver should issue a SET QUOTED IDENTIFIERS ON option when
connecting to a SQL Server version 6.0 or later database. YES specifies QUOTED_IDENTIFIERS is ON; NO
specifies the option is OFF. For more information, see "SET Options Used by the Driver."
AnsiNPWThis parameter specifies whether the driver should SET ON the ANSI_NULLS, ANSI_PADDING, and
ANSI_WARNINGS options when connecting to a SQL Server version 6.5 or later database. YES specifies the
options are ON; NO specifies they are OFF. For more information, see "SET Options Used by the Driver."
The followingdriver-specific SQLConfigDataSource keywords are new in SQL Server 6.5.
QueryLogFile
This parameter specifies the file name the driver should use to log long-running queries. Include the full
path name for the file. For more information, see "ODBC Driver Profiling Features."
QueryLog_ONThis parameter specifies whether the data source should do query profiling. 1 specifies profiling is done;
omitting the parameter specifies no profiling. For more information, see "ODBC Driver Profiling Features."
QueryLogTime
This parameter specifies the interval for long-running queries. The interval is specified in milliseconds. If a
query is outstanding for a period exceeding the QueryLogTime, it is written to the QueryLogFile. For more
information, see "ODBC Driver Profiling Features."
StatsLogFile
This parameter specifies the file name the driver should use to log long performance statistics. Include the
full path name for the file. For more information, see "ODBC Driver Profiling Features."
StatsLog_On
This parameter specifies whether the data source should log performance statistics. 1 specifies profiling is
done; omitting the parameter specifies no profiling. For more information, see "ODBC Driver Profiling
Features."
Trusted_Connection
This parameter specifies whether the data source should use trusted connections when connecting to
SQL Server. 1 specifies trusted connections; omitting the parameter specifies no trusted connections. For
more information, see "Integrated and Standard Security."
Creating Data Sources in ODBC Administrator
When you add, modify, or double-click a SQL Server data source in ODBC Administrator, the SQL Server
ODBC driver displays the ODBC SQL ServerSetup dialog box. The parameters in this dialog box control
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
10/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 10/66
t e same eatures t at are contro e y t e SQLCon gDataSource eywor s ear er n t s paper,
although they have slightly different names. Many of the options are in the dialog box that displays when
you click Options. To specify the query and performance profiling options, click Options, and then click
Profiling.
Driver-specific SQLDriverConnect Keywords
An ODBC application can connect to a SQL Server without referencing a data source:
The SQL Server ODBC driver supports three classes of keywords on SQLDriverConnect:
The standard ODBC keywords
The SQL Server ODBC driver supports the four standard ODBC SQLDriverConnect keywords: DSN,
UID, PWD, and DRIVER.
The driver-specific SQLConfigDataSource keywords
On SQLDriverConnect the SQL Server ODBC driver supports all of the driver-specific keywords it
supports for SQLConfigDataSource. See the list earlier in this paper for a description of these
driver-specific keywords.
The driver-specific keywords APP and WSIDIn addition to supporting the same driver-specific keywords as SQLConfigDataSource,
SQLDriverConnect also supports the two driver-specific keywords APP and WSID.
APP
This keyword specifies the application name to be recorded in the program_name column in
master.dbo.sysprocesses . APP is equivalent to a DB-Library application calling the DBSETLAPP function in
C or the SQLSetLApp function in the Visual Basic programming system.
WSID
This keyword specifies the workstation name to be recorded in the hostname column in
master.dbo.sysprocesses . WSID is equivalent to a DB-Library application calling the DBSETLHOST function
in C or the SQLSetLHost function in Visual Basic.
Connection Messa es
RETCODE retcode;
UCHAR szDSN[MAXBUFLEN+1] =
"DRIVER={SQL Server};SERVER=MyServer;"
"UID=sa;PWD=astring;APP=Generic32;DATABASE=pubs";
UCHAR szUID[MAXUID+1] = "sa",
szAuthStr[MAXAUTHSTR+1] = "password",
szConnStrOut[MAXBUFLEN+1];
SWORD swStrLen;
retcode = SQLDriverConnect(hdbc1,NULL,
szDSN,
(SWORD)strlen(szDSN),
szConnStrOut,
MAXBUFLEN,
&swStrLen,
SQL_DRIVER_NOPROMPT);
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
11/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 11/66
The SQL Server ODBC driver returns SQL_SUCCESS_WITH_INFO on a successful SQLConnect,
SQLDriverConnect, or SQLBrowseConnect. When an ODBC application calls SQLError after getting
SQL_SUCCESS_WITH_INFO, it can receive the following messages:
5701indicates SQL Server initially putting the user's context into the default database defined at the
server for the login ID used in the connection
5703indicates the language being used on the server
If either the ODBC data source has a default database specified or the application specified theDATABASE keyword on SQLDriverConnect or SQLBrowseConnect, there will be a second 5701
message that indicates the user's context has been switched to the database requested.
The following example shows these messages being returned on a successful connect by the System
Administrator (SA) login. The SA login has its default database at the server defined as the master
database, the server is running US English, and the connect used an ODBC data source that specified pubs
as the default database.
Applications can ignore these 5701 and 5703 messages; they are informational only. Applications cannot,
however, ignore a return of SQL_SUCCESS_WITH_INFO return code on the SQLConnect, SQLDriverConnect,
or SQLBrowseConnect. This is because messages other than 5701 and 5703 that do require action may be
returned. For example, if a driver connects to a SQL Server with outdated system stored procedures, one of
the messages returned through SQLError is:
An application's error handling routines for SQL Server connections should call SQLError until it returns
SQL_NO_DATA_FOUND and act on any messages other than the ones that return apfNative code of 5701 or
5703.
Integrated and Standard Security
Full Connect:szSqlState = "01000", *pfNativeError = 5701,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Changed database context to 'master'."
szSqlState = "01000", *pfNativeError = 5703,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Changed language setting to 'us_english'."
szSqlState = "01000", *pfNativeError = 5701,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Changed database context to 'pubs'."
Successfully connected to DSN 'my60server'.
SqlState: 01000
pfNative: 0
szErrorMsg: "[Microsoft][ODBC SQL Server Driver]The ODBC
catalog stored procedures installed on server
my421server are version 02.00.4127; version 06.00.0115
or later is required to ensure proper operation.
Please contact your system administrator."
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
12/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 12/66
Standard security
The SA defines SQL Server logins with passwords in SQL Server and then associates the logins with
users in individual databases. With older versions of SQL Server, all connection attempts must
specify a valid login and password. SQL Server version 6.0 or 6.5 also allows trusted connections to
a server running standard security. SQL Server logins are separate from Windows NTuser IDs.
Integrated securityThe SA defines logins for those Windows NTuser accounts that are allowed to connect to
SQL Server. Users do not have to specify a separate login and password when they connect to
SQL Server after logging on to the Windows NTnetwork. When they attempt to connect, the Net-
Library attempts a trusted connection to SQL Server. If the user's Windows NTaccount is one that the
SA specified to SQL Server, the connection succeeds.
Mixed security
The SA defines both SQL Server logins and Windows NTaccounts as SQL Server logins. Users with
validated Windows NTaccounts can connect using trusted connections; other users can connect using
standard security with the SQL Server logins.
The SQL Server ODBC driver always uses a trusted connection when connecting to a server running
integrated security. The driver can also be instructed to open trusted connections when connecting to a
server that is running with standard or mixed security. Only the named pipes or multiprotocol Net-Libraries
support integrated security and trusted connections.
There are two ways to tell the driver to use trusted connections:
Driver-specific data source options
When defining a data source using the ODBC Administrator, you can select Use Trusted Connection.When defining a data source using SQLConfigDataSource, an application can specify
Trusted_Connection=1.
Driver-specific connection options
Before making a connect request, the application can set a driver-specific option:
Integrated security offers several benefits:
Passwords do not need to be stored in the application.
Passwords are never present in the SQL Server TDS packets.
Integrated security is easy to administer because the SA can use the SQL Security Manager utility to
create SQL Server logins from existing Windows NTaccounts.
Protocol Considerations
Integrated security is only available when using either the named pipes or multiprotocol Net-Libraries.
When using the multiprotocol Net-Library, the SA can also configure the server to encrypt packets sent
across the network, so that even users of network sniffers cannot see the data. The named pipes and
multiprotocol Net-Libraries can also work with either a TCP/IP, SPX/IPX, or NetBEUI protocol stack. This
means a client running only a TCP/IP protocol stack can use either the Windows sockets, named pipes, or
SQLSetConnectOption(hdbc, SQL_INTEGRATED_SECURITY, SQL_IS_ON);
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
13/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 13/66
multiprotocol Net-Libraries. The Windows sockets (TCP/IP), SPX/IPX, Appletalk, DECNet, and Banyan Vines
Net-Libraries only work with their single, associated, protocol stack.
Due to their added functionality, such as the encryption feature, the multiprotocol Net-Libraries are
somewhat slower than the others. Testing at Microsoft has found that the TCP/IP Net-Libraries are
somewhat faster than the other Net-Libraries. Other considerations, however, such as database design,
indexing, and the design of queries and applications, usually have a greater impact on performance than
the choice of a Net-Library.
Applications running against SQL Server 6.0 or 6.5 can sometimes improve their performance by resetting
the TDS network packet size. The default packet size is set at the server, and is 4K. 4K generally gives the
best performance. Applications can set the packet size themselves if testing shows that they perform better
with a different packet size. ODBC applications can do this by calling SQLSetConnectionOption with the
SQL_PACKET_SIZE option before connecting. Some applications may perform better with a larger packet
size, but performance improvements are generally minimal for packet sizes larger than 8K.
Verifying and Testing Data Sources
The Odbcping.exe utility can be used to check whether an ODBC connection can be made between a client
and a SQL Server. The command syntax to use the utility is:
odbcping {/Sservername |/Ddatasource}/Ulogin_id/Ppassword
where
servername
Is the network name of the server running SQL Server.
datasource
Is the name of an ODBC data source.
login_id
Is the SQL Server login ID.
password
Is the login password.
You must specify either/S or /D, but not both. (The version ofodbcping that ships with SQL Server 6.0 will
not accept the/D parameter, only/S,/U, and/P.)
When odbcping makes a successful connection, it displays a message indicating the connection was
successful and the versions of the driver and server. For example:
If the connect attempt is not successful, odbcping displays the errors it receives. (The 6.0 version of
odbcping does not display the Native Error code.) For example:
CONNECTED TO SQL SERVER
ODBC SQL Server Driver Version: 02.65.0201
SQL Server Version: SQL Server for Windows NT6.50 - 6.50.201 (Intel X86)
Apr 3 1996 02:55:53
Copyright (c) 1988-1997 Microsoft Corporation
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
14/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 14/66
ThepfNative (or Native Error) code is important in diagnosing connection problems. For more information,
see "pfNative Error Codes."
Processing Queries and Results
General Good Practices
The following sections discuss general practices that will increase the performance of SQL Server ODBC
applications. Many of the concepts apply to database applications in general.
Columns in a Result Set
Applications should select only the columns needed to perform the task at hand. Not only does this reduce
the amount of data sent across the network, it also reduces the impact of database changes on the
application. If an application does not reference a column from a table, then the application is not affected
by any changes made to that column.
Stored Procedures
Sites can realize performance gains by coding most of their SQL statements into stored procedures and
having applications call the stored procedures rather than issuing the SQL statements themselves. This
offers the following benefits:
Higher performance
The SQL statements are parsed and compiled only when the procedures are created, not when the
procedures are executed by the applications.
Reduced network overhead
Having an application execute a procedure instead of sending sometimes complex queries across
the network can reduce the traffic on the network. If an ODBC application uses the ODBC { CALL
MyProcedure} syntax to execute a stored procedure, the ODBC driver makes additional
optimizations that eliminate the need to convert parameter data (for more information, see "ODBCCall vs. Transact-SQL EXECUTE").
Better consistency
The organization's business rules can be coded and debugged once in a stored procedure, and they
will then be consistently applied by all of the applications. The site does not have to depend on all
application programmers coding their SQL statements correctly in all the applications.
Better accuracy
Most sites will have their best SQL programmers developing stored procedures. This means that the
SQL statements in procedures tend to be more efficient and have fewer errors than when the code is
developed multiple times by programmers of varying skill levels.
The Enterprise versions of the Microsoft Visual C++ development system and Microsoft Visual Basic
programming system also offer a new SQL debugger tool. With SQL Debugger, programmers can use the
COULD NOT CONNECT TO SQL SERVER
SQLState: 01000 Native Error: 2
Error Message: [Microsoft][ODBC SQL Server Driver][dbnmpntw]
ConnectionOpen (CreateFile()).
SQLState: 08001 Native Error: 6
Error Message: [Microsoft][ODBC SQL Server Driver][dbnmpntw]
Specified SQL Server not found.
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
15/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 15/66
,
variables, to debug their SQL Server stored procedures.
Batches
An application that builds several SQL statements to execute realizes better performance if it batches the
statements together and sends them to the server all at once. This will reduce the number of network
roundtrips the application uses to perform the same work. For example:
The application uses SQLMoreResults to be positioned on the next result set when they are finished with
the current result set.
SQLBindCol and SQLGetData
Excess use ofSQLBindCol to bind a result set column to a program variable is expensive because
SQLBindCol causes an ODBC driver to allocate memory. When an application binds a result column to a
variable, that binding remains in effect until the application either calls SQLFreeStmt with fOption set to
either SQL_DROP or SQL_UNBIND. The bindings are not automatically undone when the statement
completes.
This logic allows applications to effectively deal with situations where they may execute the same SELECT
statement several times with different parameters. Since the result set will keep the same structure, the
application can bind the result set once, process all the different SELECT statements, then do a
SQLFreeStmt with fOption set to SQL_UNBIND after the last execution. Applications should not call
SQLBindCol to bind the columns in a result set without first calling SQLFreeStmt with fOption set toSQL_UNBIND to free any previous bindings.
When using SQLBindCol, applications can either do row-wise or column-wise binding. Row-wise binding is
somewhat faster than column-wise binding.
Applications can use SQLGetData to retrieve data on a column-by-column basis, instead of binding the
result set columns using SQLBindCol. If a result set contains only a couple of rows, then using SQLGetData
instead ofSQLBindCol is faster, otherwise, SQLBindCol gives the best performance. If an application does
not always put the data in the same set of variables, it should use SQLGetData instead of constantly
rebinding. Applications can only use SQLGetData on columns that are in the select list after all columns are
bound with SQLBindCol. The column must also appear after any columns on which the application hasalready used a SQLGetData.
Data Conversion
The ODBC functions dealing with moving data into or out of program variables, such as SQLBindCol,
SQLBindParameter, and SQLGetData, allow implicit conversion of data types. For example, an application
that displays a numeric column can ask the driver to convert the data from numeric to character:
SQLExecDirect(hstmt,
"select * from authors; select * from titles",
SQL_NTS);'
retcode = SQLBindCol(hstmt1,
1, // Point to integer column
SQL_C_CHAR,
&szCharVar,
ARRAYSIZE,
&cbCharVar);
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
16/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 16/66
Applications should minimize data conversions. Unless data conversion is a required part of the application,
the application should bind columns to a program variable of the same data type as the column in the
result set.
If the application needs to have the data converted, it is more efficient for the application to ask the driver
to do the data conversion than for the application to do it.
The driver normally just transfers data directly from the network buffer to the application's variables.
Requesting the driver to perform data translation forces the driver to buffer the data and use CPU cycles to
perform the conversion.
Data Truncation
If an application attempts to retrieve data into a variable that is too small to hold it, the driver generates a
warning. The driver must allocate memory for the warning messages and spend CPU resources on some
error handling. This can all be avoided if the application allocates variables large enough to hold the data
from the columns in the result set, or uses the SUBSTRING function in the select list to reduce the size of thecolumns in the result set.
Query Options
Timeout intervals can be adjusted to prevent problems. Also, having different settings for some ODBC
statement and connection options among several open connection or statement handles can generate
excess network traffic.
Calling SQLSetConnectOption with fOption set to SQL_LOGIN_TIMEOUT controls the amount of time an
application waits for a connection attempt to timeout while waiting to establish a connection (0 specifies an
infinite wait). Sites with slow response times can set this value high to ensure connections have sufficient
time to complete, but the interval should always be low enough to give the user a response in a reasonableamount of time if the driver cannot connect.
Calling SQLSetStmtOption with fOption set to SQL_QUERY_TIMEOUT sets a query timeout interval to
protect the server and the user from long running queries.
Calling SQLSetStmtOption with fOption set to SQL_MAX_LENGTH limits the amount oftext and image data
that an individual statement can retrieve. Calling SQLSetStmtOption with fOption set to SQL_MAX_ROWS
also limits a rowset to the first n rows if that is all the application needs. Note that setting SQL_MAX_ROWS
causes the driver to issue a SET ROWCOUNT statement to the server, which will affect all SQL statements,
including triggers and updates.
Care should be used when setting these options, however. It is best if all statement handles on a connection
handle have the same settings for SQL_MAX_LENGTH and SQL_MAX_ROWS. If the driver switches from a
statement handle to another with different values for these options, the driver must generate the
appropriate SET TEXTSIZE and SET ROWCOUNT statements to change the settings. The driver cannot put
these statements in the same batch as the user SQL since the user SQL can contain a statement that must
be the first statement in a batch, therefore the driver must send the SET TEXTSIZE and SET ROWCOUNT
statements in a separate batch, which automatically generates an extra roundtrip to the server.
SET NOCOUNT
Applications can execute the Transact-SQL statement SET NOCOUNT ON. When this is set on, SQL Serverdoes not return an indication of how many rows were affected by data-modification statements, or by any
statements within procedures. When SET NOCOUNT is ON, the driver does not get the information it needs
to return the number of rows affected should the application call SQLRowCount after a data-modification
statement.
printf("fetched row cola = %s\n", szCharVar);
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
17/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 17/66
All statements executed in a stored procedure, including SELECT statements, generate an "xrows affected"
message. Issuing a SET NOCOUNT ON at the start of a large stored procedure can significantly reduce the
network traffic between the server and client and improve performance by eliminating these messages.
These messages are typically not needed by the application when it is executing a stored procedure.
Cursors
Starting with SQL Server 6.0, the SQL Server ODBC driver supports the ODBC cursor options by using server
cursors.
Cursor Types
The ODBC standard assumes that a cursor is automatically opened on each result set and, therefore, does
not make a distinction between a result set and a cursor. SQL Server SELECT statements, however, always
return a result set. A SQL Server cursor is a separate entity created when the application needs to perform
cursor operations such as scrolling and positioned updates.
In the ODBC model, all SQL statements return a result set within a cursor, and an application retrieves rows
through the cursor using either SQLFetch or SQLExtendedFetch. Before executing an SQL statement, an
ODBC application can call SQLSetStmtOption to set statement options that control the cursor's behavior.
These are the default settings for the cursor options.
Option Default
SQL_CURSOR_TYPE SQL_CURSOR_FORWARD_ONLY
SQL_CONCURRENCY SQL_CONCUR_READ_ONLY
SQL_ROWSET_SIZE 1
When running with these default settings, the application can only use SQLFetch to fetch through the result
set one row at a time from the start of the result set to the end. When running with these default settings,
the SQL Server ODBC driver requests a default result set from the server. In a default result set, SQL Server
sends the results back to the client in a very efficient, continuous stream. The calls to SQLFetch retrieve the
rows out of the network buffers on the client.
It is possible to execute a query with these default settings, and then change the SQL_ROWSET_SIZE after
the SQLExecDirect or SQLExecute complete. In this case, SQL Server still uses a default result set to
efficiently send the results to the client, but the application can also use SQLExtendedFetch to retrieve
multiple rows at a time from the network buffers.
An ODBC application can change the SQL_CURSOR_TYPE to request different cursor behaviors from the
result set. The types of cursors that can be set are:
Static cursors
In a static cursor, the complete result set is built when the cursor is opened. The cursor does not
reflect any changes made in the database that affect either the rows in the result set, or the values in
the columns of those rows. In other words, static cursors always display the result set as it was when
the cursor was opened. If new rows have been inserted that satisfy the conditions of the cursor's
SELECT statement, the do not a ear in the cursor. If rows in the result set have been u dated, the
SQL_CURSOR_TYPE=SQL_CURSOR_STATIC
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
18/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 18/66
new data values do not appear in the cursor. Rows appear in the result set even if they have been
deleted from the database. No UPDATE, INSERT, or DELETE operations are reflected in a static
cursor (unless the cursor is closed and reopened), not even modifications made by the same user
who opened the cursor. Static cursors are read-only.
Dynamic cursors
Dynamic cursors are the opposite of static cursors; they reflect all changes made to the rows in their
result set as the user scrolls around the cursor. In other words, the data values and membership of
rows in the cursor can change dynamically on each FETCH. The cursor shows all DELETE, INSERT, and
UPDATE statements either made by the user who opened the cursor or committed by other users.
Dynamic cursors do not support FETCH ABSOLUTE because the size of the result set and the position
of rows within the result set are not constant. The row that starts out as the tenth row in the result se
may be the seventh row the next time a FETCH is performed.
Forward-only cursors
This cursor is similar to a dynamic cursor, but it only supports fetching the rows serially in sequence
from the start to the end of the cursor.
Keyset-driven cursor
With a keyset-driven cursor, the membership of rows in the result set and their order is fixed when
the cursor is opened. Keyset-driven cursors are controlled through a set of unique identifiers (keys),
known as the keyset. The keys are built from a set of columns that uniquely identify the rows. The
keyset is the set of all the key values that made up the rows in the result set when the cursor was
opened. Changes to data values in nonkeyset columns for the rows (made by the current user or
committed by other users) are reflected in the rows as the user scrolls through the cursor. Inserts
are not reflected unless the cursor is closed and reopened. Deletes generate an "invalid cursor
position" error (SQLState S1109) if the application attempts to fetch the missing row. If an update is
made to a key-column value, it operates like a delete of the old key value followed by an insert of
the new key value, and the new key value is not visible to the cursor. Attempts to fetch the old key
value generate the S1109 error, but the new key value is not visible to the cursor.
Mixed cursors
SQL_CURSOR_TYPE=SQL_CURSOR_DYNAMIC
SQL_CURSOR_TYPE=SQL_CURSOR_FORWARD_ONLY
SQL_CURSOR_TYPE=SQL_CURSOR_KEYSET_DRIVEN
SQL_CURSOR_TYPE=SQL_CURSOR_KEYSET_DRIVEN
SQL_KEYSET_SIZE=n
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
19/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 19/66
SQL Server does not support mixed cursors.
All ODBC cursors support the concept of a rowset, which is the number of rows returned on an
individual SQLExtendedFetch. For example, if an application is presenting a 10-row grid to the user,
the cursor can be defined with a rowset size of 10 to simplify mapping data into the grid.
Concurrency Option Overview
In addition to the cursor types, cursor operations are also affected by the concurrency options set by theapplication:
With this option set, the cursor does not support UPDATE, INSERT, or DELETE statements.
Locks are not held on the underlying rows that make up the result set.
This option offers optimistic concurrency control. Optimistic concurrency control is a standard
part of transaction control theory and is discussed in most papers and books on the subject.
The application uses optimistic control when "optimistic" that there is a slight chance that
anyone else may have updated the row in the interval between when the row is fetched and
when the row is updated. When the cursor is opened in this mode, no locks are held on theunderlying rows to maximize throughput. If the user attempts an UPDATE, the current values in
the row are compared with the values retrieved when the row was fetched. If any of the values
have changed, SQL Server returns an error . If the values are the same, the cursor engine
performs the UPDATE.
Selecting this option means the application must deal with an occasional error indicating that
another user updated the row and changed the values. A typical action taken by an
application that receives this error would be to refresh the cursor, to get the new values, and
then let the user or application decide if the UPDATE should still be performed. Note that text
and image columns are not used for concurrency comparisons.
This optimistic concurrency control option is based on row versioning. With row versioning,
the underlying table must have a version identifier of some type that the cursor engine can
use to determine whether the row has been changed since it was read into the cursor. In
SQL Server, this is the facility offered by the timestamp data type. SQL Server timestamps are
binary numbers that indicate the relative sequence of modifications in a database. Each
database has a global current timestamp value, @@dbts, which is incremented with every
change in the database. If a table has a timestamp column, then its timestamp column is
updated with the current @@dbts value every time the row is updated. The cursor engine can
then compare a row's current timestamp value with the timestamp value that was first
retrieved into the cursor to determine whether the row has been updated. The engine does
SQL_CONCURRENCY = SQL_CONCUR_READONLY
SQL_CONCURRENCY = SQL_CONCUR_VALUES
SQL_CONCURRENCY = SQL_CONCUR_ROWVER
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
20/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 20/66
not ave to compare t e va ues n a co umns, on y t e t mestamp va ue. an app cat on
requests SQL_CONCUR_ROWVER on a table that does not have a timestamp column, the
cursor defaults to the values-based optimistic concurrency control, SQL_CONCUR_VALUES.
This option implements pessimistic concurrency control, in which the application attempts to
lock the underlying database rows at the time they are read into the cursor result set. For
cursors using server cursors, an update intent lock is placed on the page holding the row
when it is read into the cursor. If the cursor is opened within a transaction, these intent-to-
update locks are held until the transaction is committed or rolled back. If the cursor has been
opened outside a transaction, the lock is dropped when the next row is fetched. Thus,
applications wanting full pessimistic concurrency control would typically open the cursor within
a transaction. An update intent lock prevents any other task from acquiring an update intent
or exclusive lock, which prevents any other task from updating the row. An update intent lock,
however, will not block a shared lock, so it does not prevent other tasks from reading the
row, unless the second task is also requesting a read with an update intent lock.
In all of these concurrency options, when any row in the cursor is updated, SQL Server locks it with an
exclusive lock. If the update has been done within a transaction, the exclusive lock is held until the
transaction is terminated. If the update has been done outside of a transaction, the update is automatically
committed when it is completed and the exclusive lock is freed. Because SQL Server must acquire an
exclusive lock before it updates the row, positioned updates done through a cursor (just like standard
updates) can be blocked by other connections holding a shared lock on the row.
Isolation Levels
The full locking behavior of cursors is based on an interaction between the concurrency options discussed
above and the transaction isolation level set by the client. ODBC clients set the transaction isolation level by
setting the connection option SQL_TXN_ISOLATION. Users should combine the locking behaviors of the
concurrency and transaction isolation level options to determine the full locking behavior of a specific
cursor environment.
READ COMMITTED (The default for both SQL Server and ODBC)
SQL Server acquires a shared lock while reading a row into a cursor but frees the lock immediately
after reading the row. Because a shared lock request is blocked by an exclusive lock, a cursor is
prevented from reading a row that another task has updated but not yet committed.
READ UNCOMMITTED
SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. This
means that cursors can be populated with values that have already been updated but not yet
committed. The user is bypassing all of SQL Server's locking transaction control mechanisms.
SQL_CONCURRENCY = SQL_CONCUR_LOCK
SQL_TXN_ISOLATION = SQL_TXN_READ_COMMITED
SQL_TXN_ISOLATION = SQL_TXN_READ_UNCOMMITED
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
21/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 21/66
REPEATABLE READ or SERIALIZABLE
Or
SQL Server still requests a shared lock on each row as it is read into the cursor as in READ
COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end
of the transaction instead of being freed after the row is read. This is the same effect as specifying
HOLDLOCK on a SELECT statement.
Note that the ODBC API specifies additional transaction isolation levels, but these are not supported by
SQL Server or the Microsoft SQL Server ODBC driver.
Server Cursors
Prior to version 6.0, SQL Server sent result sets back to clients using only one type of result set, the default
result set. While the default result set is efficient at sending results back to clients, it only supports the
characteristics of the default ODBC result set: forward-only, read-only, and a rowset size of one. Because of
this, the Microsoft SQL Server ODBC drivers that shipped with SQL Server version 4.2xonly supported the
default ODBC settings.
When using a default result set, there is only one roundtrip between the client and server; this occurs at the
time the SQL statement is executed. After the statement is executed, the server sends the packets containing
the results back to the client until all of the results have been sent back or the client has cancelled the
results by calling SQLMoreResults. Calls to SQLFetch or SQLExtendedFetch do not generate roundtrips to
the server, they just pull data from the client network buffers into the application.
SQL Server 6.0 introduced cursors that are implemented on the server (server cursors). There are two types
of server cursors:
Transact-SQL cursors
This type of cursor is based on the ANSI syntax for cursors and is meant to be used in Transact-SQL
batches, primarily in triggers and stored procedures. Transact-SQL cursors are not intended to be
used in client applications.
API server cursors
This type of cursor is created by either the DB-Library or ODBC APIs. The SQL Server ODBC driver
that shipped with SQL Server 6.0 uses API server cursors to support the ODBC cursor options.
Users access the functionality of API server cursors through either ODBC or DB-Library. If an ODBC
application executes a statement with the default cursor settings, the SQL Server ODBC driver requests a
default result set from SQL Server. If the application sets the ODBC cursor type options to anything other
than the defaults, however, then the SQL Server ODBC driver instead requests the server to implement a
server cursor with the same options requested by the application. Since the cursor is implemented on the
server, the driver does not have to use memor on the client to build a client-based cursor. Server cursors
SQL_TXN_ISOLATION = SQL_TXN_REPEATABLE_READ_UNCOMMITED
SQL_TXN_ISOLATION = SQL_TXN_SERIALIZABLE
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
22/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 22/66
can also reduce network traffic in cases where a user decides they do not need to retrieve an entire result
set. For example, if a user opens a cursor with 1,000 rows but then finds what they were looking for in the
first 100 rows they scroll through, the other 900 rows are never sent across the network.
When using server cursors, each call to SQLFetch, SQLExtendedFetch, or SQLSetPos causes a network
roundtrip from the client to the server. All cursor statements must be transmitted to the server because the
cursor is actually implemented on the server.
One potential drawback of server cursors is that they currently do not support all SQL statements. Servercursors do not support any SQL statements that generate multiple result sets, therefore they cannot be
used when the application executes either a stored procedure or a batch containing more than one select.
If the application has set options that cause the driver to request an API server cursor, and then it executes
a statement that server cursors do not support, the application gets an error:
Or
ODBC applications getting either of these errors when attempting to use server cursors with multiple
statement batches or stored procedures should switch to using the ODBC default cursor options.
Multiple Active Statements per Connection
After SQL Server has received a statement, the SQL Server TDS protocol does not allow acceptance of any
other statements from that connection until one of the following occurs:
The client application processes the entire result set.
The client sends a statement telling the server it can close the remainder of the result set.
This means that when an ODBC application is using a default result set, SQL Server does not support
multiple active statement handles on a connection handle and only one statement can be actively processed
at any point in time.
When an ODBC application is using API server cursors, however, the driver can support multiple active
statements on a connection. When the rowset for each cursor command has been received back at theclient, SQL Server considers the statement to have completed, and it accepts another statement from
another statement handle over that connection handle.
For example, an application can do the following to initiate processing on two statement handles:
SQLState: 37000
pfNative: 16937
szErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot open a cursor on a stored procedure that
has anything other than a single select statement in it.
SQLState: 37000
pfNative: 16938
szErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]
sp_cursoropen. The statement parameter can only
be a single select or a single stored procedure.
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
23/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 23/66
When the SQLExecDirect on hstmt1 is executed, the SQL Server ODBC driver issues a cursor open request.
When SQL Server completes the cursor open, it considers the statement to be finished and allows the
application to then issue a statement on another hstmt:
Once again, after the server has finished with the cursor open request issued by the client, it considers the
statement to be completed. If at this point the ODBC application makes a fetch request as follows, the
SQL Server ODBC driver sends SQL Server a cursor fetch for the first five rows of the result set:
After the server has transferred the five rows to the driver, it considers the fetch processing completed and
accepts new requests. The application could then do a fetch on the cursor opened for the other statement
handle:
SQL Server accepts this second statement on the connection handle because, as far as it is concerned, it
has completed the last statement on the connection handle, which was the fetch of the first five rows of the
rows for hstmt1.
Choosing a Cursor Option
The choice of cursor type depends on several variables, including:
Size of the result set.
Percentage of the data likely to be needed.
Performance of the cursor open.Need for cursor operations like scrolling or positioned updates.
Desired level of visibility to data modifications made by other users.
The default settings would be fine for a small result set if no updating is done, while a dynamic cursor
SQLAllocEnv(&henv):
SQLAllocConnect(henv, &hdbc);
SQLAllocStmt(hdbc, &hstmt1);
SQLAllocStmt(hdbc, &hstmt2);
SQLSetConnectOption(hdbc, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC);
SQLSetConnectOption(hdbc, SQL_ROWSET_SIZE, 5);
SQLExecDirect(hstmt1, "select * from authors", SQL_NTS);
SQLExecDirect(hstmt2, "select * from titles", SQL_NTS);
SQLExtendedFetch(hstmt1, SQL_FETCH_NEXT, 1, ...);
SQLExtendedFetch(hstmt2, SQL_FETCH_NEXT, 1, ...);
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
24/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 24/66
of the rows.
Some simple rules to follow in choosing a cursor type are:
Use default settings for singleton selects (returns one row), or other small result sets. It is more
efficient to cache a small result set on the client and scroll through the cache.
Use the default settings when fetching an entire result set to the client, such as when producing a
report. After SQLExecute or SQLExecDirect, the application can increase the rowset size to retrievemultiple rows at a time using SQLExtendedFetch.
The default settings cannot be used if the application is using positioned updates.
The default settings cannot be used if the application is using multiple active statements.
The default settings must be used for any SQL statement or batch of SQL statements that will
generate multiple result sets.
Dynamic cursors open faster than static or keyset-driven cursors. Internal temporary work tables
must be built when static and keyset-driven cursors are opened but are not required for dynamic
cursors.
Use keyset-driven or static cursors if SQL_FETCH_ABSOLUTE is used.
Static and keyset-driven cursors increase the usage oftempdb. Static server cursors build the entire
cursor in tempdb; keyset-driven cursors build the keyset in tempdb.
Each call to SQLFetch or SQLExtendedFetch causes a roundtrip to the server when using server cursors.
Applications should minimize these roundtrips by using a reasonably large rowset size and by using
SQLExtendedFetch instead ofSQLFetch whenever possible.
Implicit Cursor Conversions
Applications can request a cursor type through SQLSetStmtOption and then execute an SQL statement that
is not supported by server cursors of the type requested. A call to SQLExecute or SQLExecDirect returns
SQL_SUCCESS_WITH_INFO and SQLError returns:
The application can determine what type of cursor is now being used by calling SQLGetStmtOption with
fOption set to SQL_CURSOR_TYPE. The cursor type conversion applies to only one statement. The next
SQLExecDirect or SQLExecute will be done using the original statement cursor settings.
Both SQL Server 6.0 and 6.5 have the following restrictions:
If an SQL statement contains UNION, UNION ALL, GROUP BY, an outer join, or DISTINCT, all cursor
types other than static are converted to static.
If a keyset-driven cursor is requested and there is at least one table that does not have a unique
index, the cursor is converted to a static cursor.
SQL Server 6.0 has the following additional restrictions:
If a dynamic cursor is requested and there is at least one table that does not have a unique index,
the cursor is converted to a static cursor.If a dynamic cursor is requested and the SQL statement contains an ORDER BY that does not match a
unique index or subquery, the cursor is converted to a static cursor.
SQLExecDirect vs. SQLPrepare/SQLExecute
szSqlState = "01S02", *pfNativeError = 0,
szErrorMsg="[Microsoft][ODBC SQL Server Driver]Cursor type changed"
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
25/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 25/66
This section discusses when SQLExecDirect or SQLPrepare/SQLExecute should be used.
Driver Implementation Overview
ODBC offers two options for executing a statement. If a statement is only executed once or twice, the
application can use SQLExecDirect to execute the statement. The ODBC definition ofSQLExecDirect states
that the database engine parses the SQL statement, compiles an execution plan, executes the plan, and then
returns results to the application.
If an application executes the same statement many times, then the overhead of having the engine compilethe plan every time degrades performance. An application in this case can call SQLPrepare once and then
call SQExecute each time it executes the statement. The ODBC definition ofSQLPrepare states that the
database engine just parses the statement and compiles an execution plan, then returns control to the
application. On SQLExecute, the engine simply executes the precompiled execution plan and returns the
results to the client, thereby saving the overhead of parsing and recompiling the execution plan.
SQL Server itself does not directly support the SQLPrepare/SQLExecute model, but the SQL Server ODBC
driver can use stored procedures to emulate this behavior. On a SQLPrepare, the driver asks the server to
create a stored procedure that contains the SQL statement from the application. On SQLExecute, the driver
executes the created stored procedure. The ODBC driver uses stored procedures to support
SQLPrepare/SQLExecute when the option is enabled either in the data source or the SQLDriverConnect
keywords. For example, if an application calls:
The driver sends a statement to the server:
When the application then does:
The driver sends a remote stored procedure call to have the server run the #ODBC#nnnnnnnn procedure.
Because a CREATE PROCEDURE statement essentially compiles an SQL statement into an execution plan,
and an EXECUTE statement simply executes the precompiled plan, this meets the criteria for the
SQLPrepare/SQLExecute mode.
Excess or inappropriate use ofSQLPrepare/SQLExecute degrades an application's performance.
SQL Server applications should only use SQLPrepare/SQLExecute if they plan to execute a statement more
than 3 to 5 times. If an application needs to execute a statement only once, using SQLPrepare/SQLExecute
generates two roundtrips to the server: one to create the stored procedure and another to execute it.
SQLExecDirect would only use one roundtrip and would also save the overhead of creating and storing a
stored procedure. Excess use ofSQLPrepare can also cause locking contention in the system tables in
SQLPrepare(hstmt, "select *from authors", SQL_NTS);
CREATE PROCEDURE #ODBC#nnnnnnnn as
SELECT * FROM authors
SQLExecute(hstmt);
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
26/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 26/66
tempdb as concurrent users continually try to create the stored procedures to support SQLPrepare.
You may think that applications must use SQLPrepare/SQLExecute to use parameter markers, even if the
application will only execute the statement once or twice. This is not true, applications can use parameter
markers with SQLExecDirect by calling SQLBindParameter before SQLExecDirect.
If an application will be run by many concurrent users and the users will all be using the same SQL
statement, the best approach is to create the SQL statement as a permanent, parameterized, stored
procedure and executed it with SQLExecDirect. Having many users concurrently issue SQLPreparecommands can create a concurrency problem on the system tables in tempdb. Even if each user is
executing exactly the same statement, the SQL Server ODBC driver on each client is creating its own copy of
a temporary stored procedure in tempdb. If the SQL statement is created as a parameterized stored
procedure, however, the procedure is created only once. Each ODBC application does not have to create a
new procedure for its exclusive use, it simply uses a copy of the permanent procedure's execution plan
from the procedure cache.
When used in the appropriate circumstances (to execute a single statement several times),
SQLPrepare/SQLExecute can provide significant performance savings.
Impact on TempdbSQL Server 6.0 introduced temporary stored procedures, which are identified by having a number sign (#)
as the first character in the name. These procedures operate like temporary tables and are automatically
dropped by the server if the connection is broken. The SQL Server ODBC driver now creates the procedures
that support SQLPrepare as temporary procedures. This makes it impossible for the ODBC-related stored
procedures to build up as a result of broken network connections or client computer failures. However, the
temporary stored procedures are always created in tempdb. This means that sites running SQL Server 6.0
or 6.5 with ODBC applications that use SQLPrepare must ensure that tempdb is large enough to hold the
temporary procedures generated to support SQLPrepare.
There is another factor to consider in relation to how many stored procedures exist in tempdb. ODBCapplications call SQLSetConnectoption with fOption set to the driver-specific value
SQL_USE_PROCEDURE_FOR_PREPARE and vParam set to either SQL_UP_OFF, SQL_UP_ON, or
SQL_UP_ON_DROP to control the generation of temporary procedures.
SQL_UP_OFF means that the driver does not generate stored procedures.
SQL_UP_ON_DROP means that the driver generates stored procedures, and that they are dropped
when the application does a SQLDisconnect, a SQLFreeStmt with fOption set to SQL_DROP, or the
next time the application issues SQLPrepare on the same statement handle.
SQL_UP_ON means that temporary procedures are created, but they are only dropped on a
SQLDisconnect.
SQL_UP_ON is the default setting. The driver can reuse procedures if an application re-prepares the same
SQL statement, and most applications realize a performance boost because the driver is not having to
continually drop stored procedures. This may result in a build up of #ODBC procedures in tempdb,
however, from applications that never disconnect or applications that make heavy use ofSQLPrepare.
These applications should set SQL_UP_ON_DROP by calling SQLSetConnectOption. Starting with the driver
that shipped in SQL Server 6.5 SP2, SQL_UP_ON_DROP is now an option that can be specified on data
sources for the SQL Server ODBC driver.
Other Considerations of SQLPrepare
To keep from having to hold locks on the tempdb system tables for the length of a user transaction, theSQL Server ODBC driver does not generate a stored procedure for SQLPrepare if it is called within a
transaction. The exception to this is when the SQLPrepare is the first statement in the transaction. In this
case, the driver generates a stored procedure but then immediately commits the CREATE PROCEDURE
statement.
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
27/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 27/66
The driver does not generate a stored procedure for a SQLPrepare that uses the ODBC CALL escape clause
to call a stored procedure. On SQLExecute, the driver executes the called stored procedure (there is no
need to create a temporary stored procedure).
Calling either SQLDescribeCol or SQLDescribeParam before calling SQLExecute generates an extra
roundtrip to the server. On SQLDescribeCol, the driver removes the WHERE clause from the query and
sends it to the server with SET FMTONLY ON to get the description of the columns in the first result set
returned by the query. On SQLDescribeParam, the driver calls the server to get a description of the
columns in the tables referenced by the query. This method also has some restrictions, such as not being
able to resolve parameters in subqueries.
Stored Procedures
This section discusses issues related to executing stored procedures using the SQL Server ODBC driver.
ODBC Call vs. Transact-SQL EXECUTE
Applications can call SQL Server procedures using either the Transact-SQL EXECUTE statement or the ODBC
SQL CALL escape clause (the Transact-SQL statement appears first, followed by the ODBC SQL CALL):
Using the ODBC syntax is recommended. The ODBC syntax, in addition to being more portable, offers
improved features and performance over the EXECUTE statement.
The SQL Server TDS protocol provides two methods of sending a procedure to the server: the procedure
can be sent to the server as a regular SQL statement, or it can be sent as a TDS Remote Procedure Call
(RPC).
The TDS RPC syntax was originally defined for use by servers when one server is asked to execute a remote
stored procedure on another server, but it can also be used by applications. Using the TDS RPC syntax
means neither the driver nor the server need to perform any parameter conversions. This improves
performance, especially for image parameters. The SQL Server ODBC driver uses the TDS RPC syntax if the
application uses the ODBC CALL escape clause; it uses the regular SQL statement syntax if the application
uses the Transact-SQL EXECUTE statement.
Using the ODBC CALL escape clause also allows the application to retrieve output parameters and returncodes from a stored procedure. Output parameter and return code processing is discussed below.
Output Parameters and Return Codes
SQL Server stored procedures can return both output parameters and return codes to an application:
The arameters and return codes can be bound to ro ram variables in an ODBC a lication where the
SQLExecDirect(hstmt, "EXECUTE sp_helpdb 'pubs' ", SQL_NTS);
SQLExecDirect(hstmt, "{ call sp_helpdb ('pubs') }", SQL_NTS);
CREATE PROCEDURE odbcproc @oparm int OUTPUT AS
SELECT name FROM sysusers WHERE uid < 2
SELECT @oparm = 88
RETURN 99GO
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
28/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 28/66
application can reference them. For example, to execute the procedure above using the ODBC CALL escape
clause and bind the return code and output parameters to program variables:
SQL Server does not send back the values for the return code or output parameters until the end of all
result sets for the procedure. The program variables ProcRet and OParm do not hold the output values of
99 and 88 until SQLMoreResults returns SQL_NO_DATA_FOUND.
Text and Image Data
The SQL Server ODBC driver has a couple of optimizations for text and image column processing that
applications can use to improve performance.
Bound vs. Unbound Text and Image Columns
When using server cursors (see "Cursors"), the driver is optimized to not transmit the data for unboundtext or image columns at the time the row is fetched. The text or image data is not actually retrieved from
the server until the application issues SQLGetData for the column.
This optimization can be applied to applications so that no text or image data is displayed while a user is
scrolling up and down a cursor. After the user selects a row, the application can call SQLGetData to
retrieve the text or image data. This saves transmitting the text or image data for any of the rows the user
does not select and can save the transmission of very large amounts of data.
Logged vs. Nonlogged
An application can request that the driver not log text and image modifications:
This option should only be used for situations where the text or image data is not critical, and the data
owners are willing to trade data recovery for higher performance.
Data-At-Execution and Text and Image Columns
ODBC Data-At-Execution allows applications to work with extremely large amounts of data on bound
columns or parameters. When retrieving very large text or image columns, an application cannot simply
allocate a huge buffer, bind the column into the buffer, and fetch the row. When updating very large text or
SQLRETURN rcd;
DWORD ProcRet = 0, OParm = 0;
long cbProcRet = 0, cbOParm = 0;
// Bind the return code.rcd = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT,
SQL_C_SLONG, SQL_INTEGER, 0, 0, &ProcRet, 0, &cbProcRet);
// Bind the output parameter.
rcd = SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT,
SQL_C_SLONG, SQL_INTEGER, 0, 0, &OParm, 0, &cbOParm;
// First ? marks the return code,
// second ? marks the output parameter.
rcd = (SQLExecDirect(hstmt, "{? = call odbcproc(?)}", SQL_NTS;
SQLSetStmtOption(hstmt,
SQL_SOPT_SS_TEXTPTR_LOGGING,
SQL_TL_OFF);
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
29/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 29/66
mage co umns, t e app cat on cannot s mp y a ocate a uge u er, n t to a parameter mar er n an
SQL statement, and then execute the statement. Whenever the size of the text or image data exceeds 400K
(64K with SQL Server 4.21a), the application must use SQLGetData or SQLPutData with their Data-At-
Execution options. Applications should always use Data-At-Execution if there is any possibility that the size
of the data will exceed these limits.
Data-At-Execution is described in the ODBC 2.0 Programmer's Reference; however, it remains one of the
hardest parts of the ODBC API for an application programmer to learn. The Appendix of this paper contains
the source code of two Win32 console applications, Getimage.c and Putimage.c, that illustrate using Data-At-Execution to read and write large amounts ofimage data. Text columns would use similar calls, the only
difference would be binding between SQL_C_CHAR and SQL_LONGVARCHAR instead of SQL_C_BINARY and
SQL_LONGVARBINARY. Programmers interested in working with text or image columns should look up the
Data-At-Execution index entries of the ODBC 2.0 Programmer's Reference, then search for "text" and "image"
in Microsoft SQL Server Programming ODBC for SQL Server.
Querying Metadata
This section discusses some common issues when getting metadata and catalog information from the
driver.
Instcat.sql
Both the SQL Server system catalog stored procedures and the ODBC API catalog functions address the
need of applications to retrieve catalog information from a database. Because there is a high correlation
between the ODBC catalog functions and the SQL Server catalog stored procedures, the SQL Server ODBC
driver implements many of the ODBC API catalog functions as calls to a corresponding SQL Server catalog
procedure. The driver is therefore dependent on the catalog stored procedures in any SQL Server to which
it connects.
Each version of the SQL Server ODBC driver is developed in conjunction with a specific version of
SQL Server. The proper operation of each driver version requires the versions of the catalog stored
procedures associated with the specific version of SQL Server with which the driver was developed, or a
later version of the procedures. For example, the 2.50.0121 driver was developed in conjunction with
Microsoft SQL Server version 6.0, and requires either the versions of the system catalog stored procedures
that were released with SQL Server 6.0, or with later vers ions, such as 6.5. The driver does not work
properly with older versions of the catalog stored procedures, such as those in SQL Server version 4.21a.
If a driver attempts to connect to a SQL Server running an older version of the catalog stored procedures
than those required by the driver, the connection completes with SQL_SUCCESS_WITH_INFO and a call to
SQLError returns the following message:
Although the connection is successful, the application may later encounter errors on calls to the ODBC API
catalog functions.
Sites running multiple versions of the driver against a server need to ensure that the server is running with
at least the version of Instcat.s l associated with the newest ODBC driver that will connect to it. For exam le,
SqlState: 01000
pfNative: 0
szErrorMsg: "[Microsoft][ODBC SQL Server Driver]The ODBC
catalog stored procedures installed on server
My421Server are version 02.00.4127; version 06.00.0115
or later is required to ensure proper operation.
Please contact your system administrator."
-
7/29/2019 Using ODBC with Microsoft SQL Server.pdf
30/66
14/05/13 Using ODBC with Microsoft SQL Server
msdn.microsoft.com/en-us/library/ms811006(d=printer).aspx 30/66
a site running multiple version 6.0 servers could buy SQL Server version 6.5 and upgrade some clients to
use the new 2.65.0201 driver that comes with version 6.5. The site would also need to run the 6.5 version of
Instcat.sql against the 6.0 servers before the new driver can connect to them.
Installing a newer version of Instcat.sql into an older server does not break any existing applications
connecting to that server, even ones still using the old drivers. It simply allows the applications using the
new driver to operate correctly.
Sites should run the Instcat.sql script at the server command prompt by using the isql utility.
For more information about determining the version of Instcat.sql currently applied to a server, see
Microsoft Knowledge Base article Q137636. For more information about the isql utility, see the Microsoft
SQL Server Transact-SQL Reference.
Multiple Active Statements per Connection
Starting with SQL Server6.5 and its associated driver, users can have multiple outstanding calls for
metadata. In SQL Server 6.5, the catalog