Migrate ODBC Provider to OLEDB Provider | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Migrate ODBC Provider to OLEDB Provider

Hello, I need some help ‘selling’ the idea to management that we should be using an the SQL OLEDB provider instead of the current ODBC one. The current web application (classic ASP) stores a single ODBC based connection string in a shared config file. All connections are created a via a common component using ADO so changes to considerable number of pages should be avoidable even if some code in the way the connection is created needs to change. The main concern is the behaviour of SET NOCOUNT OFF. With a the SQL OLEDB provider this can result in two result sets being returned the first simply contain a number of row affected. This of course will break lots of things and must be avoided. Is there something I put in the connection string or assign to the ADODB.Connection properies collection which will ensure that NOCOUNT ON is SET by default on the connection? Or will it take a roundtrip to the server to ensure that this and other session properties are configured before returning the connection to the caller? Where can I find a comparision of the performance between the two that can bolster my case? Does connection pooling vary between the two in any way that is advantageous? Thanks for your help, Anthony.
Hi Anthony, Please refer to link below for pros/cons with reference to your question. http://ftp.sas.com/techsup/download/v8papers/odbcdb.pdf Are you facing any performance issue with your current site or are you planning to implement some feature require you to change ODBC technology to OLEDB? 1-
As a developer myself, i will not take a risk to change my production site to change DB connectivity (unless i get real problems). 2-
Or i will not mind to change if there are only 2-3 pages which will be effected by this change, otherwise it will require planning, testing (as we are talking production site). 3-
Or i am re-developing site. Hope this will help. Waqar. ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~
]]>