Latest OLEDB and ODBC drivers

Question

User-443210350 posted

I understand that Microsoft decided back in 2011 to deprecate OLE DB and that no new drivers or maintenance would occur beyond SQL Native Client V11. In future you should use ODBC based drivers - http://weblogs.sqlteam.com/dang/archive/2011/09/04/rip-ole-db.aspx

The latest ODBC driver being 'Microsoft ODBC Driver 13 for SQL Server' release on 25th July 2016 - https://www.microsoft.com/en-us/download/details.aspx?id=50420

ODBC drivers from Native Client V9,10,11 and the Microsoft ODBC Driver SQL Server V11 and 13 all work in Classic ASP code interacting with SQL server (I am using SQL 2012) except for one problem - nText and Nvarchar(max).

They just return blank - I have seen solutions saying you should read into a local variable first rather than addressing the recordset directly e.g. varStr=rs("LargeText"), but that does not work for me. And there is other mentions of using get chunk etc.

But I am quite happily using Native Client V9 (Provider=SQLNCLI) which works perfectly with these data types.

So, my questions are:

Is there anyway to get ODBC drivers working with nText/Nvarchar(max) datatypes?

Is there any benefit to using ODBC over OLEDB?

Do I have to upgade to ODBC at some stage in order to connect to SQL server in future i.e. SQL 2014/2016?

In other words, can I just carry on using OLEDB going forward?

Answer 1

User690216013 posted

Classic ASP itself was decided to be dead for even a longer time, so I am not surprised to see newer technologies from Microsoft fail to support it (as when they designed the new things they wouldn't even consider inter-operation with classic ASP at all).

You should be aware of the fact that although classic ASP is still supported by Microsoft (aka https://support.microsoft.com/en-hk/kb/2669020), the level of "support" is limited. When more and more old components of Windows are dying and replaced by other technologies, your application will become more and more fragile long before the end of life day.

Your top priority at this stage, is not "from OLEDB to ODBC", but to migrate away from classic ASP itself.

Answer 2

User1278090636 posted

Hi,

Is there anyway to get ODBC drivers working with nText/Nvarchar(max) datatypes?

Please take a look at  the following link, it may help.

http://blog.sqlauthority.com/2012/12/04/sql-server-fix-error-402-the-data-types-ntext-and-varchar-are-incompatible-in-the-equal-to-operator/

Is there any benefit to using ODBC over OLEDB?

You can find the difference of ODBC and OLEDB here : http://stackoverflow.com/questions/103167/what-is-the-difference-between-ole-db-and-odbc-data-sources

Do I have to upgade to ODBC at some stage in order to connect to SQL server in future i.e. SQL 2014/2016?

Just mention that the ntext type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them.

https://msdn.microsoft.com/en-us/library/ms187993(v=sql.110).aspx

Best Regards,

Jean