Why do I get error "The OLE DB provider "MSDASQL" for linked server supplied inconsistent metadata for a column" when accessing Salesforce data from SQL Server?

The full text of the error reported to us was:

The OLE DB provider "MSDASQL" for linked server "SF" supplied inconsistent
metadata for a column. The column "CustomerPriority__c" (compile-time ordinal
48) of object ""SF"."DBO"."Account"" was reported to have a
"DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.

This issue can occur when you have custom columns in Salesforce. Let's say you already have some data in the Account table. You then add a custom column that has the data type Text and make the column Required. Salesforce marks the column in its metadata as not nullable. However, as you already have data in the Account table, Salesforce can't populate the column for you, so the data stored internally ends up being NULL.

The ODBC API SQLColumns returns whether a column can accept NULL values with NULLABLE (ODBC 1.0) / IS_NULLABLE (ODBC 3.0). NULLABLE returns one of three states:

• SQL_NO_NULLS if the column could not include NULL values.
• SQL_NULLABLE if the column accepts NULL values.
• SQL_NULLABLE_UNKNOWN if it's not known whether the column accepts NULL values.

IS_NULLABLE also has 3 states:

• "NO" if the column does not include NULLs.
• "YES" if the column could include NULLs.
• A zero-length string if nullability is unknown.

So Salesforce says the column is NOT NULL but the data contains NULL values.

In this situation, Easysoft's Salesforce ODBC driver returns SQL_NULLABLE_UNKNOWN. This leads to the DBCOLUMNFLAGS_ISNULLABLE issue in SQL Server.

To work around this issue in SQL Server, use the OPENQUERY function. OPENQUERY doesn't care if the column is NULL or NOT NULL. Here's an example:

SELECT * FROM OPENQUERY(MyLinkName, 'SELECT * FROM Account')