OLE DB provider or ODBC driver ?

[Guest_imported]

Hi,

I use Enterprise Manager (SQL Server 7) and I create local packages (Data Transformation Services) to import data from text files into SQL tables.

My question is : what connection should I use ? What is the best solution between 'Microsoft OLE DB Provider for SQL Server' and 'SQL Server ODBC Driver' ? (I am not quite sure these are different things...)

Thank you very much.
Cheers,
Ollivier2 from Paris...

 

[foxdev]

I'm sure others will chime in if I get this wrong, but I believe OLE DB is a higher-level (and more modern) wrapper around ODBC. OLE DB supports more data types and has some enhanced functionality, though it is not always faster than ODBC. -----------------
Robert Bradley
use coupon code "TEKTIPS" for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com

 

[Guest_imported]

Thank you for your answer but I've just read somewhere :

" OLE DB, the data integration element of Microsoft's Enterprise OLE strategy, uses ODBC to access data from relational data sources. "

So in my local package (DTS) - in which I need a connection to access SQL Server tables - does it make any difference if choose 'OLE DB' or 'OBDC driver' ?

T.h.a.n.k.s
Ollivier2

 

[moonshadow]

There is a difference. You must use ODBC for SQL Server to connect to SQL Server 6.5 and below. I expect that using OLEDB against SQL 7 and above would generally be faster. I got caught out last week when a DTS package was using the generic ODBC driver (not specific to SQL) and refused to transfer tables with TEXT fields. All was fine when I used ODBC for SQL Server. I expect this is the sort of functional difference Foxdev was referring to that you'd also get using OLEDB. If you have a choice I'd use OLEDB.