none
SQL Native Client and ODBC RRS feed

  • Question

  • ,Hello

    .I understand that Microsoft decided to deprecate OLE DB 
    SQL Server Native Client (SNAC) will not be supported beyond SQL Server 2012. Microsoft advises to avoid using SNAC in new development work, and to modify applications that currently use it. 
    We have many legacy applications which work with ADO and Borland CodeGear 2007 C ++ Builder.
    Currently we use an OLE DB SQLNCLI11. This provider   works perfectly for us.
    My question is ,when will this support dissapear permanently and what (the best) alternative do we have? 

    Thank you,



    Victor Rodniansky



    Monday, December 5, 2016 10:49 AM

Answers

  • My question is ,when will this support dissapear permanently and what (the best) alternative do we have? 

    I can't speak to when support for OLE DB connectivity to SQL Server will disappear but it appears its death will be gradual. As with any deprecation announcement, you should avoid the feature for new development and plan to move applications to supported technologies.

    ODBC is the preferred SQL Server API from native code going forward. The latest ODBC driver is available as a free download and separate install . Since your existing app uses ADO, switching to ODBC might be as simple as a installing the ODBC driver and a connection string change (e.g. "Provider=MSDASQL;Driver={ODBC Driver 13 for SQL Server};Server=YourServer;Database=YourDatabase;Trusted_Connection=Yes"). ADO will use the MSDASQL OLE DB provider (Microsoft OLE DB Provider for ODBC Drivers) to access ODBC data sources.

    A heavier lift would be to switch from using ADO to using the ODBC call-level interface directly, which would provide the highest performance but at the cost of code complexity.  I know nothing about Borland CodeGear 2007 C ++ Builder but perhaps it includes a friendly OO interface for ODBC similar to ADO.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com



    Monday, December 5, 2016 11:45 AM

All replies

  • Hello Victor,

    OleDB for SQL Server is deprecated and for new development ODBC should be used instead. But SNAC isn't deprecated, it's still base for ODBC and .NET Clients. See Data Access Technologies Road Map for more details


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, December 5, 2016 11:32 AM
  • My question is ,when will this support dissapear permanently and what (the best) alternative do we have? 

    I can't speak to when support for OLE DB connectivity to SQL Server will disappear but it appears its death will be gradual. As with any deprecation announcement, you should avoid the feature for new development and plan to move applications to supported technologies.

    ODBC is the preferred SQL Server API from native code going forward. The latest ODBC driver is available as a free download and separate install . Since your existing app uses ADO, switching to ODBC might be as simple as a installing the ODBC driver and a connection string change (e.g. "Provider=MSDASQL;Driver={ODBC Driver 13 for SQL Server};Server=YourServer;Database=YourDatabase;Trusted_Connection=Yes"). ADO will use the MSDASQL OLE DB provider (Microsoft OLE DB Provider for ODBC Drivers) to access ODBC data sources.

    A heavier lift would be to switch from using ADO to using the ODBC call-level interface directly, which would provide the highest performance but at the cost of code complexity.  I know nothing about Borland CodeGear 2007 C ++ Builder but perhaps it includes a friendly OO interface for ODBC similar to ADO.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com



    Monday, December 5, 2016 11:45 AM
  • ,Hi

    Maybe you can help me again

    I get <"Data provider or other service returned an E_FAIL status">, error message when I fetch (SELECT) any record , which has a column (nvarchar(MAX)) with a data length that happens to be greater than 132 (WHERE LEN(ContentSummary) > 132).

    The ContentSummary column is NVARCHAR(MAX). If the length is less than 132 , everything works well. Also everything worked well when we used only the SQL Server Native client 11.0 OLE DB Provider (SQLNCLI11). This is my ConnectionString: String Con_String = "Provider=MSDASQL.1;Driver={SQL Server Native Client 11.0};Server=.;Persist Security Info=False;User ID=%s;Password=%s;Database=%s;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False";

    Thank you




    Victor Rodniansky

    Wednesday, December 7, 2016 8:55 AM
  • ADO is unaware of the SQL Server data types introduced after SQL Server 2000.  I thought you would need to specify the DataTypeCompatibility keyword in the connection string for this to work with SQLNCLI but it seems that is not the case.

    I ran a quick ADO test using both the OLE DB and ODBC driver of SQL Server Native Client 11.0 and see differences in the way nvarchar(MAX) is handled by MSDASQL and ADO. So it seems you will be stuck in deprecated land with ADO if you use newer data types, requiring use of the SQL Server Native Client OLE DB driver. Sorry I don't have a better solution.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, December 7, 2016 1:47 PM
  • Thank you

    Victor Rodniansky

    Wednesday, December 7, 2016 1:54 PM
  • There is rich information about the Supported status of OLE DB at: https://docs.microsoft.com/sql/connect/connect-history

    Tuesday, April 30, 2019 4:06 AM