?
Solved

vba ado provider to sql server -- ole and native client

Posted on 2016-08-04
11
Medium Priority
?
162 Views
Last Modified: 2016-08-11
i haven't tested the performance recently.. but I want to throw this question out there to get perspective.

I have an Excel/vba app that runs reports off of a sql server warehouse in a corporate environment, using windows authentication.  

I'm using an ADO connection to the db, and have alternated between using the OLE (SQLOLEDB) and the native client (SQLNCLI10) provider.

It's worked fine with both providers for 6 years, and I haven't noticed a significant performance difference, although I haven't been scientific in benchmarking them.

But now some people have been upgraded from version 10 to version 11, and now it doesn't work for them, since it is version specific.  

That will be a pain to maintain, and I'll have to come up with a creative way to try more than one and turn off  On Error.  

I know that OLE has been depreciated, but it seems to me it has the significant advantage of not being version specific in the connection string.

Is there a way to use the native client that is not version specific?  And is it truly better than using OLE?  When will OLE be phased out?

Thanks...
0
Comment
Question by:dougfosterNYC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 

Author Comment

by:dougfosterNYC
ID: 41742553
Oh, and we are migrating to a new server environment.  So the legacy is sql server 2008, and the new one is version 2012.  I'm not sure if that is going to make any difference...
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 500 total points
ID: 41743690
Hi,

the SQLOLEDB driver is a more "common" driver, the SQLNCLI driver supports more specific features of each version of SQL Server. I would say, if you do not use specific new datatypes of newer SQL Server versions you will not have a problem using SQLOLEDB. SQLNCLI supports ways of downloading data faster than other drivers but as far as I know that is not the case if it is used by ADO, but here I'm not completely sure.

I would always prefer to use SQLNCLI if possible, the only case when I'm forced to use SQLOLEDB is for recordsets to be assigned to an Access form to keep it updatable. In any other case I use SQLNCLI.

Details about connection strings for SQLNCLI you can find here:
http://www.connectionstrings.com/sql-server/

But there is no "upgrade" in case of SQLNCLI: All versions can be installed at the same time (I use SQLNCLI for SQL Server 2005 and 2008 in parallel on the clients), so you can use any version you want on the same machine. If you plan to use a newer SQL Server, why not let all clients install the SQLNCLI11 driver and update your connection string?

Cheers,

Christian
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 1000 total points
ID: 41743762
SqlNCli increases overall speed. If it is significant and worth the version-dependant connection string hassle is a question of the environment. Sadly there is indeed no way to always use the newest available and installed Native Client, you need to provide the release (10 or 11), and exactly that driver has to be installed.

We are currently switching from OleDB to SqlNCli on a broad base for our customers, and I've implemented a registry check to switch to OleDB if the Native Driver is not installed, by manipulating the connection string.
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41743981
SQLNCLI is deprecated since MSSQL 2014. MSSQL ODBC 11 should be used instead.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 41744059
MS is leaning towards ODBC 11. OLE DB is "depricated", not the Native Client - there will be no newer release than 2012 for it, but I wouldn't tell that "depricated".
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41744093
Avoiding to make a dissertation of the "deprecated" meaning but want I wanted to state is to avoid using Native Client for MSSQL 2014 or superior. Newer versions of MSSQL ODBC has been released so don't stick with SNAC 2012 if you want to work with last SQL Server versions.
0
 

Author Comment

by:dougfosterNYC
ID: 41744145
Thanks all for your comments.  It looks like there is no clarity on this issue, but for now, there is no pressing reason to drop OLE.  But i think I'll add logic to find the newest version of the native client... i'm hoping that IT is rolling it out for all of us so we'll all be on version 11 soon....  

The reason i can't have users upgrade is because i work at a large company and our environments are completely dictated by IT.  We can't install anything on our machines (many of which are virtual)....
0
 

Author Comment

by:dougfosterNYC
ID: 41745478
excellent conversation
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41751916
Doug, I'm not sure if you're expecting any comment from our part or you're done with this question.
0
 

Author Comment

by:dougfosterNYC
ID: 41751944
Ok, thanks.  I thought i closed this out but clearly it didn't take. I'm not sure what happened... I'll do it again.
0
 

Author Closing Comment

by:dougfosterNYC
ID: 41751950
thanks, excellent help.  Sorry i didn't close this out before... I tried but didn't see this portion of the process...
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question