Freigeben über

Getting started with SQL Native Client

  • Artikel

Getting started with SQL Native Client [Chris Lee]

[This was originally posted on the DataWorks blog which is now suspended]

As Acey Bunch explained in April, SQL Native Client meets the needs of developers wanting to take advantage of new features in SQL Server 2005 from ADO, ODBC and OLE DB applications. For those of you who haven’t looked at SQL Native Client yet we now need to start the education process of how to use it. The good news is that it’s very simple. We have implemented a very small number of new interfaces for OleDb, but most new features are implemented via connection or statement attributes, which you already know how to use.

I’ll start at a very basic level and talk about how to convert existing applications to use SQL Native Client. This comes in three stages: first getting existing code running; second, preparing to exploit new features; third, using new features. In this post I’ll deal with the first two of these. The third will be covered in my next post, using Multiple Active Result Sets (MARS) as an example. If you have SQL Server 2005 installed on your machine, SQL Native Client is already installed. If not, SQLNCLI.msi is included with the SQL Server 2005 distribution, but isn’t copied when SQL Server 2005 is installed, so just copy it from the distribution in the \Setup folder.

Stage 1:Getting existing code running
For ADO you change your connection string to use SQLNCLI as the provider, and add a keyword to enable SQL Server 2000 data type compatibility, so
“ …;Provider=SQLOLEDB;…”
becomes
“…;Provider=SQLNCLI;DataTypeCompatibility=80;…”
ADO is a generic data access API that is now part of the Windows Platform and is not part of SQL Native Client, so the ADO on your machine did not change when you installed SQL Native Client. Therefore, it doesn’t have any specific knowledge of SQL Server in general, much less the new datatypes introduced for SQL Server 2005. For this reason, when using ADO we have to tell SQL Native Client to map new SQL Server 2005 data types to data types that ADO does understand. I’ll explain this in a later article, for now accept that we need this, and that it doesn’t get in the way of using other new features of SQL Server 2005.

For ODBC you change the driver name from ‘SQL Server’ to ‘SQL Native Client’. If your application uses a DSN you need to create a new DSN and select ‘SQL Native Client’ as the driver. If you use DSN-less connections just update the connection string in your application.

For OLE DB you simply change the provider name from ‘SQLOLEDB’ to ‘SQLNCLI’, or use CLSID_SQLNCLI instead of CLSID_SQLOLEDB.

Stage 2: Preparing to use new featuresFor ADO:
If you’re using ADO, you’re already good to go.

ODBC and OLE DB applications need to use sqlncli.h to gain access to new features. You also need to be using Visual Studio.Net. Sqlncli.h is a new common header file for both ODBC and OLE DB and is typically installed to C:\Program Files\Microsoft SQL Server\90\SDK\Include.

For ODBC sqlncli.h is a straight replacement for odbcss.h. If you’re using bcp API calls alongside ODBC calls then you need to link with sqlncli.lib instead of odbcbcp.lib.

For OLE DB you can add the #include for sqlncli.h after the #include for sqloledb.h if you need to use both old and new providers (sqlncli.h doesn’t contain the CLSIDs for SQLOLEDB), or you can replace the #include for sqloledb.h with the #include for sqlncli.h if you don’t need the old CLSIDs. If you need both headers, the #include for sqloledb.h must come first.

Since sqlncli contains symbols for both ODBC and OLE DB, there’s a chance you may get a name clash between one of your own symbols and a symbol defined for use by the ‘other’ API (the one you’re not going to be using). In this case you can add a #define to get rid of the symbols for the API you don’t need. If you’re using OLE DB you #define _SQLNCLI_OLEDB_ and if you’re using ODBC you #define _SQLNCLI_ODBC_.

Next steps …
You’re now prepared to start using the new features of SQL Server 2005 available with SQL Native Client. Consult SQL Native Client Programming in SQL Server Programming Refernce in Books Online for details of these features. The documentation for SQL Native Client has been updated quite a lot recently, so you need to use the latest build available to you. Most of the new features are very simple to program and are controlled by connection or statement properties. In my next post I’ll take a look at Multiple Active Result Sets (MARS).

Chris Lee
Program Manager, DataWorks

Comments

  • Anonymous
    January 20, 2009
    Thanks for the information.  This information helped us to  upgrade drivers to Native client  for an Legacy ASP application. Regards, Saravanakumar

  • Anonymous
    May 31, 2009
    PingBack from http://outdoorceilingfansite.info/story.php?id=22736