Data Access blog : SQL Native Client

Quick update re: Data Access blog.

alexbarn — Wed, 12 Jul 2006 00:57:00 GMT

We're putting this blog (Data Access blog) into suspended animation. That doesn't mean we will stop blogging about ADO.NET and data access stuff, or that we'll take down published posts or comments here - it's just means we'll carry on blogging about ADO.NET (current and future releases including info on ADO.NET Entity Framework, EDM, LINQ to Entities, etc) at our shiny new ADO.NET blog and blog about other data access technology related posts at our other team blogs (see below).

If you are subscribed to this blog's feed (there are quite a few of you!), please update you reader / aggregator so it points to the new ADO.NET blog's RSS feed or Atom feed. We can't automagically redirect you, so sorry for the hassle here.

Comments on this blogs.msdn/com/dataaccess blog will be closed, but we have re-published this blog's June and July posts over to the new blog, so you can comment there if you like.

In case you are wondering, here's run down of the various blogs run by the Data Programmability teams:

The Data Programmability team blog (more about data access in general, rather than about a specific technology)
SQL Protocols team blog (discussions related to Microsoft's SQL Server Protocols - Netlibs, TDS and (new for SQL 2005) SOAP. Topics include connections and SQL connectivity)
XML team blog (covers XQuery, XPath, SQLXML, XML Editor, XSLT, XSD, XLinq (LINQ to XML) and more XML goodness)
ADO.NET team blog (ADO.NET and System.Data namepace, posts on current and future releases including info on ADO.NET Entity Framework, EDM, LINQ to Entities, etc)

Also, check out the MSDN Data Access and MSDN XML Developer Centers.

Hope to see you there!

Developers! - Express contest - win $10K

alexbarn — Tue, 14 Mar 2006 21:45:00 GMT

A new worldwide contest for developers has been launched by the folks at Visual Studio Express and SQL Server Express.

Here's how it goes: you think of something cool using Visual Studio Express and SQL Server Express. It could be desktop app, a web app, a mobile app, whatever. Then you submit your cool idea here.

Once the finalists are picked, then you build. The top prize is $10,000 (USD) in cash, another winner will win $1,000 in cash, and all 12 finalists who complete their project will receive $250 in Amazon gift certificates.

There are three judges. Microsoft's Robert Scoble, MAKE's Philip Torrone and a 'Mystery Judge'.

Here's the contest site....good luck!

10 Mistakes Developers Make With Databases

alexbarn — Tue, 07 Mar 2006 02:01:00 GMT

A new article published at Developer.com is worth checking out: Ten of the Biggest Mistakes Developers Make With Databases.

Using MARS with SQL Native Client [Chris Lee]

DataWorksBlog — Wed, 03 Aug 2005 01:53:00 GMT

As stated in my previous post, the majority of new features available through SQL Native Client are accessed by connection or statement properties. MARS is about the simplest new feature to use in SQL Server 2005, and possibly the one we’ve all been waiting for the longest, so it’s a good candidate for your first excursion into the new features available with SQL Native Client.

MARS lets an application perform multiple operations on a single connection even if there are default result sets open on the connection. Prior to MARS, ADO and SQLOLEDB applications would (under default conditions) use implicit multiple connections and ODBC applications would get ‘connection busy’ errors.

Pre-MARS behavior
Let’s look at MARS using ADO for simplicity (I’ll mention how to enable MARS with ODBC or OLE DB at the end of this post, but don’t skip there just yet as most of what follows is still going to be useful to you even if you’re not using ADO). Try executing the following code (which assumes you have a SQL Server instance called SQL2K5 with the AdventureWorks demo database available on your local machine). This is just ‘nonsense’ code that just opens a default result set and then opens another result set using the same query multiple times, to make a point. The extension of the point it makes to your own applications shouldn’t be hard to grasp.

    Dim i As Integer
    Dim cn As ADODB.Connection
    Dim qryCustomers1 As ADODB.Command
    Dim qryCustomers2 As ADODB.Command
    Dim rsCustomers1 As ADODB.Recordset
    Dim rsCustomers2 As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.Open ("Provider=sqlncli;Data Source=.\SQL2K5" & _
             ";Database=AdventureWorks;Integrated Security=SSPI;")
    Set qryCustomers1 = New ADODB.Command
    Set qryCustomers1.ActiveConnection = cn
    qryCustomers1.CommandText = "SELECT CustomerID" & _
                       " FROM Sales.Customer" & _
                       " WHERE CustomerID=1" & _
                       " ORDER BY CustomerID"
    Set qryCustomers2 = New ADODB.Command
    Set qryCustomers2.ActiveConnection = cn
    qryCustomers2.CommandText = qryCustomers1.CommandText

    Set rsCustomers1 = qryCustomers1.Execute
    For i = 1 To 3
        Set rsCustomers2 = qryCustomers2.Execute
        rsCustomers2.Close
    Next i
    rsCustomers1.Close
    cn.Close

If you take a default SQL Server Profiler trace you’ll see something like this:

The second result set is using a new connection each time it is opened. This clearly has some overhead (and it turns out that the additional connections aren’t pooled, so the overhead is for a full server connection network protocol exchange each time). This is the default behavior for SQLOLEDB and SQL Native Client (OLE DB) – a new implicit connection is spawned when the main connection is busy with a default result set. You can prevent multiple implicit connections being spawned by changing the connection lines to

cn.Open ("Provider=sqlncli;Data Source=.\SQL2K5;Database=AdventureWorks;Integrated Security=SSPI;")
cn.Properties("Multiple Connections") = False

The effect of this is as follows. This is similar to ODBC where multiple implicit connections aren’t used and the application gets a ‘connection busy’ error.

Behavior with MARS
Now we’re ready for MARS. In addition to providing a great deal more convenience to the programmer, MARS can reduce server overhead too. Change the connection string to: cn.Open ("Provider=sqlncli;Data Source=.\SQL2K5;MARS Connection=True;Database=AdventureWorks;Integrated Security=SSPI;")

And the code works again, but now the Profiler trace looks like this:

Is MARS all good news, or is there any downside? Well, mostly MARS is good news. The downsides are twofold. First, existing code optimized to run in the non-MARS world may show a slight performance dip when run un-modified with MARS. Default result sets make very efficient use of network resources and adding the MARS capability incurs some additional network overhead. The message here is “if it ain’t broke don’t fix it”, or – as with any feature – you need to design your code to make best use of MARS.

This brings us to the second caveat when using MARS – don’t try to get too smart. With MARS you can send multiple multi-statement batches to the server. The server will interleave execution of such batches, which means that if the batches change server state via SET or USE statements, for example, or use TSQL transaction management statements (BEGIN TRAN, COMMIT, ROLLBACK), both you and the server can get confused about what your actual intent is. The outcome may be an error, or it may be that the application doesn’t behave as you expected it to. It is because of these potential difficulties when MARS is ‘over used’, that we decided to turn it off by default rather than have it enabled by default, as it was for Beta 2.

Without MARS you can only execute one multi-statement batch at a time, which results in completely predictable performance. Stay with this model with MARS – but of course with the relaxation that you can execute multiple single statement queries and have their result sets pending at the point at which you execute your multi-statement batch – and you’ll stay out of trouble. This model is all that most people want and expect to do with MARS anyway.

It’s likely that the first thing you’ll want to try with MARS is convert server cursors to default result sets. Is this the right thing to do? If the result set is fairly small, or will be consumed in its entirety fairly quickly and you don’t require locking on rows in the result set and you haven’t optimized your code to use block fetches, then conversion to a default result set with MARS will be beneficial. If you’re not going to process all the rows in a result set, or the result set is very large and will not be read quickly, or you need locking or scrolling, then you should stay with a server cursor.

There are some other considerations you need to take into account. Firstly, you can’t begin a transaction when you have a default result set open. Secondly, if you commit or rollback a transaction any open default result sets become unavailable (attempts to use them result in errors). These restrictions are the same whether you use MARS or not, although the error messages are different in some cases. If you need ‘Preserve On Commit’ behavior, then you must use a server cursor.

Using MARS from ODBC and OLE DB
To use MARS with ODBC the connection string addition is “MARS_Connection=yes”, or you can a connection attribute as follows:
SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED,SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);
before calling SQLDriverConnect or SQLBrowseConnect. With OLE DB, the provider string addition for IDBInitialize::Initialize is “MarsConn=yes”, or “MARS Connection=true” if you use an initialization string with IDataInitialize::GetDataSource. You can also enable MARS programmatically as follows:

IDBInitialize *pIDBInitialize = NULL;
IDBProperties *pIDBProperties = NULL;
// Create the data source object.
hr = CoCreateInstance(CLSID_SQLNCLI, NULL, CLSCTX_INPROC_SERVER,IID_IDBInitialize, (void**)&pIDBInitialize);
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void**)&pIDBProperties);

// Set the MARS property.
DBPROP rgPropMARS;
IDBProperties rgPropMARS.dwPropertyID = SSPROP_INIT_MARSCONNECTION;
rgPropMARS.dwOptions = DBPROPOPTIONS_REQUIRED;
rgPropMARS.dwStatus = DBPROPSTATUS_OK;
rgPropMARS.colid = DB_NULLID;
V_VT(&(rgPropMARS.vValue)) = VT_BOOL;
V_BOOL(&(rgPropMARS.vValue)) = VARIANT_TRUE;

// Create the structure containing the properties.
DBPROPSET PropSet;
PropSet.rgProperties = &rgPropMARS;
PropSet.cProperties = 1;
PropSet.guidPropertySet = DBPROPSET_SQLSERVERDBINIT;

// Get an IDBProperties pointer and set the initialization properties.
pIDBProperties->SetProperties(1, &PropSet);
pIDBProperties->Release();
// Initialize the data source object.
hr = pIDBInitialize->Initialize();

Conclusion
MARS is a great new feature that fosters a more natural programming style when working with SQL Server 2005, especially if you’ve ever worked with other databases. With ADO and OLE DB your applications will probably require fewer server connections. MARS will be problem free when used sensibly, but you still need to give some thought to the choice between default result sets and server cursors, although you have more flexibility than before.

Chris Lee
Program Manager, DataWorks
Disclaimer: This posting is provided "AS IS" with no warranties, and confers

Getting started with SQL Native Client [Chris Lee]

DataWorksBlog — Sat, 30 Jul 2005 00:31:00 GMT

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 features
For 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
Disclaimer: This posting is provided "AS IS" with no warranties, and confers