Bug #71979 incorrect value for "DBPROP_CONCATNULLBEHAVIOR"
Submitted: 8 Mar 2014 18:27 Modified: 31 Mar 2014 11:26
Reporter: a b Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.2.5 OS:Microsoft Windows (2003 Server R2 Standard x64)
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: DBPROP_CONCATNULLBEHAVIOR, leading, ODBC

[8 Mar 2014 18:27] a b
Description:
I'm using SQL Server 2005 SP4 64bit on a Windows 2003 R2 x64 server.

MySQL Connector 5.2.5 Unicode is installed and an ODBC DSN is set up, and the test works OK. (The MySQL server is on a different machine)

The MySQL server version is 5.0.27

I have a "linked server" set up in SQL Server pointing to MySQL using the MSDASQL OLEDB provider for ODBC drivers.

Simple queries work fine using the SQL Server "linked server" syntax e.g.:

SELECT * FROM OPENQUERY(MYSQL, 'SELECT 1')

But it fails if the query string has a leading tab or line break:

SELECT * FROM OPENQUERY(MYSQL, '<tab>SELECT 1')

SELECT * FROM OPENQUERY(MYSQL, '
SELECT 1')

This works fine with non-MySQL linked servers, and it works fine with older MySQL drivers. (e.g. 5.1.12)

Using tabs or line breaks inside the query or at the end aren't a problem.

Using the "SQL Profiler" that comes with SQL Server, the error is:

OLE DB provider "MSDASQL" for linked server "MYSQL" returned an incorrect value for "DBPROP_CONCATNULLBEHAVIOR", which should be "either DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL".

There is an "OLEDB Call Event" just before this error with some parts that look relevant:

<hresult>265946</hresult>
<outputs>
  <pcPropertySets>4</pcPropertySets>
  <prgPropertySets>
    ...
    <DBPROPSET>
      <cProperties>17</cProperties>
      <guidPropertySet>DBPROPSET_DATASOURCEINFO</guidPropertySet>
      <rgProperties>
        ...
        <DBPROP>
          <dwPropertyID>DBPROP_CONCATNULLBEHAVIOR</dwPropertyID>
          <dwOptions>0</dwOptions>
          <dwStatus>0</dwStatus>
          <colid>DB_NULLID</colid>
          <vValue>
            <VARIANT>
              <vt>VT_I4</vt>
              <lVal>0</lVal>
            </VARIANT>
          </vValue>
        </DBPROP>
      </rgProperties>
    </DBPROPSET>
  </prgPropertySets>
</outputs>

How to repeat:
run this from a SQL Server with a linked server called "MYSQL".

SELECT * FROM OPENQUERY(MYSQL, '<tab>SELECT 1')

SELECT * FROM OPENQUERY(MYSQL, '
SELECT 1')
[31 Mar 2014 11:26] Bogdan Degtyariov
Verified as described.
The same behavior is observed if enter a wrong SQL syntax command.
No entries in the general query log.