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 cannot 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 is not known whether the column accepts NULL values.IS_NULLABLE
also has 3 states:
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 you need to use the OPENQUERY function. OPENQUERY does not care if the column is NULL or NOT NULL. Here's an example:
select * from OPENQUERY( MyLinkName, 'select * from Account')