MS SQL OLE DB vs. MS SQL Native Client

[deadsea]

Hello Folks:

Long time lurker here...first time poster, although I usually found my answers here quite easily so never had a need to post :)

I am trying to understand what are the pros and cons of connecting to a MS SQL Server 2008 and/or 2012 database using MS OLE DB connection vs. MS SQL Server 2012 Native client in C10.2.1. I have scoured the net and only found blanket statements that say "Always use native driver...." without any explaination or evidence.

Instead of randomly using one over the other, I thought I will check with the gurus here. Perhaps someone out in the community has experience with these two drivers and can provide me their valuable feedback?

Thanks.

[bdbits]

Short answer - OLE DB is being deprecated. No more drivers after SQL Server 2012. Any questions? :D

OLE DB is a layer to abstract database interfaces (much like ODBC). As such, it has some overhead, so there is a (probably minor) performance hit. As an abstraction layer, it will introduce some differences in statement syntax over a pure native client interface. I suspect the OLE DB layer just ends up translating to and then running through the native interface anyway. So you really have not gained anything, particularly in a product like Cognos that is normally going to be generating your SQL for you anyway.

So to me, OLE DB is added complexity with little or no benefit for Cognos. I would not bother with it when you can just use the native client.

[deadsea]

Thanks for your response. Good to know that OLE is on its way out.

Hopefully we can have someone with experience between the two in terms of a performance benchmark.

[MFGF]

Thanks for your response. Good to know that OLE is on its way out.

Hopefully we can have someone with experience between the two in terms of a performance benchmark.

OLE DB hasn't progressed since SQL Server 2000 as far as I'm aware. At one point in the 90s it appeared to be Microsoft's connection type of the future (replacing ODBC) but they have moved things along since then. If you are connecting to SQL Server 2000, OLE DB is as close to a native client as you can get, and is certainly preferable to ODBC, but if you're on a later version of SQL Server, use the Native Client instead - it is more modern and more efficient.

All my own opinion, of course! :)

MF.

[beneuto]

Native Client is both ODBC and OLEDB providers bundled together with ODBC, OLEDB and ADO APIs.   Whilst ODBC and OLEDB by themselves are frozen at SQL 2000 level, the Native Client provides extended capabilities via the APIs hence why it is better to use Native Client.   There is no real performance difference between ODBC and OLEDB.   Individually they suit different needs rather than one being better than the other so I guess it made sense to put them together into a single Native Client package.

Only the OLEDB provider in the Native Client is being deprecated.   SQL Server will still be accessible using the OLEDB API via Native Client and I think standalone OLEDB too.   I guess Microsoft have figured out a way to get full functionality using the ODBC provider in Native Client only.

[Mfg]

Hello There,

Environments:
I have 2 environments. Audit DB has been configured. Type of DB
1. SQL Server
2. SQL Server

Audit data source:
1. MS SQL Server OLE DB
2. MS SQL Server 2008 Native Client

Audit Package:
1. Published in CQM
2. Published in CQM

Issue:
SQL Server function CharIndex
1. MS SQL Server OLE DB ( Working Fine )
2. MS SQL Server 2008 Native Client ( Throwing following error)

Expression Used:
SELECT SUBSTRING
(cogipf_package, CHARINDEX('''',cogipf_package) + 1,
CHARINDEX('''',cogipf_package, CHARINDEX('''',cogipf_package) + 1) - (CHARINDEX('''',cogipf_package) + 1) ) app_name
FROM cogipf_runreport COGIPF_RUNREPORT

Error:
RQP-DEF-0177
An error occurred while performing operation 'sqlOpenResult' status='-28'.
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft SQL Server Native Client 10.0]Invalid length parameter passed to the LEFT or SUBSTRING function.

Any suggestions :-[

thanks in advance.

[Mfg]

@bdbits , @MFGF, any help !! :(