An example of using ODBC and SQL Server 2008 table-valued parameters

I'll be at TechEd and ITForum in Barcelona in November, doing sessions on SQL Server 2005 and SQL Server 2008. One of the TechEd sessions is called "To ODBC or to OLE DB?" and is a discussion of using ODBC and OLE DB is applications these days, whether one or the other is "better", if you should convert existing applications, etc. On that note…

Back at TechEd US, I'd spoken with Chris Lee, who's in charge of SQL Native Client. That's the OLE DB provider and ODBC driver that ship with SQL Server. SQL Server 2008 has a new version of the provider and driver, and when I'd asked if support for the new features (like the new DATE/TIME data types and table-valued parameters) Chris had not only replied "yes", but given me a demo of using table-valued parameters from ODBC to show off and post if I wanted to.

Here's the code. To build and run it:

1. Use the Visual C++ compiler that comes with Visual Studio 2005 or Visual Studio 2008 beta. I used VS2008 Beta 2.
2. Convert the project if needed. I ignored the warnings about 1 source file not being converted.
3. Make sure that sqlncli.h and sqlncli10.lib are available to the compiler. They're in C:\Program Files\Microsoft SQL Server\100\SDK\Includes and Lib, respectively.
4. Install SQLNCLI from the SQL Server 2008 CTP distibution on the client machine. Just run the SQLNCLI.msi in Servers\Setup.
5. Setup an ODBC System DSN from Control Panel/ODBC Administrator named TVPDemo. It must use the SQL Server Native Client 10.0 ODBC driver. Use the database of your choice, the demo will run DDL to create the database objects you need.
6. Compile the demo, set breakpoints and walk through the code

Cheers! See you in Barcelona!


ODBC TVP Sample (1008.79 KB)

4 thoughts on “An example of using ODBC and SQL Server 2008 table-valued parameters

  1. Thanks for such a informative article.
    But I am not able to figure out one thing please help me to understand it.
    What is the difference between "provider vs driver" (like OLE DB provider and ODBC driver )

    happy blogging

  2. Hi Dhanajay,

    It’s just terminology for the component that connects a particular data source to a particular API. Different APIs use different names, as in:
    ODBC, JDBC – driver
    OLE DB – provider
    ADO.NET – data provider

    But, what’s in a name? Same concept

  3. Hi Rickard,

    As far as I can see, for writing your own code against an existing driver/provider, you’re missing nothing. OLE DB had a concept of "service provider" (a set of services you get on top of any base provider for free), but few of these service providers were ever built. Please do come to the discussion and share your experiences. See you there.


Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.