Skip to Content
0

OLE DB vs ODBC, drivers required?

Mar 31, 2017 at 07:44 PM

211

avatar image
Former Member

Hi there!

I'm relatively new to Crystal Reports. I've made some reports in Crystal Reports 2016 using the OLE DB access method, because that's what some tutorials on Lynda and Infinite Skills showed me. My data source is a SQL Server 2008 R2 database. Crystal Reports can runt he report, and Crystal Report Viewer on another machine can run the report. However, on the second machine, I can't get any 3rd party report viewers to run the report unless I switch to ODBC and manually configure a System DSN.

I don't know or understand the difference, and one of the developers of the 3rd party report viewer said something about installing OLE DB drivers, but I can't seem to find any when I search the net. Well, I find links to Jet and ACE databases, but not SQL Server.

Looking up OLE DB and ODBC, I find lots of technical information geared towards application developers, and I think I'm getting pretty deep in the weeds but not finding much fruit. For example, this Microsoft article:

https://msdn.microsoft.com/en-us/library/aa235368(v=vs.60).aspx

For this project and client, I will have to use a 3rd party report viewer, as Crystal Report Viewer's limitation of not being able to refresh data is a deal breaker. I'm hesitant to switch to ODBC, since that seems to be an older communication mechanism. If I do, will i lose any features or abilities?

On a related note, is there a way to script the creation of an ODBC DSN/driver? Am i going in the right direction?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Don Williams
Mar 31, 2017 at 07:52 PM
0

Hi Dan,

First to clarify, CR Designer does not have a MS SQL OLE DB Provider, we do have a connector that we use to connect to the DB's OLE DB provider client.

CR is a 32 bit application, So to connect the first thing you need to do is install MS SQL Client Tools on your app server and DEV PC. Use the MS SQL Native 10 client dll to connect, the MDAC version is old and not fully supported in newer SQL Servers by Microsoft which is like what you are using.

Search MS's site for the client tool MSI or it will be on the MS SQL Server install CD/DVD under Client Tools.

Once it is installed then the app and CR will connect fine.

Also, MS is actually dropping OLE DB and moving to ODBC only, so you should convert to ODBC so in a few years when you upgrade SQL Server support you won't have to update all of your reports.

When you create the DSN select SQL Server Native 10 dll in your System DSN.

Don

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

I don't understand what you're saying about Crystal Reports not having a OLE DB provider but i having a connector. Those words are going to be too vague for me to run through an internet search.

I've switched to ODBC, and after hearing enough suggestions to switch, and that Microsoft is switching back to ODBC, I think the point is moot regarding OLE DB. Setting up a DSN isn't too complicated, and with Ido's method, i could automate it.

0
Ido Millet Apr 02, 2017 at 10:07 AM
1

To clone the ODBC DSN to other machines:
- use regedit to export the node containing the DSN definition from the registry to a .reg file
- Double-click the .reg file on the user machines (that imports the .reg file into the registry).

Share
10 |10000 characters needed characters left characters exceeded