The importance of selecting the correct Sql provider for the job. Sqlncli vs. Sqlncli10

Imagine this; you have been looking at the new data type DateTime2 in Sql Server 2008 because you have a need for the increased precision this data type offers.

Comparison here:

"SQL Server 2008 Books Online (October 2008) - datetime2 (Transact-SQL)"

  https://msdn.microsoft.com/en-us/library/bb677335.aspx

"SQL Server 2008 Books Online (October 2008) - datetime (Transact-SQL)"

  https://msdn.microsoft.com/en-us/library/ms187819.aspx

So you implement this data type, now, all of a sudden your .Net application starts to throw cast an exception, like so: “System.InvalidCastException: Specified cast is not valid.”

when you are retrieving the data in your code. Some clients work, some fails. What gives?

The error most likely comes from the fact that the working client is using the wrong provider for connecting to the database.

For example, you can use the sqlncli (Sql Native Client for Sql Server 2005) provider to connect to both Sql Server 2005 and 2008, and you can use the sqlncli10 (Sql Native Client for Sql Server 2008)

provider to connect to both Sql Server 2005 and 2008 as well.

As usual, a small repro will make it clearer what I am talking about.

In, Sql Server 2008, create a new table like so:

create table OrderTable(oid int identity, odate datetime2)

insert into OrderTable values ('2008-12-02')

insert into OrderTable values ('2008-12-04')

Create a new .Net console application that connects to this table and replace the Main method as follows:

            string[] providers = { "SQLNCLI10", "SQLNCLI" };

            foreach (string provider in providers)

            {

                String cs = String.Format(@"Provider={0};Data Source=<your 2008 server>;Integrated Security=SSPI;", provider);

                using (OleDbConnection con = new OleDbConnection(cs))

                {

                    Console.WriteLine("\n--- Using {0} ---", provider);

                    try

                    {

                        con.Open();

  OleDbCommand cmd = new OleDbCommand("SELECT oid, odate FROM OrderTable", con);

                        OleDbDataReader rdr = cmd.ExecuteReader();

                        while (rdr.Read())

                        {

                  int orderId = (int)rdr[0];

                            DateTime orderDate = (DateTime)rdr[1];

                            Console.WriteLine("Id: {0}, Date: {1}", orderId, orderDate.ToShortDateString());

                        }

                 con.Close();

                    }

                    catch (Exception ex)

                    {

                        Console.WriteLine(ex);

                    }

                }

            }

and run it, this will give the following:

--- Using SQLNCLI10 ---

Id: 1, Date: 2008-12-02

Id: 2, Date: 2008-12-04

--- Using SQLNCLI ---

System.InvalidCastException: Specified cast is not valid.

As explained earlier, the error comes from the fact that the wrong provider is used. What caught me a bit was that the new datetime2 was not ‘truncated’ to the lower precision datetime.

What is actually returned is a string, and therefore the cast to DateTime will fail. This can be easily seen. Replace the Main method above with the following:

            string[] providers = { "SQLNCLI10", "SQLNCLI" };

            foreach (string provider in providers)

            {

                String cs = String.Format(@"Provider={0};Data Source=<your 2008 server>;Integrated Security=SSPI;", provider);

                using (OleDbConnection con = new OleDbConnection(cs))

                {

                    Console.WriteLine("\n--- Using {0} ---", provider);

                    try

                    {

                        con.Open();

                        OleDbCommand cmd = new OleDbCommand("SELECT oid, odate FROM OrderTable", con);

                        OleDbDataReader rdr = cmd.ExecuteReader();

                        rdr.Read();

                        for (int i = 0; i < rdr.FieldCount; i++)

                        {

                            Console.WriteLine("ColName: {0, -10}, ColDataType: {1, -20}, GetType(): {2, -20}", rdr.GetName(i), rdr.GetDataTypeName(i), rdr[i].GetType());

                        }

                        rdr.Close();

                        con.Close();

                    }

                    catch (Exception ex)

                    {

                        Console.WriteLine(ex);

                    }

                }

            }

and run it, this will give the following, clearly showing that what we get back from the server is not a date data type, it is a varchar/string:

--- Using SQLNCLI10 ---

ColName: oid , ColDataType: DBTYPE_I4 , GetType(): System.Int32

ColName: odate , ColDataType: DBTYPE_DBTIMESTAMP , GetType(): System.DateTime

--- Using SQLNCLI ---

ColName: oid , ColDataType: DBTYPE_I4 , GetType(): System.Int32

ColName: odate , ColDataType: DBTYPE_WVARCHAR , GetType(): System.String

All this is actually explained here:

"SQL Server 2008 Books Online (October 2008) - New Date/Time Features with Previous SQL Server Versions (OLE DB)"

  https://msdn.microsoft.com/en-us/library/bb677283.aspx

What happens is that the client bits actually tells the server what version it is, the server then takes action depending on this.

This means that unchanged functionality executes the same independent on what client provider is used, but that newer functionality behaves different depending on the client provider being used.

How this works is a different subject and is better explained by someone with more Sql Core knowledge, so I’ll leave it at that.

To summarize, when you are using new functionality, data types, etc. Make sure that your clients are using the appropriate provider for the server version being used.

How to install and where to find the Sql Native Client providers:

“SQL Server 2008 Books Online (December 2008) -Installing SQL Server Native Client”

  https://msdn.microsoft.com/en-us/library/ms131321.aspx

“SQL Server 2005 Books Online (September 2007) - Installing SQL Native Client”

  https://msdn.microsoft.com/en-us/library/ms131321(SQL.90).aspx

You may have noticed that the code examples are using the OleDbConnection classes, however, the same thing goes for the SqlClient classes if you are running .Net 2.0 on a lower version than SP 1.

So when creating applications that utilizes Sql Server version specifics, make sure that your clients either have the appropriate .Net version (SqlClient) or that they use the appropriate Sql Native Client provider.