SQL Server Drivers - Should I use 32 or 64 bit? ODBC vs OLEDB?

By: Chris Lewis

This question comes up a lot and it is a confusing issue because we are dealing with three different situations.

1. Campaign Enterprise is currently a 32-bit application
2. SQL Server which could be 32 or 64 bit
3. Windows which can be 32 or 64 bit
   

Campaign Enterprise is currently only available in 32-bit which means it can use 4GB of RAM. That is a lot for a single installation.  The 64-bit version of Campaign Enterprise will be out with version 12.

Next, let's talk about the SQL Server and the Operating System.  Now, it may be possible to run a 32-bit SQL Server on a 64-bit Operating System but there is no reason to do that.  SQL Server comes in both different bit types, so match your SQL Server bit-type to the Operating System's bit-type.

ODBC - "O' Ancient One"
Though not recommended for various reasons, if you choose to use ODBC drivers to access SQL Server, your ODBC driver needs to be 32-bit to match Campaign Enterprise.  If you are on a 32-bit Operating System, the ODBC Connections tool in your control panel can be used to administer the ODBC definition.  If you are on a 64-bit Operating System, things are a little different.  If you go into the ODBC Connections tool in the Control Panel on a 64-bit Operating System then you will be creating 64-bit ODBC connections which Campaign Enterprise will not see at all.  In this case you have to use the 32-bit ODBC driver which is found most of the time at this location: Windows\SysWOW64\odbcad32.exe.  Run this program and you will be creating 32-bit ODBC connections that Campaign Enterprise can see.  ODBC Drivers for SQL Server and most of the time just part of the latest Operating Systems.  I am always surprised to see it listed even though SQL Server is not installed on the machine.  If you are missing it, you will need to download the SQL Server ODBC drivers.

OLEDB - "The Connector for Today"
This database method is the most current way to connect to SQL Server (and other) databases.  There will be 32 and 64 bit versions of these drivers.  Unlike ODBC connections, OLEDB drivers bit-type will depend on the SQL Server bit-type.  If you have a 32-bit SQL Server, use the 32-bit OLEDB driver.  If you ahve a 64-bit SQL Server, use the 64-bit driver.  Even though Campaign Enterprise is a 32-bit application, it can surprisingly talk to a 64-bit driver, not sure why, it is just that way.  You can download OLEDB Drivers from this location currently: http://www.microsoft.com/en-us/download/details.aspx?id=29065
On this page, look for this header: "SQL Server Native Client" or some variant of that.  There will be x86 version (32-bit) and x64 version (64-bit). For those of you in the future, just go to Google and type in "oledb driver download sql server" and you will get the latest.

Remember that the drivers here can sometimes be available already on machines that have SQL Server on them, but if you are communicating with a different machine with SQL Server then you will definitely have to download the drivers mentioned above.

ODBC vs OLEDB
ODBC was an attempt to make a universal database connection and is still widely used. Though it still can function fine depending on the situation, as time has marched on, ODBC has become obsolete because it cannot access many of the newer database column types we see and it does not do well with newer cursor types.  ODBC also has "layers" of translation that make it slow.  Though you could happily use ODBC connections far into the future, there might be a time where you need to use "that data type" and ODBC will not be able to handle it.  Among those data types are the larger structures like "TEXT" fields and other large data structures.  ODBC will return a empty string when you try to access these columns using an ODBC connector.  Overall, performance is the most attractive feature in using OLEDB for your connection.

Enjoy!