one ado connection object - multiple spids?

Hi,
We have asp application (ado connection) which connects to the sql server
2000 (sp3). There is one asp page which sequentially executes set of 6
stored procedures. When I execute same set of stored procedures in query
analyzer, I get response in less than 1 second.When application does that,
response gets back in 15-20 seconds (only when we have problem, otherwise in
2-3 seconds). In the trace, I noticed that each of these 6 stored procedures
(which btw use 1 connection object) gets a different SPID. Why is that?
Shouldn't they use the same SPID, if they use the same connection object
(executed sequentially!)?
Also, the trace shows that each of them gets executed almost instantly
(1ms), but between end of previous, and beginning of the next one, there is
delay of 2-3 seconds. During these 2-3 seconds, there is nothing going on on
the database server (very few events).
Does anybody have an idea?
ThanksPedja wrote:
> Hi,
> We have asp application (ado connection) which connects to the sql
> server 2000 (sp3). There is one asp page which sequentially executes
> set of 6 stored procedures. When I execute same set of stored
> procedures in query analyzer, I get response in less than 1
> second.When application does that, response gets back in 15-20
> seconds (only when we have problem, otherwise in 2-3 seconds). In the
> trace, I noticed that each of these 6 stored procedures (which btw
> use 1 connection object) gets a different SPID. Why is that?
> Shouldn't they use the same SPID, if they use the same connection
> object (executed sequentially!)? Also, the trace shows that each of them
> gets executed almost instantly
> (1ms), but between end of previous, and beginning of the next one,
> there is delay of 2-3 seconds. During these 2-3 seconds, there is
> nothing going on on the database server (very few events).
> Does anybody have an idea?
> Thanks
Are you sure you are not closing the connection and opening it up each time
your ASP code executed some SQL?
David Gugick
Quest Softwaresql

OLEDB connection to a remote SQL Server Database - Servername

I want to establish a ADO OLEDB connection to a SQL Server 2K at a webserver
with a reference to the server name instead of IP-adress. Just like :
db.Properties("Data Source").Value = "http://testserver.com"
Do I need to specify the standard port '1433' ( as is the case with IP
adressing) in the connection string ?
OscarI Have the same problem (under W2003 Server)
"Oscar" <oku@.xs4all.nl> a crit dans le message de
news:40575beb$0$126$e4fe514c@.dreader10.news.xs4all.nl...
> I want to establish a ADO OLEDB connection to a SQL Server 2K at a
webserver
> with a reference to the server name instead of IP-adress. Just like :
> db.Properties("Data Source").Value = "http://testserver.com"
> Do I need to specify the standard port '1433' ( as is the case with IP
> adressing) in the connection string ?
> Oscar
>

OLE DB VS ADO.NET

I think this is a question you could find on every .NET related forum.

But I'm going to throw it: Which connection type should I use? ADO .NET or OLE DB

I know in the past ADO was just a layer on top of OLE DB so it was better to use OLE DB directly. Nowadays, with ADO .NET, that isn't the case anymore or am I totally wrong?

The reason why I'm asking this is because I'm considering to switch from OLE DB connection to ADO .NET because I have nothing but troubles with parameter mappings and wrong values inserted in the database because of data type conversions. When I use ADO.NET, everything works fine.

What about performance? A fable or the truth? In my testing environment I work with small datasets but in production this won't be the case.

Is there a good article somewhere out there on this subject?

Thanks in advance,
Tom

Tom,

I'm currently in a training session for SSIS, and this very question has be asked by one of our Senior Database developers. I is inclined to use ADO.Net because it is explicit with the named variables. Our instructor then pointed out that if the variable name changes in the stored procedure then you break your package. He also suggested that if you abstract your variables to a table, then you could avoid that issue.

My question is the performance between the two. Is ADO.Net just a wrapper for OLE DB? Or is it an independent environment?

Brandon Forest

Database Administrator

Data & Web Services

Sutter Connect IT

foresb@.sutterhealth.org

|||

Hi Brandon,

I've read a lot about this and nobody seems to know the answer.

Performance isn't the issue apparently.

Allthough Microsoft pushes developers towards ADO.NET (code examples on MSDN usually use ADO.NET), I for myself am tended to use OLE DB, this because of the simple fact they seem to have forgotten about ADO.NET in SSIS designer (OLE DB Source component - but nothing like ADO.NET Source Component)

Greets,

Tom

|||[Microsoft follow-up]|||

There isn't a single answer that's correct in all cases. As others have pointed out, there are differences between ADO.Net and OLE DB that lead to slightly different strengths and weaknesses, but performance is much more strongly determined by the provider than the interface. I.e., two different OLE DB or two different ADO.Net providers for the same data source can demonstrate significantly different performance in only slightly different scenarios.

So, if you really want a meaningful answer, you have to ask, "Should I be using OLE DB or ADO.Net in this particular scenario?" The best way to answer that question is with some prototyping.

|||

ADO.NET vs OLEDB:

I'll try to answer this question by slicing it into 4 main areas:

1. support across SSIS components:

a. SSIS is not welcoming ADO.NET and OLE DB connectors equally across the board. Although this is more so in SQL Server 2005, we are making steps to close the gap in Katmai in between the two. For instance, there is no ADO.NET destination component in Data Flow, and the Lookup component only supports OLE DB. OLE DB is by far the most supported connectivity option across the board. For a full list of supported connectivity options and SSIS components, see my article on the subject: http://ssis.wik.is/Connectivity_Libraries

2. performance

a. Performance is very much dependent on the computing environment and the data and network load levels in your scenario. We highly recommend slicing your end-end scenario into isolated sub scenarios to see where the performance bottleneck is. RowCount component comes particularly handy where you can replace source and destination components with. By replacing the connectors with the RowCount component, which does not incur any I/O and frees memory as an inline component, you are indeed collecting benchmark performance numbers with and without the connector. This will give you the time the connector spends reading/writing the data.

b. That being said, OLE DB, for most cases will have better performance than ADO.NET. This is due to ADO.NET being a managed fa?ade, and providing more abstraction with a little more performance overhead.

3. 64bit considerations

a. Since ADO.NET is managed, the deployment of packages and moving them around 32bit and 64bit boxes will be easier compared to the experience with OLE DB providers. SSIS design time is a 32bit application because of the dependency on Visual Studio, and has to work with 32bit connectors. However, during runtime, you have the option to choose a native 64bit or a 32bit connector. Please read my article on 64bit and why things are the way they are here: http://ssis.wik.is/64-bit_Story

b. Some OLE DB providers are 32bit only (i.e. Office connectivity, JET and ACE providers), whereas others support both architectures (i.e. Oracle’s own OLE DB provider for Oracle). To work in a seamless 64bit environment, make sure your provider has both binaries.

c. For an OLE DB provider to be available only in 32bit (i.e. JET), does not necessarily mean that you cannot run it on 64bit machines. You can, but it will be in WOW64 emulation mode. The only downside to that is if you need the 64bit address space of huge memory (i.e. greater than 4GB). Otherwise, it’s not a problem.

4. target data source & data type supportability

a. Connectors, whether they are ADO.NET or OLE DB providers, don’t support a specific target database/application system equally. For instance, the OLE DB providers for DB2 are all different in the way they support different flavors of DB2. Some support DB2 on Linux, others support DB2 on AS/400, etc…For a good list of connectors and what actually they support, please see the table we are compiling in our connectivity wiki : http://ssis.wik.is/Data_Sources

b. Another key support question is whether these connectors support a specific version of the target database and how good they are in dealing with a recently introduced data type. For instance, Microsoft’s Oracle OLE DB provider was not recently updated to fully support new data types in Oracle 10g +…However, Microsoft’s ADO.NET provider for Oracle does a better job with Oracle data types. Again, our connectivity wiki is a good place to start and contribute to this very organic structure of the connectivity realm.

We also have a white paper on connectivity which covers other aspects of this comparison, it’s a good read: http://ssis.wik.is/Connectivity_White_Paper

Hope this helps,

Deniz

|||Thanks Deniz.

Phil|||

Many thanks to all of you

-Tom

OLE DB VS ADO.NET

I think this is a question you could find on every .NET related forum.

But I'm going to throw it: Which connection type should I use? ADO .NET or OLE DB

I know in the past ADO was just a layer on top of OLE DB so it was better to use OLE DB directly. Nowadays, with ADO .NET, that isn't the case anymore or am I totally wrong?

The reason why I'm asking this is because I'm considering to switch from OLE DB connection to ADO .NET because I have nothing but troubles with parameter mappings and wrong values inserted in the database because of data type conversions. When I use ADO.NET, everything works fine.

What about performance? A fable or the truth? In my testing environment I work with small datasets but in production this won't be the case.

Is there a good article somewhere out there on this subject?

Thanks in advance,
Tom

Tom,

I'm currently in a training session for SSIS, and this very question has be asked by one of our Senior Database developers. I is inclined to use ADO.Net because it is explicit with the named variables. Our instructor then pointed out that if the variable name changes in the stored procedure then you break your package. He also suggested that if you abstract your variables to a table, then you could avoid that issue.

My question is the performance between the two. Is ADO.Net just a wrapper for OLE DB? Or is it an independent environment?

Brandon Forest

Database Administrator

Data & Web Services

Sutter Connect IT

foresb@.sutterhealth.org

|||

Hi Brandon,

I've read a lot about this and nobody seems to know the answer.

Performance isn't the issue apparently.

Allthough Microsoft pushes developers towards ADO.NET (code examples on MSDN usually use ADO.NET), I for myself am tended to use OLE DB, this because of the simple fact they seem to have forgotten about ADO.NET in SSIS designer (OLE DB Source component - but nothing like ADO.NET Source Component)

Greets,

Tom

|||[Microsoft follow-up]|||

There isn't a single answer that's correct in all cases. As others have pointed out, there are differences between ADO.Net and OLE DB that lead to slightly different strengths and weaknesses, but performance is much more strongly determined by the provider than the interface. I.e., two different OLE DB or two different ADO.Net providers for the same data source can demonstrate significantly different performance in only slightly different scenarios.

So, if you really want a meaningful answer, you have to ask, "Should I be using OLE DB or ADO.Net in this particular scenario?" The best way to answer that question is with some prototyping.

|||

ADO.NET vs OLEDB:

I'll try to answer this question by slicing it into 4 main areas:

1. support across SSIS components:

a. SSIS is not welcoming ADO.NET and OLE DB connectors equally across the board. Although this is more so in SQL Server 2005, we are making steps to close the gap in Katmai in between the two. For instance, there is no ADO.NET destination component in Data Flow, and the Lookup component only supports OLE DB. OLE DB is by far the most supported connectivity option across the board. For a full list of supported connectivity options and SSIS components, see my article on the subject: http://ssis.wik.is/Connectivity_Libraries

2. performance

a. Performance is very much dependent on the computing environment and the data and network load levels in your scenario. We highly recommend slicing your end-end scenario into isolated sub scenarios to see where the performance bottleneck is. RowCount component comes particularly handy where you can replace source and destination components with. By replacing the connectors with the RowCount component, which does not incur any I/O and frees memory as an inline component, you are indeed collecting benchmark performance numbers with and without the connector. This will give you the time the connector spends reading/writing the data.

b. That being said, OLE DB, for most cases will have better performance than ADO.NET. This is due to ADO.NET being a managed fa?ade, and providing more abstraction with a little more performance overhead.

3. 64bit considerations

a. Since ADO.NET is managed, the deployment of packages and moving them around 32bit and 64bit boxes will be easier compared to the experience with OLE DB providers. SSIS design time is a 32bit application because of the dependency on Visual Studio, and has to work with 32bit connectors. However, during runtime, you have the option to choose a native 64bit or a 32bit connector. Please read my article on 64bit and why things are the way they are here: http://ssis.wik.is/64-bit_Story

b. Some OLE DB providers are 32bit only (i.e. Office connectivity, JET and ACE providers), whereas others support both architectures (i.e. Oracle’s own OLE DB provider for Oracle). To work in a seamless 64bit environment, make sure your provider has both binaries.

c. For an OLE DB provider to be available only in 32bit (i.e. JET), does not necessarily mean that you cannot run it on 64bit machines. You can, but it will be in WOW64 emulation mode. The only downside to that is if you need the 64bit address space of huge memory (i.e. greater than 4GB). Otherwise, it’s not a problem.

4. target data source & data type supportability

a. Connectors, whether they are ADO.NET or OLE DB providers, don’t support a specific target database/application system equally. For instance, the OLE DB providers for DB2 are all different in the way they support different flavors of DB2. Some support DB2 on Linux, others support DB2 on AS/400, etc…For a good list of connectors and what actually they support, please see the table we are compiling in our connectivity wiki : http://ssis.wik.is/Data_Sources

b. Another key support question is whether these connectors support a specific version of the target database and how good they are in dealing with a recently introduced data type. For instance, Microsoft’s Oracle OLE DB provider was not recently updated to fully support new data types in Oracle 10g +…However, Microsoft’s ADO.NET provider for Oracle does a better job with Oracle data types. Again, our connectivity wiki is a good place to start and contribute to this very organic structure of the connectivity realm.

We also have a white paper on connectivity which covers other aspects of this comparison, it’s a good read: http://ssis.wik.is/Connectivity_White_Paper

Hope this helps,

Deniz

|||Thanks Deniz.

Phil|||

Many thanks to all of you

-Tom

OLE DB VS ADO.NET

I think this is a question you could find on every .NET related forum.

But I'm going to throw it: Which connection type should I use? ADO .NET or OLE DB

I know in the past ADO was just a layer on top of OLE DB so it was better to use OLE DB directly. Nowadays, with ADO .NET, that isn't the case anymore or am I totally wrong?

The reason why I'm asking this is because I'm considering to switch from OLE DB connection to ADO .NET because I have nothing but troubles with parameter mappings and wrong values inserted in the database because of data type conversions. When I use ADO.NET, everything works fine.

What about performance? A fable or the truth? In my testing environment I work with small datasets but in production this won't be the case.

Is there a good article somewhere out there on this subject?

Thanks in advance,
Tom

Tom,

I'm currently in a training session for SSIS, and this very question has be asked by one of our Senior Database developers. I is inclined to use ADO.Net because it is explicit with the named variables. Our instructor then pointed out that if the variable name changes in the stored procedure then you break your package. He also suggested that if you abstract your variables to a table, then you could avoid that issue.

My question is the performance between the two. Is ADO.Net just a wrapper for OLE DB? Or is it an independent environment?

Brandon Forest

Database Administrator

Data & Web Services

Sutter Connect IT

foresb@.sutterhealth.org

|||

Hi Brandon,

I've read a lot about this and nobody seems to know the answer.

Performance isn't the issue apparently.

Allthough Microsoft pushes developers towards ADO.NET (code examples on MSDN usually use ADO.NET), I for myself am tended to use OLE DB, this because of the simple fact they seem to have forgotten about ADO.NET in SSIS designer (OLE DB Source component - but nothing like ADO.NET Source Component)

Greets,

Tom

|||[Microsoft follow-up]|||

There isn't a single answer that's correct in all cases. As others have pointed out, there are differences between ADO.Net and OLE DB that lead to slightly different strengths and weaknesses, but performance is much more strongly determined by the provider than the interface. I.e., two different OLE DB or two different ADO.Net providers for the same data source can demonstrate significantly different performance in only slightly different scenarios.

So, if you really want a meaningful answer, you have to ask, "Should I be using OLE DB or ADO.Net in this particular scenario?" The best way to answer that question is with some prototyping.

|||

ADO.NET vs OLEDB:

I'll try to answer this question by slicing it into 4 main areas:

1. support across SSIS components:

a. SSIS is not welcoming ADO.NET and OLE DB connectors equally across the board. Although this is more so in SQL Server 2005, we are making steps to close the gap in Katmai in between the two. For instance, there is no ADO.NET destination component in Data Flow, and the Lookup component only supports OLE DB. OLE DB is by far the most supported connectivity option across the board. For a full list of supported connectivity options and SSIS components, see my article on the subject: http://ssis.wik.is/Connectivity_Libraries

2. performance

a. Performance is very much dependent on the computing environment and the data and network load levels in your scenario. We highly recommend slicing your end-end scenario into isolated sub scenarios to see where the performance bottleneck is. RowCount component comes particularly handy where you can replace source and destination components with. By replacing the connectors with the RowCount component, which does not incur any I/O and frees memory as an inline component, you are indeed collecting benchmark performance numbers with and without the connector. This will give you the time the connector spends reading/writing the data.

b. That being said, OLE DB, for most cases will have better performance than ADO.NET. This is due to ADO.NET being a managed fa?ade, and providing more abstraction with a little more performance overhead.

3. 64bit considerations

a. Since ADO.NET is managed, the deployment of packages and moving them around 32bit and 64bit boxes will be easier compared to the experience with OLE DB providers. SSIS design time is a 32bit application because of the dependency on Visual Studio, and has to work with 32bit connectors. However, during runtime, you have the option to choose a native 64bit or a 32bit connector. Please read my article on 64bit and why things are the way they are here: http://ssis.wik.is/64-bit_Story

b. Some OLE DB providers are 32bit only (i.e. Office connectivity, JET and ACE providers), whereas others support both architectures (i.e. Oracle’s own OLE DB provider for Oracle). To work in a seamless 64bit environment, make sure your provider has both binaries.

c. For an OLE DB provider to be available only in 32bit (i.e. JET), does not necessarily mean that you cannot run it on 64bit machines. You can, but it will be in WOW64 emulation mode. The only downside to that is if you need the 64bit address space of huge memory (i.e. greater than 4GB). Otherwise, it’s not a problem.

4. target data source & data type supportability

a. Connectors, whether they are ADO.NET or OLE DB providers, don’t support a specific target database/application system equally. For instance, the OLE DB providers for DB2 are all different in the way they support different flavors of DB2. Some support DB2 on Linux, others support DB2 on AS/400, etc…For a good list of connectors and what actually they support, please see the table we are compiling in our connectivity wiki : http://ssis.wik.is/Data_Sources

b. Another key support question is whether these connectors support a specific version of the target database and how good they are in dealing with a recently introduced data type. For instance, Microsoft’s Oracle OLE DB provider was not recently updated to fully support new data types in Oracle 10g +…However, Microsoft’s ADO.NET provider for Oracle does a better job with Oracle data types. Again, our connectivity wiki is a good place to start and contribute to this very organic structure of the connectivity realm.

We also have a white paper on connectivity which covers other aspects of this comparison, it’s a good read: http://ssis.wik.is/Connectivity_White_Paper

Hope this helps,

Deniz

|||Thanks Deniz.

Phil|||

Many thanks to all of you

-Tom

OLE DB VS ADO.NET

I think this is a question you could find on every .NET related forum.

But I'm going to throw it: Which connection type should I use? ADO .NET or OLE DB

I know in the past ADO was just a layer on top of OLE DB so it was better to use OLE DB directly. Nowadays, with ADO .NET, that isn't the case anymore or am I totally wrong?

The reason why I'm asking this is because I'm considering to switch from OLE DB connection to ADO .NET because I have nothing but troubles with parameter mappings and wrong values inserted in the database because of data type conversions. When I use ADO.NET, everything works fine.

What about performance? A fable or the truth? In my testing environment I work with small datasets but in production this won't be the case.

Is there a good article somewhere out there on this subject?

Thanks in advance,
Tom

Tom,

I'm currently in a training session for SSIS, and this very question has be asked by one of our Senior Database developers. I is inclined to use ADO.Net because it is explicit with the named variables. Our instructor then pointed out that if the variable name changes in the stored procedure then you break your package. He also suggested that if you abstract your variables to a table, then you could avoid that issue.

My question is the performance between the two. Is ADO.Net just a wrapper for OLE DB? Or is it an independent environment?

Brandon Forest

Database Administrator

Data & Web Services

Sutter Connect IT

foresb@.sutterhealth.org

|||

Hi Brandon,

I've read a lot about this and nobody seems to know the answer.

Performance isn't the issue apparently.

Allthough Microsoft pushes developers towards ADO.NET (code examples on MSDN usually use ADO.NET), I for myself am tended to use OLE DB, this because of the simple fact they seem to have forgotten about ADO.NET in SSIS designer (OLE DB Source component - but nothing like ADO.NET Source Component)

Greets,

Tom

|||[Microsoft follow-up]|||

There isn't a single answer that's correct in all cases. As others have pointed out, there are differences between ADO.Net and OLE DB that lead to slightly different strengths and weaknesses, but performance is much more strongly determined by the provider than the interface. I.e., two different OLE DB or two different ADO.Net providers for the same data source can demonstrate significantly different performance in only slightly different scenarios.

So, if you really want a meaningful answer, you have to ask, "Should I be using OLE DB or ADO.Net in this particular scenario?" The best way to answer that question is with some prototyping.

|||

ADO.NET vs OLEDB:

I'll try to answer this question by slicing it into 4 main areas:

1. support across SSIS components:

a. SSIS is not welcoming ADO.NET and OLE DB connectors equally across the board. Although this is more so in SQL Server 2005, we are making steps to close the gap in Katmai in between the two. For instance, there is no ADO.NET destination component in Data Flow, and the Lookup component only supports OLE DB. OLE DB is by far the most supported connectivity option across the board. For a full list of supported connectivity options and SSIS components, see my article on the subject: http://ssis.wik.is/Connectivity_Libraries

2. performance

a. Performance is very much dependent on the computing environment and the data and network load levels in your scenario. We highly recommend slicing your end-end scenario into isolated sub scenarios to see where the performance bottleneck is. RowCount component comes particularly handy where you can replace source and destination components with. By replacing the connectors with the RowCount component, which does not incur any I/O and frees memory as an inline component, you are indeed collecting benchmark performance numbers with and without the connector. This will give you the time the connector spends reading/writing the data.

b. That being said, OLE DB, for most cases will have better performance than ADO.NET. This is due to ADO.NET being a managed fa?ade, and providing more abstraction with a little more performance overhead.

3. 64bit considerations

a. Since ADO.NET is managed, the deployment of packages and moving them around 32bit and 64bit boxes will be easier compared to the experience with OLE DB providers. SSIS design time is a 32bit application because of the dependency on Visual Studio, and has to work with 32bit connectors. However, during runtime, you have the option to choose a native 64bit or a 32bit connector. Please read my article on 64bit and why things are the way they are here: http://ssis.wik.is/64-bit_Story

b. Some OLE DB providers are 32bit only (i.e. Office connectivity, JET and ACE providers), whereas others support both architectures (i.e. Oracle’s own OLE DB provider for Oracle). To work in a seamless 64bit environment, make sure your provider has both binaries.

c. For an OLE DB provider to be available only in 32bit (i.e. JET), does not necessarily mean that you cannot run it on 64bit machines. You can, but it will be in WOW64 emulation mode. The only downside to that is if you need the 64bit address space of huge memory (i.e. greater than 4GB). Otherwise, it’s not a problem.

4. target data source & data type supportability

a. Connectors, whether they are ADO.NET or OLE DB providers, don’t support a specific target database/application system equally. For instance, the OLE DB providers for DB2 are all different in the way they support different flavors of DB2. Some support DB2 on Linux, others support DB2 on AS/400, etc…For a good list of connectors and what actually they support, please see the table we are compiling in our connectivity wiki : http://ssis.wik.is/Data_Sources

b. Another key support question is whether these connectors support a specific version of the target database and how good they are in dealing with a recently introduced data type. For instance, Microsoft’s Oracle OLE DB provider was not recently updated to fully support new data types in Oracle 10g +…However, Microsoft’s ADO.NET provider for Oracle does a better job with Oracle data types. Again, our connectivity wiki is a good place to start and contribute to this very organic structure of the connectivity realm.

We also have a white paper on connectivity which covers other aspects of this comparison, it’s a good read: http://ssis.wik.is/Connectivity_White_Paper

Hope this helps,

Deniz

|||Thanks Deniz.

Phil|||

Many thanks to all of you

-Tom

OLE DB or ADO? plz give me a suggestion!

i am learn database access with native vc++, so ADO.NET is not electable for me.

i wonder whether i should study OLE DB first or ADO.

i've been working with ADO under VB for some time, it's very simple to use. but i dont think it's the best choice for vc++, i think it's OLE DB. but OLE DB is really difficult, too many interfaces, and properties!!!
could u give a suggestion? should i learn ADO with vc++ first and then OLE DB, or sould i learn OLE DB directly?

I would not suggest using ADO, use OLE DB if you're trying to stay native (since ADO has been superceded by ADO.NET). You could also use the C libraries that many database vendors supply.

Is there a reason you're using native C++ database calls, and not something like ADO.NET? Just curious,

Josh Lindenmuth|||thank for your suggestion! i use native C++ because i like it, and i am a student, i am learning native VC++. ADO.NET is great, it does a lot for you. but i want to learn more about the system.

the biggest problem i am facing now is about the data conversion and binding, i can find much material about this subject on MSDN. and the Property Set too, almost every call need a set of properties, but no body tell why i need them. and it seems that many calls can work with NULL property set!!!

is there any nice book for teaching OLE DB?|||

I still say if you're going to use OLEDb, use it through ADO.NET (there's an OLEDB data provider). You'll develop code faster, and it will probably perform better as well. Otherwise, I don't see much use ... if you're going to use native C++ I'd still suggest using ODBC or one of the database specific C libraries that many vendors provide.

Here are two older books on the subject of ODBC and OLEDB:
http://www.amazon.com/gp/product/0764533088/102-6516636-3196933?v=glance&n=283155&v=glance

http://www.amazon.com/gp/product/1572316128/102-6516636-3196933?v=glance&n=283155&s=books&v=glance

Hope this helps,
Josh Lindenmuth

|||thanks!!
one more question!
if i want to code a lightweight COM component, should i use ATL+ODBC or ATL+OLE DB ?|||

You should also look at ODBC. It is a simpler API with fewer calls and hence is easier to learn than OLE DB. OLE DB would be a good choice if you plan to become a COM expert, otherwise ODBC may be your best bet. For most tasks you will write less code with ODBC than you would with OLE DB.

|||yes, i want to be a COM expert! but the OLE DB interfaces are really difficult for starters, however, ATL make it much easier to use! i think i can use those wrapped OLE DB interfaces first!

OLE DB - ADO .NET

Does anybody know why in some cases you can use both connection types, in others you can't. Like for example, you start a data flow task with a Datareader Source which uses an ADO .NET connection manager, next you want to do a lookup and seems like the only type of connections you can use in a lookup table is the OLE DB.

So OLE DB is placed hard coded in a lookup task, and I thought things changed since the coming of OOP ...

I think ole db is ok

just when i use script component,i use ado.net

|||Your observation is correct -- Lookup currently only supports OLE DB connections. We hope to add ADO.NET connection support in the future.|||Ok