Advantages OLEDB vs ODBC

Hi!
Could you give me a list of advantages using OLEDB in stead of ODBC? Is
there an idea of getting good informations how to handle OLEDB?
My problem is by using ODBC for a WebApplication the
system often is blocked. I hope the system gets
faster by using OLEDB. Is it like this?
Thanks,
Christian alias HansMoser
If you can not get your system to perform using ODBC, switching to OLEDB is
not going to help you. You better investigate why it does not perform first.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Hans Moser" <dont_answer@.gmx.de> wrote in message
news:4153eeea$0$14521$99d2ed3e@.news.highwayone.de. ..
> Hi!
> Could you give me a list of advantages using OLEDB in stead of ODBC? Is
> there an idea of getting good informations how to handle OLEDB?
> My problem is by using ODBC for a WebApplication the
> system often is blocked. I hope the system gets
> faster by using OLEDB. Is it like this?
> Thanks,
> Christian alias HansMoser
>
sql

ado-sql-oledb

Hi
Im using VB6,ADO, OLEDB and SQL Server 2000/2005
On any SQL error the error number from Error is -2147221504
How to get the "real" error number to specifiy error
For example if "Time Out" happens then error is 10021 or something
aso.
regards;
Mex
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uIz8nYfGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
Enumerate the ADODB.Connection.Errors Collection
http://support.microsoft.com/kb/168336
http://support.microsoft.com/kb/168354
hth
-ralph
|||http://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx
|||Meelis Lilbok wrote:
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
This may help:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ado-sql-oledb

Hi
Im using VB6,ADO, OLEDB and SQL Server 2000/2005
On any SQL error the error number from Error is -2147221504
How to get the "real" error number to specifiy error
For example if "Time Out" happens then error is 10021 or something
aso.
regards;
Mex"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uIz8nYfGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
Enumerate the ADODB.Connection.Errors Collection
http://support.microsoft.com/kb/168336
http://support.microsoft.com/kb/168354
hth
-ralph|||http://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx|||Meelis Lilbok wrote:
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
This may help:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ado-sql-oledb

Hi
Im using VB6,ADO, OLEDB and SQL Server 2000/2005
On any SQL error the error number from Error is -2147221504
How to get the "real" error number to specifiy error
For example if "Time Out" happens then error is 10021 or something
aso.
regards;
Mex"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uIz8nYfGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
Enumerate the ADODB.Connection.Errors Collection
http://support.microsoft.com/kb/168336
http://support.microsoft.com/kb/168354
hth
-ralph|||http://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx|||Meelis Lilbok wrote:
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
This may help:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ADO.NET or OLEDB connection/recordset?

My package needs to be a High Performance (target: 150,000 rows in 30 minutes) ETL solution. We are using all MS technologies - SSIS, SQL 2005, BIDS, etc. I need to loop the recordset executed by a Stored Proc in a Execute SQL Task in a Source Script Component.

If I use an ADO.NET Connection Manager, here is the code in the Source Script Component Public Overrides Sub CreateNewOutputRows()

Code 1

Dim sqlAdapter As New SqlDataAdapter

Dim dataRow As Data.DataRow

Dim ds As DataSet = CType(Me.Variables.rsSomeResultset, DataSet)

sqlAdapter.Fill(ds)

Iget: Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.

Code 2

Dim oledbAdapter As New OleDb.OleDbDataAdapter

Dim dataTable As DataTable

oledbAdapter.Fill(dataTable, Me.Variables.rsSomeResultset)

Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb

It works all right when I use an OLEDB Connection Manager with the second code sample.

Question: In order to extract the maximum performance, wouldn't it be preferred to use ADO.NET with SqlClient Provider in an all SQL Server 2005 environment? Or will an OLEDB Connection provide comparable or equal performance?

If so, what code can I use? Since the recordset returned by the Stored Proc (in the Execute SQL Task) can only be captured in a System.Object variable and you can only use the overload of the Fill() method of the OleDbDataAdapter to accept an ADO Recordset or Record object.

There was a post recently that compared some of the connection types, but thanks to the lovely search functionality, I can't find it. If anyone else has it, please post it to this thread.

In general, I don't think you are going to see a significant performance difference between ADO.NET and OLEDB against SQL Server.

That being said, if you want to do some further research into the problem, try adding a message box to your script to display the type of the variable.

Code Snippet

System.Windows.Forms.MessageBox.Show(Me.Variables.rsSomeResultset.ToString())

|||You Code 1 snippet doesn't look right for ADO.NET. You shouldn't be trying to call sqlAdapter.Fill(ds). Try Dim dataTable as DataTable = ds.Tables(0) instead. I'd guess that OLE DB would be faster. Please post your findings.
|||

Do you mean this discussion?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1985034&SiteID=1

ADO.NET or OLEDB connection/recordset?

My package needs to be a High Performance (target: 150,000 rows in 30 minutes) ETL solution. We are using all MS technologies - SSIS, SQL 2005, BIDS, etc. I need to loop the recordset executed by a Stored Proc in a Execute SQL Task in a Source Script Component.

If I use an ADO.NET Connection Manager, here is the code in the Source Script Component Public Overrides Sub CreateNewOutputRows()

Code 1

Dim sqlAdapter As New SqlDataAdapter

Dim dataRow As Data.DataRow

Dim ds As DataSet = CType(Me.Variables.rsSomeResultset, DataSet)

sqlAdapter.Fill(ds)

Iget: Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.

Code 2

Dim oledbAdapter As New OleDb.OleDbDataAdapter

Dim dataTable As DataTable

oledbAdapter.Fill(dataTable, Me.Variables.rsSomeResultset)

Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb

It works all right when I use an OLEDB Connection Manager with the second code sample.

Question: In order to extract the maximum performance, wouldn't it be preferred to use ADO.NET with SqlClient Provider in an all SQL Server 2005 environment? Or will an OLEDB Connection provide comparable or equal performance?

If so, what code can I use? Since the recordset returned by the Stored Proc (in the Execute SQL Task) can only be captured in a System.Object variable and you can only use the overload of the Fill() method of the OleDbDataAdapter to accept an ADO Recordset or Record object.

There was a post recently that compared some of the connection types, but thanks to the lovely search functionality, I can't find it. If anyone else has it, please post it to this thread.

In general, I don't think you are going to see a significant performance difference between ADO.NET and OLEDB against SQL Server.

That being said, if you want to do some further research into the problem, try adding a message box to your script to display the type of the variable.

Code Snippet

System.Windows.Forms.MessageBox.Show(Me.Variables.rsSomeResultset.ToString())

|||You Code 1 snippet doesn't look right for ADO.NET. You shouldn't be trying to call sqlAdapter.Fill(ds). Try Dim dataTable as DataTable = ds.Tables(0) instead. I'd guess that OLE DB would be faster. Please post your findings.
|||

Do you mean this discussion?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1985034&SiteID=1

ADO.NET OleDb SET ROWCOUNT or DBPROP_MAXROWS

I am writing a data access tool that needs to be non provider specific. I have used System.Data.OleDb to access a variety of data source types (MSSQLServer, MSAccess, MSExcel, CSV, Oracle, XML). This works beautifully. Thankyou Microsoft for giving us ADO.NET2.0.

However, I now need to restrict the number of rows returned from a SELECT statement. Is there any way I can I achieve this in a non provider specific fashion?

So far searches have yielded DBPROP_MAXROWS however I can find no way of setting the DBProperties using ADO.NET2.0. Is this possible?

Documentation for DBPROP_MAXROWS states that it uses "SET ROWCOUNT n" as part of the command text. I tried this and it works but not for the JET4 provider (this is a problem since I use JET4 to access .mdb, .xls and .csv files)!

Any tips here would be greatly appreciated. Even if somebody were to tell me that it's just not possible, at least that would put me out of my misery :)

Thanks.

Because of the way JET accesses data, bringing all of the data to the client before processing the criteria, I don't think that there is a way to make that work.|||

Thanks for the reply Arnie,

since posting I have done some more research and discovered that using TOP works for Jet. This is good for me as it also works for SQL Server. The approach I have taken is as follows:

int maxRows = 100;

string someQry = "SELECT * FROM table1";

string topQry = "SELECT TOP " + maxRows.ToString() + " * FROM (" + someQry + ") as topSubQry";

However it doesn't work for XML files. To get data from XML files I use a connection string of "Provider=MSDAOSP.1;Data Source=MSXML2.DSOControl.2.6;" and then the full path to the XML file as the command text. I am guessing there will just be no way to limit the rows returned when using this provider.

ADO Using ODBC vs. OLEDB

I am working on a large ASP application(s) which uses SQL Server 2K on the back end, ADO 2.7 for Data Access. The current connection strings are setup to use older ODBC drivers. I wanted to change them to OLEDB for performance gains and better support in the future.

The problem encountered is that once the driver is changed the pages become riddled with errors. There are many piece of ADO code that are supported using the new driver. Many are cursor issues or code dealing with identity columns.

Has anyone else gone through this process?

Is there a comprehensive listing of those methods that are not compliant between these two drivers?

Any input would be appreciated...ThanksCan you post the error message you got?|||Is this an asp or asp.net application ? Yes and please post the errors and the code it is failing on.|||It definitely sounds like all your back-end code sits on the front-end, right? You would save a lot of time if instead of cleaning it up try to move to where it belongs, - back-end.|||Thanks for responding to my post.

I am using standard ASP pages not ASP.NET.

The applications I am working with are comprised of roughly 10K pages altogether. There are many errors that occur and I am able to address them as they arise. It really has to do with a lack of support from one driver to another through ADO.

I really was looking for some resources on this topic not solutions to specific code errors.

If I listed each error and code sample here it could take a long time.

Thanks|||One place you might find useful is the recordset.supports method, it will tell you if a particular method is supported by the recordset or not.

You will find differences with transaction handling too I expect.

The dynamic recordset & connection properties change too.

You could get a copy of Adoanywher browser, it displays the supported functionality of your recordsets and allows you to open multiple connections with multiple recordsets. Full support for transactions. It displays all available recordset and connection properties.

I basically use it to compare drivers just as you are attempting to do.

The browser is currently free if you register quickly at the forum :

http://www.adoanywhere.com/forum

Mike.

ADO connection options

I noticed when connecting to SQL Server using the SQL OLEDB provider that a
number of SET commands are issued. In particular
SET CONCAT_NULL_YIELDS_NULL ON
is issued, and I'd like to change that to OFF. I've set the database option
to turn this off by default, but all of the client connections still issue
the command. Is there a way in the connect string to specify SET options?
Thanks,
TomI don=B4t know of any settings to manipulate this. You only chance for
this is to put the command in your batch fired against the SQL Server.
HTH, jens Suessmeyer.|||Change you query to ISNULL(column,'')
Tom wrote:
> I noticed when connecting to SQL Server using the SQL OLEDB provider that
a
> number of SET commands are issued. In particular
> SET CONCAT_NULL_YIELDS_NULL ON
> is issued, and I'd like to change that to OFF. I've set the database opti
on
> to turn this off by default, but all of the client connections still issue
> the command. Is there a way in the connect string to specify SET options?
> Thanks,
> Tom

ADO connection options

I noticed when connecting to SQL Server using the SQL OLEDB provider that a
number of SET commands are issued. In particular
SET CONCAT_NULL_YIELDS_NULL ON
is issued, and I'd like to change that to OFF. I've set the database option
to turn this off by default, but all of the client connections still issue
the command. Is there a way in the connect string to specify SET options?
Thanks,
Tom
I don=B4t know of any settings to manipulate this. You only chance for
this is to put the command in your batch fired against the SQL Server.
HTH, jens Suessmeyer.
|||Change you query to ISNULL(column,'')
Tom wrote:
> I noticed when connecting to SQL Server using the SQL OLEDB provider that a
> number of SET commands are issued. In particular
> SET CONCAT_NULL_YIELDS_NULL ON
> is issued, and I'd like to change that to OFF. I've set the database option
> to turn this off by default, but all of the client connections still issue
> the command. Is there a way in the connect string to specify SET options?
> Thanks,
> Tom