TaskCentre OLEDB Query: SQL Native Client versus MDAC?
I ran in to a recent problem when building an OLEDB Query and needed to filter on date fields. I hit on a problem that when using SQL Native Client 10, which I chose believing that to be the best option for SQL 2008, the SQL parsed by the query builder did not wrap my date literal in quotes, causing the query to fail. (I raised this with Orbis as issue #42146).
This was strange because I’d done this many times before, so why didn’t it parse correctly with SNC 10? I tried with a connection via the standard (MDAC) OLEDB Provider for SQL Server and the query worked just fine. Okay, a small defect in the query builder that Orbis now has on the fix list (#42149).
So if it works with the OLEDB Provider for SQL Server why use SNC 10 at all and what are the differences? There are a number of features that SNC 10 provides, such as the XML data type, but since these aren’t available through TaskCentre’s OLEDB Query Tool I see no advantage to using SNC.
According to MSDN (http://msdn.microsoft.com/en-US/library/ms130828%28v=sql.90%29.aspx), “For existing OLE DB and ODBC applications, the primary issue is whether you need to access the new features of SQL Server 2005. If you have a mature application that does not need the new features of SQL Server 2005, you can continue to use MDAC.” So if you try querying through SQL Native Client and run into unexpected SQL parsing errors, try using good old MDAC instead.
My thanks to Eddy McCarthy, Orbis Software’s Support Desk Manager, for his help with this issue.