Share via

Native Data Access for SQL Server 2005 FAQ

  • Article

Native Data Access for SQL Server 2005 FAQ

In SQL Server 2005 Microsoft decided to change the way it supports ODBC and OLE DB with the introduction of Microsoft SQL Server Native Client (SQL Native Client), a new component of SQL Server 2005. This FAQ answers some basic questions about SQL Native Client and about ODBC and OLE DB in general.

What is SQL Native Client?

Microsoft SQL Server Native Client (SQL Native Client) is a single native dynamic link library (DLL) that contains the ODBC driver and OLE DB provider which together support applications using native-code APIs (ODBC, OLE DB and ADO) to access Microsoft SQL Server. SQL Native Client is a component of SQL Server 2005 and is serviced in SQL Server 2005 service packs. It is also available for download from MSDN (https://msdn.microsoft.com/data/ref/sqlnative/default.aspx). In SQL Native Client, everything (APIs, TDS routines and netlibs) is packaged into a single DLL.

Previously ODBC and OLE DB support for SQL Server was provided via Microsoft Data Access Components (MDAC - https://msdn.microsoft.com/data/ref/mdac/). MDAC is now fully integrated into Windows and is available only in Windows releases and service packs – there will be no more standalone MDAC downloads on MSDN. MDAC continues to provide core data access functionality such as the ODBC Driver Manager and OLE DB Core Services, but the ODBC and OLE DB support for SQL Server is frozen at the level of functionality provided by SQL Server 2000.

Note that SQL Native Client supports SQL Server releases from SQL Server 7.0 onwards, so its use is not restricted to SQL Server 2005. However, new features such as MARS (https://msdn2.microsoft.com/en-us/library/ms131686.aspx) are only available when connected to SQL Server 2005.

Where do I find out more about SQL Native Client?

You can find information about SQL Native Client in Books Online and also on MDSN (https://msdn.microsoft.com/data/learning/sqlnative/).

For anything you can’t find there you can use the SQL Server Data Access forum (https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1)

Must I upgrade clients to use SQL Native Client as soon as I upgrade my server?

Applications deployed before SQL Server 2005 was released can and should continue to use MDAC.

Applications must upgrade to take full advantage of the new features and data types introduced in SQL Server 2005 (https://msdn2.microsoft.com/en-us/library/ms131456.aspx ). When an application is upgraded, it should be thoroughly tested since there are some differences in behavior between MDAC and SQL Native Client (https://msdn2.microsoft.com/en-us/library/ms131035.aspx ). The vast majority of applications will not be affected by these changes, but this does not remove the responsibility to test before deployment.

Should I rewrite my native API applications to use ADO.Net and managed code?

In most cases the old adage “If it isn’t broken, don’t fix it” applies. The .Net platform has many compelling attributes and will be the right choice for new development in most cases, but this does not mean that it is the right choice in all cases. There are valid reasons for making other choices in some case: Visual Studio 2005 and C++/CLI (https://msdn.microsoft.com/msdnmag/issues/04/05/VisualC2005/)makes re-use of existing native data access code within a .Net deployment infrastructure a more viable option than ever before; there are some scenarios where native code and native APIs provide the best solution; Microsoft now has its own high quality JDBC driver for SQL Server 2005(https://msdn.microsoft.com/data/ref/jdbc/default.aspx); also, Native XML Web Services in SQL Server 2005 (https://msdn2.microsoft.com/en-us/library/ms191274.aspx ) provide an additional means of exposing database-dependent services in service oriented architectures.

Isn’t ODBC is on the way out? If you have to use a native API isn’t OLE DB better?

ODBC and OLE DB are supported equally in SQL Native Client. Neither is better, they service different requirements and communities. There is no need to convert from one to the other unless there are other compelling business reasons. While there was a time when Microsoft expected OLE DB to supersede ODBC that time has passed and we now recognize that each API has its place.

OLE DB is a good choice for COM applications and has broader functionality than ODBC in some areas. It is used extensively within SQL Server itself. However, it is quite complex and more difficult to program for basic operations than ODBC.

ODBC may have been out of the limelight in recent years, but Microsoft anticipates strong and continued interest in this API going forward. ODBC has a huge following across the entire software industry and will therefore remain an essential component of SQL Server to meet requirements for standards alignment and interoperability. When migrating applications from other databases to SQL Server, where companies choose to re-use existing code and applications rather than make a fresh start and re-develop from scratch, ODBC will be the natural choice in most cases.

There is little difference in performance between OLE DB and ODBC – application architecture is likely to have a bigger impact on performance than choice of API for well-written applications that follow best practice guidelines.

Isn't ODBC just a ‘wrapper’ API and inherently inefficient?

This may be true for some ODBC drivers, but not for SQL Native Client. ODBC in SQL Native Client and also in MDAC is a true native API for SQL Server.

What about DB-Library and Embedded SQL for C(ESQL/C)?

DB-Library and ESQL/C components are not included in SQL Server 2005 but connections from existing applications are supported. Microsoft has no plans for any new development work involving these APIs. Customers may continue to deploy existing applications, but development of new applications and extension of existing applications is not recommended. Continued support for DB-Library and ESQL/C is not guaranteed in future releases of SQL Server (https://msdn2.microsoft.com/en-us/library/ms143729.aspx ).

Microsoft is reviewing support for DB-Library and ESQL/C in future releases of SQL Server and welcomes your input as part of the review process.

Chris Lee
Program Manager, SQL Native Client

Comments

  • Anonymous
    October 11, 2006
    PingBack from http://blog.vbug.net/PermaLink.aspx?guid=bbb11a8c-4658-443b-ba7f-8ca9f2687cb7

  • Anonymous
    October 31, 2006
    PingBack from http://chaespot.com/mssql/2006/10/31/ace-your-sql-server-2005-mcts-exam/

  • Anonymous
    February 15, 2007
    Hi, We have connection to SS2005 from PhP using SQL Native Client. Issue is that some queries (for example, of "FOR XML" type) give unexpected result set. Thus, query select top 1 replace(replace((select 'abc' as [data] from pc for xml path('')), '<data>', ''),'</data>','') which must give abcabcabc... gives something like this РPbН°bН#P®Н€УТ€УТLИТИpbНШbН whereas using ODBC gives correct result set. Can you help me to clear the issue? One can see this odd result of above query at http://www.sql-ex.ru/exercises.php (click "enter without login" , then mark "without checking", paste the above query and click Run button). Additional information: The following query works: select top 1 cast( replace(replace((select 'abc' as [data] from pc for xml path('')), '<data>', ''),'</data>','') as varchar(8000)) Above without CAST does not work. Why? Also, select top 1 cast(replace(replace((select 'abc' as [data] from pc for xml path('')), '<data>', ''),'</data>','') as varchar(max)) does not work. Respectfully, Sergey Moiseenko

  • Anonymous
    February 21, 2007
    Sergey, The result of replace() is returned as nvarchar(max). nvarchar(max) has unlimited size and ODBC represents this by returning a column size of 0. This can confuse applications which don’t check for 0 as a special case and do buffer size arithmetic based on the column size returned by SQLDescribeCol or similar calls. I suspect the application is binding a buffer that only has space for the null terminator and is getting a truncation warning from ODBC as a consequence. When you cast the result to a fixed size varchar type SQLBindCol returns a column size matching the cast and the application then binds a buffer that is big enough for the result. Chris Lee

  • Anonymous
    May 15, 2007
    PingBack from http://chaespot.com/mssql/2007/05/16/microsoft-sql-server-2005-standard-5-cal-2/

  • Anonymous
    August 12, 2007
    PingBack from http://chaespot.com/mssql/2007/08/12/sql-server-magazine-sql-server-management-for-mobile-devices/

  • Anonymous
    October 30, 2007
    PingBack from http://discount-perfume-hq.com/?p=2004

  • Anonymous
    June 09, 2009
    PingBack from http://quickdietsite.info/story.php?id=4741

  • Anonymous
    June 19, 2009
    PingBack from http://edebtsettlementprogram.info/story.php?id=21916

  • Anonymous
    April 05, 2010
    I have to write a small DLL to access a SQL Server (calling some stored procedures) for an embedded environment. I already wrote a similar DLL for PostgreSQL and it was very easy to find the API (libpq http://www.postgresql.org/docs/8.1/static/libpq-exec.html#LIBPQ-EXEC-MAIN) and write the code... Why is this so difficult for MSSQL? Where do I get code samples for accessing MSSQL server from plain old C code. If impossible, is there something for C++ code? (I can always hide the C++ from the DLL users) Is it possible to access a remote MSSQL WITHOUT having to configure the ODBC source OUTSIDE your code. I mean just saying the DB server host, port, dbname and user/passwd? (Just as JDBC DOES, for example, give it a URL or connection string and be done with it) Where can I get some help, API docs on this?