Crystal Reports, which OLEDB provider to use for SQL Server

Question

When designing a new Crystal Report you have to create a new database connection. I have selected OLE DB (ADO) as the 'driver' as it made more sense than DAO,RDO or the other options for my requirements.

I need to execute an SQL Server 2005/2008 stored procedure on a remote server and show the results in Crystal Reports on a .NET client.

Selecting OLE DB, presents me with further options to select the correct provider, some of which are:

• Microsoft OLEDB provider for ODBC drivers
• Microsoft OLEDB provider for SQL Server
• SQL Native Client
• SQL Server Native Client 10.0

Which on is the best to use?

• I don't want to use the ODBC drivers, for various disadvantages (setting up ODBC definitions and maybe slower).
• Not sure how the OLEDB provider for SQL Server compares with the native client
• The native client forced me to run the native client setup on each desktop. It also seems that there is different version for each version of SQL Server.

Initially I thought the native client is the logical choice for compatibility and performance but deployment seems to be an issue (unless I am doing something wrong)

What is the best connection driver/provider combination to use here?

Solution

I would go with the Microsoft OLEDB provider for SQL Server. If you use the native client, you will need to distribute it with your applications.

This article talks about the native client in detail: http://blog.flex2sql.com/index.php/2009/02/how-and-when-to-use-sql-server-native-client/