ADO Database connectivity

[Former Member]

According to Microsoft (quoting the links below)

http://support.microsoft.com/kb/910696/en-us

http://support.microsoft.com/kb/840667

the use of ADO in ASP.NET applications is unreliable and thus discouraged. Does this also apply to Crystal Reports embedded in ASP.NET applications (Crystal Reports for Visual Studio), i.e. using the ADO connector within the reports. If so, should we use ODBC instead, or do we need to move to ADO.NET, which would mean having to populate datasets in code before calling the report?

Regards

John

[former_member183750 · Accepted solution]

Hi John

The MS resources are quite old and probably refer to VS 2005. Considering that MS is n o on VS 2013, I am not sure that those apply anymore. And even in the VS 2005 days, I don't remember any issues with CR and ADO .NET datasets. There are limits, but that is about it (e.g.; keep your dataset of a reasonable size - a few thousand records at most).

For more info see:

http://scn.sap.com/docs/DOC-21967

http://scn.sap.com/docs/DOC-21953

Crystal Reports for Visual Studio 2005 Walkthro... | SCN

SAP Crystal Reports .NET SDK Developer Guide

SAP Crystal Reports .NET API Guide

And don't forget to use the search box in the top right corner.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member · Accepted solution]

Sorry, I've obviously not made myself clear. Our application was originally developed in ASP and then ported to ASP.NET. The dev. environment was initially an early version of VS and has recently been upgraded from VS 2005 to VS 2010. The application currently still uses 'classic' ADO and the embedded Crystal reports use the ADO data connector to access SQL databases. Microsoft have indicated that the ADODB assembly is no longer supported for server apps, and we are currently re-writing the app. My question was whether the embedded Crystal reports using the ADO connector also use this assembly, and thus whether we should amend these to use ODBC (just a question of changing the connector) or amend to use the ADO.NET connector, which would mean updating our code to generate ADO.NET datasets which are then 'pushed' to the reports.

Thanks for your interest.

John

[former_member183750 · Accepted solution]

I'd go ADO .NET datasets ( has limits on size - no more than a few thousand rows) or ODBC or OLE DB. For MS SQL:

For OLE DB use:

MS SQL 2005 - OLE DB Provider

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

For ODBC use:

MS SQL 2005 - SQL Native

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

- Ludek

[Former Member · Accepted solution]

Thanks again, but you've not really answered my question. Microsoft have advised us not to use 'classic' ADO in ASP.NET applications. My question was whether this means we should avoid using OLEDB in embedded Crystal Reports (referred to as 'OLEDB (ADO)' in the user interface) and, if so, is there any advantage/disadvantage in using ADO.NET over 'ODBC (RDO)'?

As noted earlier, the application is currently using 'classic' ADO, as it was ported from ASP. It works fine on Windows 2003, but has intermittent failures on Windows 2008 R2. Microsoft have diagnosed the failures as being due to the use of ADODB, but it is not clear whether the failures are occurring in the reports or elsewhere in the application.

[Former Member · Accepted solution]

Having done some more reading up on this, I gather that the component Microsoft say we should avoid in an ASP.NET application is the ADODB assembly, rather than Oledb per se, so my question boils down to whether Crystal is built to use this assembly, or whether it uses some other technology to access an SQL database via the 'OLEDB (ADO)' connection. It may even be that the description for this connector is not accurate, and that SQL access via the native driver does not actually use OLEDB at all. I appreciate that OLEDB for access to SQL databases is being deprecated, so we would probably still want to move to using Crystal's ODBC connection option at some point. Unless advised otherwise, I'd rather not move to ADO.NET datasets, as this would mean substantial coding effort to populate the datasets in code, rather than letting Crystal 'pull' the data direct from the database as it does currently.

Thanks again for your input, which is much appreciated.

John

[former_member183750 · Accepted solution]

Ok. There is:

Classic ADO

ADO .NET

OleDB

ODBC

Native

Of all the above, the only one that I know of that MS may be moving away from is Classic ADO as that was used in the good old days of WIN and COM SDKs. E.g.; CR was able to use classic ADO in VB 6 or C++ apps using the COM Based Report Designer Component SDK. Since that SDK has been retired 2+ years ago, the other db connection options are still available and not under any threat from MS - not as far as I know.

- Ludek

[Former Member · Accepted solution]

Thanks again. I think the confusion arises from the fact the OLEDB connector in the Crystal  'Database Expert' is labelled 'OLE DB (ADO)'. This could imply that Crystal is using 'Classic ADO' (the ADODB Com component) INTERNALLY to access the database. If this is the case, we would want to move to another connection method (ODBC?), as Microsoft have stated that the ADODB Com component is unreliable in server apps - particularly ASP.NET.

Given that Microsoft have stated that ADODB was only provided in a .NET environment as a temporary solution, to give users time to migrate, I would hope that Crystal is using some other technology internally to access the database, and that the '(ADO)' in the database expert connection OLEDB option is simply misleading, but I am hoping someone 'in the know', such as yourself, could confirm this.

It doesn't affect us, as we are unlikely to need to access any later versions of SQL Server, but, if you were not already aware, Microsoft have stated that SQL 2012 will be the last version to support OLE DB. Future versions will only support ODBC.

Converting SQL Server Applications from OLE DB to ODBC

John

[former_member11696 · Accepted solution]

Hi John,

I was aware MS is moving to ODBC, seems to be the standard now and supports cross platforms.

So if Microsoft says don't use OLE DB then don't use it. Use ODBC as the data source for your dataset and simply set the report to the dataset.

rpt.SetDataSource(ds);

Is all you need to do.

CR doesn't care what you use to get your data into a dataset, as long as it's all there CR will use it.

Report can be designed off tables and the above will still work...

Don

[Former Member · Accepted solution]

Sorry to harp on about this, but it is important to us. Following some intermittent crashes in the application, Microsoft have advised our client that all use of 'Classic' ADO (the ADODB component) should be removed from the application. They have NOT, at this stage, advised against the use of OLE DB per se. My question is whether Crystal Reports still uses the ADODB component INTERNALLY if we are using the 'OLE DB (ADO)' connector from 'Database Expert'.

Our commitment to our client is to remove all use of the ADODB component from the application, so we need to know whether this means we need to address the embedded Crystal Reports as well as the VB code.

[former_member11696 · Accepted solution]

Yes you are harping... So why would CR use OLE DB if you select ODBC as your data source???? Or any other data source for that matter... CR only uses what your select.

Open each report and under the Database Tab select Set location and open ODBC and log onto your server. Update the connection and now CR is using ODBC.

Have you actually tried it and use Process Monitor to see if the driver is loaded? You obviously will not take our word for it so you'll have to confirm this yourself.

How do you know the intermittent crashes were cause by OLE DB driver? And if you are using updated MS SQL Servers you MUST use the updated Clients also in EACH report and subreport.

[Former Member · Accepted solution]

I think you may have hit the nail on the head if you say we MUST use the appropriate client drivers - most, if not all, our reports use SQLOLEDB, mainly for consistency, because the application accesses several databases on different versions of SQL Server (going back to SQL 7).

We have experienced intermittent crashes in the w3wp.exe process (on Windows 2008 R2 only - the same application is solid on Windows 2003), and both ourselves and Microsoft have diagnosed the error as occurring in the OLEDB plumbing. Microsoft will not analyse the problem further, as the application is still using 'classic' ADO (which it certainly is in the VB code), so we were intending to update the ADO VB code to ADO.NET. I have been trying to find out whether Crystal uses 'classic ADO' (adodb.dll) internally, when we use the OLEDB connector (AFAIK, ADO is not the same as OLEDB, so one does not imply the other). However, this may be a red herring if the problem is in SQLOLEDB. I think maybe, before we go down the path of updating our code to ADO.NET, we should try updating the reports (100+ of them!) to use the appropriate 'native' driver, rather than SQLOLEDB. I see that

Microsoft SQL Server Native Client and Microsoft SQL Server 2008 Native Client - Microsoft SQLNCli ...

includes a table of which SQL versions are supported by each native client, with the original native client supporting SQL 7. If the problem could be with SQLOLEDB, would you suggest we use the original (SNAC 9) native driver, rather than SQLOLEDB, with SQL 7? (In an earlier post, Ludek has suggested SQLOLEDB for OLEDB access to SQL 2005, and hasn't listed any earlier SQL versions).

[Former Member · Accepted solution]

Incidentally, if we were to move to ODBC, we might have a problem, as we need to be able to update the server and database names programmatically. Googling indicates that people have had difficulty doing this for ODBC, though we do it currently with OLEDB. However, that is for another thread....

[former_member11696 · Accepted solution]

Then you should log the ODBC issue with Microsoft so they can fix their ODBC Drivers.

Be sure to use the correct Client dll for each version of SQL Server.

Native for SQL 2005 and previous

Native 10 for SQL 2008

Native 11 for SQL 2012

Sounds like no matter what you do MS SQL is going have problems when connected...

Good luck

Don